Performing queries on multiple servers

Question

Post reply

Performing queries on multiple servers

doug 37293

Old Hand

Points: 362
More actions
April 6, 2011 at 4:40 pm

#249101

I have 2 SQL Servers. One is running SQL Server 2008. The other - SQL Server 2005.

I need to do a query with a join on tables in the 2 different servers. Can this be done? What do I need to do?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 1

Latheesh NK

SSCertifiable

Points: 6559

April 6, 2011 at 11:25 pm

#1308353

if both are in same server just use complete path. if those are in two different server, create a linked server and use the join.

doug 37293 Old Hand Points: 362 More actions · Answer 2

doug 37293

Old Hand

Points: 362

April 7, 2011 at 11:41 am

#1308714

OK, I got though the process of Linking the servers, and all of the hijinks involved with getting that security right (I think).

Could you please give me a sample of the syntax for a SELECT - JOIN using tables in the 2 servers

WayneS SSC Guru Points: 95405 More actions · Answer 3

Check out the FROM clause in Books-OnLine (BOL). The table on the report server will need to be specified in 4-part naming (Server.Database.Schema.Table).

Edit: Where Server = the Linked Server name for the remote server on the local server.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lowell SSC Guru Points: 323482 More actions · Answer 4

a simple example:



SELECT

t1.*,

t2.*

FROM Databasename.dbo.Table t1

INNER JOIN MyLinkedServer.DbName.dbo.OtherTable t2

  ON t1.ID = t2.ID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

doug 37293 Old Hand Points: 362 More actions · Answer 5

Good so far. Now I am receiving an error which is known per http://support.microsoft.com/kb/906954.

The fix is to run instcat.sql. Before I do this, I want to make sure that I completely understand the syntax of the recommended command.

Command:

osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName> -i <Location>\instcat.sql

Example:

osql -U sa -P MyPassWord -S Database.IpAddress.com -i “C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql”

I think this needs to be run on the older SQL 2000 server, but what do I enter for the <LinkedServerName>, the SQL 2000, or the SQL 2008 server?

Craig Purnell SSCrazy Points: 2660 More actions · Answer 6

doug 37293 (4/8/2011)

Good so far. Now I am receiving an error which is known per http://support.microsoft.com/kb/906954.

The fix is to run instcat.sql. Before I do this, I want to make sure that I completely understand the syntax of the recommended command.

Command:

osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName> -i <Location>\instcat.sql

Example:

osql -U sa -P MyPassWord -S Database.IpAddress.com -i “C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql”

I think this needs to be run on the older SQL 2000 server, but what do I enter for the <LinkedServerName>, the SQL 2000, or the SQL 2008 server?

Yes, instcat.sql needs to be run on the SQL 2000 32 bit box. You can do it via osql (above) or just connect to the sql server via SSMS and then run the script there.