Updating Data From Different Servers

Question

0.00/5 (No votes)

See more:

What is the best way to update a table which is in another server B with data from server A?

I tried to use the servername.databasename.databaseowner.tablename

But it doesnot work: "sp_addlinksever to add the server to sys.servers". There is another way to do this without the need of doing that procedure; maybe with C#?

Posted 4-Oct-10 10:30am

jalmonte

Add a Solution

Comments

Henry Minute 4-Oct-10 17:24pm

You will need two of everything. Two connection-strings, Two SqlCommands. If you are using visual controls anywhere in the process, two BindingSources/DataAdaptors/whatever.

Keep the two halves of the process completely separate except for a signal of some sort from the reader to the updater that there is data available.

3 solutions

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)

Abhishek Sur · Answer 1 · 2010-10-04T12:14:00

Did you try :

SELECT * FROM [LinkedServer].[RemoteDatabase].[User].[Table]

You need to specify your [] braces.

Also before doing that, you need to configure Lined server for the server database.

http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm[^]

Other than that, you can always have provision to do the same from your ADO.NET code.

Use

SqlCommand cmdServer1 = new SqlCommand("query", new SqlConnection("first server con string"));

SqlCommand cmdServer2 = new SqlCommand("query", new SqlConnection("Second server con string"));

By this way your application can separately connect to two server based on their respective connectionstrings.

Note : you should not copy / paste the code above, as I just showed you the code and there is chance for betterment.

:)

Henry Minute · Answer 2 · 2010-10-04T11:11:00

Solution 1

Well, I've never tried it but I suspect that if you have two connection strings (one for each database) you could read from one into an in memory DataTable/Collection/Some structure then Insert/Update the other.

Although I'm also pretty sure that someone else will have a better idea.

Posted 4-Oct-10 11:11am

Henry Minute

Comments

jalmonte 4-Oct-10 17:19pm

I tried that. But how then you add both connections in the SqlCommand?

Goutam Patra · Answer 3 · 2010-10-04T20:40:00

Solution 3

If you dont want LinkedServer then there is way to fetch data in a cross database platform. Take a look at Openrowset

SQL

SELECT a.* FROM OPENROWSET('MSDASQL.1', 'DRIVER=SQL Server;SERVER=192.168.2.2;UID=sa;PWD=;DATABASE=CBOS',
'SELECT * FROM MASTER') as a

Posted 4-Oct-10 20:40pm

Goutam Patra

Updated 4-Oct-10 22:34pm

v2