Trying to Create a query that queries 2 different DBs!!!

[CJAI]

I am trying to write a query that queries 2 different databases on 2 different Servers. I am using SQL Server 2000.

I have linked MS Access tables to the SQL Server Tables in order to build the query easily. I built the query but it is not working in SQL Server.

If anyone can help with this query Id very much so appreciate it

SELECT DISTINCT [dbo_Environmental Safety Solutions$Item].[Common Item No_], [dbo_Environmental Safety Solutions$Item].Description, dbo_drawingCheckout.checkoutStatus

FROM [dbo_Environmental Safety Solutions$Item] INNER JOIN dbo_drawingCheckout ON [dbo_Environmental Safety Solutions$Item].[Common Item No_] = dbo_drawingCheckout.drawNo

WHERE ((([dbo_Environmental Safety Solutions$Item].[Common Item No_])<>'') AND ((dbo_drawingCheckout.checkoutStatus)="P"))

 

[CJAI]

somebody - anybody??!! im stuck and im trying to finish this so i can leave work!!! lol

 

[ArtieChoke]

You need the servers linked to join across tables - doh!

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Connatic]

ArtieChoke is correct, if you go to the Security tab in SQL Server Engine, and expand it, you should see an option called LINKED SERVERS!

By bringing up the right click menu (on the Right Pane) you can select Create New Linked Server, here you need to give details about the Server you want to link to !!

If you want to create a Linked server Programatically, Books Online has good TSQL examples !

 

[Connatic]

Oh i forgot to add, the syntax for using a linked server after you have created it is

[LinkedServerName].[DBName].[TableName].[fieldname]

You may want to alias in the WHERE statement as SQL server doesn't like you using 3 dots like above in a statement.

So,

SELECT *
FROM [LinkedServerName].[DBName].[TableName] tname
WHERE tname.Fieldname = @yourvalue

 

[CJAI]

Thank you very much for your help guys!!

J