更新来自不同服务器的数据 [英] Updating Data From Different Servers

问题描述

用来自服务器A的数据更新另一个服务器B中的表的最佳方法是什么?

我尝试使用servername.databasename.databaseowner.tablename

但是它不起作用:将服务器添加到sys.servers的sp_addlinksever".还有另一种方法可以执行此操作，而无需执行该过程.也许使用C#?

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#?

推荐答案

您尝试过:

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

您需要指定您的[]大括号.

同样，在此之前，您需要为服务器数据库配置内联服务器.

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

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.

:)

好吧，我从未尝试过，但是我怀疑如果您有两个连接字符串(每个数据库一个)，您可以从其中一个读取到内存中的DataTable/Collection中/某些结构，然后插入/更新其他结构.

尽管我也很确定其他人会有更好的主意.

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.

如果您不希望使用LinkedServer，则可以在跨数据库平台中获取数据.看看Openrowset

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

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

这篇关于更新来自不同服务器的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！