Connect to multiple servers using Query Analyzer T-SQL

[newjim]

I need to compare userids in tables in databases in two SQL Servers running on two machines. I would like to do this using the Query Analyzer, for speed. Something like:

--connect through Query Analyzer gui to 1stSQLServer
select userid from 1stSQLServer.users
--connect to 2nd SQL Server
foreach userid in 1stSQLServer.users
select userid in 2ndSQLServer.users
if userid is null...

How do you connect to this 2ndSQLServer using the T-SQL of Query Analyzer?

 

[jbenson001]

Create a linked server between the servers..
Then...

Select * from <table1>
Inner Join ServerName.DataBase.owner.<table2 Name> ON
<table1>.<column> = <table2>.<column>

 

[newjim]

Thank you.

Can you run by me the process for creating a linked server?

 

[SQLBill]

In the BOL, go to the Index tab and enter linked server.

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
or online at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

-SQLBill

Posting advice: FAQ481-4875

 

[newjim]

Thank you!

This stored procedure told me that I already had a link to MYLINKEDSERVER

sp_linkedservers

If I didn't, I would have created a linked server using:

sp_addlinkedserver(syntax is in Books Online)

Then I logged in to MYLINKEDSERVER by:

exec sp_addlinkedsrvlogin MYLINKEDSERVER, 'false', NULL, 'mylogin', 'mypassword'

Finally, I did a query on the mydatabase database using the openquery() method:

select * from
openquery(MYLINKEDSERVER,'SELECT id from mydatabase.dbo.users')

Note that there are no quotes around "MYLINKEDSERVER" and that you have to specify the table owner (dbo) in the remote query.

This has been a most useful posting for me, and I hope so for others.