匹配2个不同服务器上的2个表的列

I have 2 databases on their own server. I want to compare a FIRSTNAME and LASTNAME column that is located in a table on each database.

我在自己的服务器上有2个数据库。我想比较位于每个数据库的表中的FIRSTNAME和LASTNAME列。

If the first database has a firstname and lastname combo that matches the firstname and lastname columns in the second database, I want to display that name.

如果第一个数据库的firstname和lastname组合与第二个数据库中的firstname和lastname列匹配,我想显示该名称。

I just want to know which name is listed in both database servers.

我只是想知道两个数据库服务器中列出了哪个名称。

I have tried the below but I still get an error:

我尝试过以下但我仍然收到错误:

Could not find server 'sql01' 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.

Here is what I am trying:

这是我正在尝试的:

EXEC sp_addlinkedserver @server='sql01'

SELECT LASTNAME, FIRSTNAME, STATUS, CLASS
  FROM sql01.database1.dbo.MyTable1 T1
  WHERE EXISTS (SELECT FIRSTNAME, LASTNAME 
                FROM app02.database2.dbo.MyTable2 T2
                WHERE T1.FIRSTNAME = T2.FIRSTNAME
                AND T1.LASTNAME = T2.LASTNAME)

sql01 is my first server. app02 is my second. I am running the query in SQL Server while already being connected to app02.

sql01是我的第一台服务器。 app02是我的第二个。我在连接到app02时在SQL Server中运行查询。

Any suggestions?

2 个解决方案

#1

Include the instance name with sp_addlinkedserver (sql01\SQLEXPRESS, or something similar depending on the version of SQL server)

包含sp_addlinkedserver的实例名称(sql01 \ SQLEXPRESS,或类似的东西,具体取决于SQL Server的版本)

Also you need to run another Stored procedure to provide login details for the linked server

此外,您还需要运行另一个存储过程来提供链接服务器的登录详细信息

EXEC sp_addlinkedsrvlogin 'Servername', 'false', NULL, 'username', 'password'

#2

You need to add the server and the server login:

您需要添加服务器和服务器登录:

EXEC sp_addlinkedserver @server='sql01'

EXEC sp_addlinkedsrvlogin 'sql01', 'false', NULL, 'username', 'password'

#1