Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Is using linked server the fastest way to snyc data between web server and local server.

Posted on 2011-10-17
8
Medium Priority
?
183 Views
Last Modified: 2012-05-12
I have a sql 2008 server in work and want to sync it with webserver 2008. At the moment I have attached the web server as a linked server. But seems slow, plus i am stuck when i try and do a delete query using a left join query.

DELETE
FROM [LinkedWebServer].DB_2454.dbo.Products Webserver LEFT JOIN [ProductDatabase].[dbo].[Products] localserver ON Webserver.ProductRef=localserver.ProductRef
WHERE ((Discontinued=True))
GO
(i get an error of Incorrect syntax near the keyword 'LEFT'.)

I have tried microsoft's localdatabase cache datasync but then have to sync the .mdf file with my server then sync the mdf file with the webserver, which seems a waste of time plus have a few problems on a few computers where this method just hung. Can you do this directly with the 2 servers?

Please help if you have any ideas!
0
Comment
Question by:taz8020
  • 4
  • 3
8 Comments
 
LVL 15

Accepted Solution

by:
Anuj earned 2000 total points
ID: 36978879
Try this

DELETE [LinkedWebServer].DB_2454.dbo.Products
FROM [LinkedWebServer].DB_2454.dbo.Products Webserver LEFT JOIN [ProductDatabase].[dbo].[Products] localserver ON Webserver.ProductRef=localserver.ProductRef
WHERE ((Discontinued=True))
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36978926
note that if you delete with LEFT JOIN, you basically will delete even if there is no match, so I would review that "join" ...

0
 
LVL 3

Author Comment

by:taz8020
ID: 36978932
Hi Thanks, that worked. Do you think this is the fastest way to sync servers? it took 30 mins to update 4000 records
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 15

Expert Comment

by:Anuj
ID: 36979053
Web server means Remote Database Server?
0
 
LVL 3

Author Comment

by:taz8020
ID: 36979069
Yes sorry a remote sql data base 2008r2 and an internal  sql database sqlepress 2005
0
 
LVL 15

Expert Comment

by:Anuj
ID: 36979233
Usually when you are querying remote servers across the network, you will have performance problems. Also look for the indexes on the tables used in the above query on both servers.
0
 
LVL 3

Author Comment

by:taz8020
ID: 36980048
I have just written a insert query.
It took 25 minutes to insert 465 rows I then ran it again and it took 4 mins to return 0 records added. Why so long? The 2 tables both use ProductRef as a string(25) for the primary key.
0
 
LVL 3

Author Comment

by:taz8020
ID: 36980195
Hi angelIII, thanks have just changed this to inner. any idea why its taking so long.
I have just written a insert query.
It took 25 minutes to insert 465 rows I then ran it again and it took 4 mins to return 0 records added. Why so long? The 2 tables both use ProductRef as a string(25) for the primary key.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question