Keep remote database updated with local database

Question

0.00/5 (No votes)

See more:

Hi. I am using MSSQL 2008 Express at source with c#.net express edition in VS express 2010.
I have a remote database and a local database.
I want to keep the table of remote database with one table of source database updated.
Implementing Replication, tablediff, third party tools, microsoft sync, etc. is too time taking and also needs some tools that are directly not the part of express edition.
I am looking for a simple solution.
The button clicking on which updation is done will be placed inside a software and used by a novice user.
I am planning to use MERGE in inline query or in stored procedure but not able to achieve success. The main issue if firing queries on two connections.
i.e., reading from local and sending to remote server.
Also, I donot want to delete the remote data and then re-copy data using sqlbulkcopy or other method on every updation job. It will consume too much time and server bandwidth.
Kindly help.

What I have tried:

Sqlbulkcopy Sync, Merge, stored procedure.
MERGE TABLE1 as target
USING UTABLE as source
ON target.record_id = source.record_id
WHEN MATCHED THEN
UPDATE SET Target.columns=source.columns
I donot know how to make source as local server and target as remote server

Posted 6-Jun-16 4:36am

Member 8057273

Updated 7-Jun-16 3:48am

v3

Add a Solution

Comments

CHill60 6-Jun-16 10:38am

You ask "tell me the best possible way of doing this" and then asked us to avoid the best ways of doing it!

What issues are you experiencing with your attempt to use MERGE - and how exactly did you try to use it?
Is this always only going to be a single table that needs to be synchronised?

Member 8057273 6-Jun-16 10:43am

Ok,
I have updated the question with your concern. Can you please suggest a solution?

Member 8057273 6-Jun-16 10:45am

Yes. I have out of many tables in source db, I need to update only 1 table in destination. I liked the concept of Merge but dont know how to make it work on two different servers. one is local while other is a remote IP address. The solution I felt good is :
MERGE TABLE1 as target
USING UTABLE as source
ON target.record_id = source.record_id
WHEN MATCHED THEN
UPDATE SET Target.columns=source.columns

CHill60 6-Jun-16 10:54am

Well you can create the remote server as a Linked Server - see Linking Servers[^].
The query would then just need to fully specify the table i.e. [server].[database].[schema].[table]
Be aware that this may come with it's own performance issues - see Top 3 Performance Killers For Linked Server Queries - Thomas LaRock[^]

Member 8057273 6-Jun-16 11:03am

Thanks for the response. But isn't there a way to mention source and destination server ips in the MERGE query? That would be an easy and clean shot.

CHill60 6-Jun-16 11:06am

Yes - just use the same syntax for both tables
i.e. MERGE targetserver.targetdb.dbo.TABLE1 as target
USING sourceserver.sourcedb.dbo.UTABLE as source ...

Member 8057273 6-Jun-16 11:46am

Hi. Here is the code i tried but got an exception.

string sourceserver = @"127.0.0.1\SQLEXPRESS";
string targetserver = "111.111.111.11"; //ipofserver
SqlCommand com = new SqlCommand();
com.Connection = destcon; /* THIS IS DOUBTFUL tried with sourcecon too but still exception */
com.CommandText = "MERGE [" + targetserver + "].fancy_watchdb.fancywatch.photo as target USING [" + sourceserver + "].event_planner.dbo.photo";
sourcecon.Open();
destcon.Open();
if (com.ExecuteNonQuery() > 0)
MessageBox.Show("Done");
else
MessageBox.Show("Failed");
sourcecon.Close();
destcon.Close();

Exception is: Incorrect syntax near 'photo'.
I have checked on server and client, all names all right.

CHill60 6-Jun-16 11:50am

You need the rest of the merge statement in there too - I only included the bits that referred to the table names

Member 8057273 6-Jun-16 12:35pm

Hi. The rectified query is:

com.CommandText = "MERGE [" + targetserver + "].fancy_watchdb.fancywatch.photo as target USING [" + sourceserver + "].event_planner.dbo.photo as source ON target.r_id = source.r_id WHEN MATCHED THEN UPDATE SET target.bname=source.bname;";
sourcecon.Open();
destcon.Open();
both ran successfully, but
if (com.ExecuteNonQuery() > 0)
gave the following exception:
Could not find server '127.0.0.1\SQLEXPRESS' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

CHill60 6-Jun-16 14:24pm

See the first part of my comment - you have to set up the Linked Server first

Member 8057273 7-Jun-16 1:51am

The linked server is setup and the connection of local server with remote server is successful. The same exception is still there.
Could not find server '127.0.0.1\SQLEXPRESS' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Member 8057273 7-Jun-16 2:12am

I also tried .\SQLEXPRESS but the exception is still same:
Could not find server '.\SQLEXPRESS' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers

Member 8057273 7-Jun-16 7:53am

I have removed the datbase names from course and destination connection strings and also changes the server name to "SHOBHIT-ASUS\SQLEXPRESS". Now the new error is: "The target of a MERGE statement cannot be a remote table, a remote view, or a view over remote tables"

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2016-06-07T03:48:00

See also the reams of comments above.

Ok, the target of a MERGE statement cannot be a remote table, but the source can be.

I suggest setting up the MERGE statement in a Stored Procedure on the target (i.e. remote) server. You can then execute it from your source / button press using

SQL

exec('your_SP_name') AT linked_server_name

Reference: EXECUTE (Transact-SQL)[^]
There is another example here - The Curse and Blessings of Dynamic SQL[^] - using parameters with dynamic SQL to run the pass-through query.