Thursday, April 24, 2008

Linking my database to another database using a linked server

I have had trouble linking to a seperate server called 'jn-svrdev1-vma7/sql2005'. Googling how to lookup a table on another server will tell you just use a simple statement like the following:

SELECT md.*, od.*
FROM myDataTable md
INNER JOIN [OtherServerName].OtherDatabaseName.dbo.OtherDataTable od
ON od.SameFieldID = md.SameFieldID

Simple enough? No, they don't explain that the 'OtherServerName' must be a linked server defined in the 'Server Objects' of your database. I will outline how to link this server in the next posting. I ran into errors trying to do this. Once you have a linked server, you can use a statement like the one above.

I have found that using the Design Query helps in this case, since saves a few keystrokes writing the statement.

No comments: