How do I query a linked server table

Question

question

technette asked Oct 4, '11

How do I query a linked server table

What's the correct format for querying a linked server table. I'm trying to do the following: SELECT column1, column2, column3 FROM Profiletbl P INNER JOIN Depttbl E ON P.lUserId = E.DeptId INNER JOIN modeltbl M ON P.Modelno = M.Modelno Left Outer Join LinkServer.Database.Table U ON U.EmployeeID = E.UID

linked-server

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Oct 04, 2011 at 12:06 PM

@technette The format you are trying is just as bad as it would be without the linked server (you are missing the schema spec). With the linked server, 4-part name seems to be the best, i.e. **LinkServer.Database.Schema.Table** rather than **LinkServer.Database.Table**. If you use openrowset or openquery then use 3 part name (**Database.Schema.Table**). With the linked server you can use T-SQL but with openrowset - whatever is the flavour of the destination database.

0 ·

Oleg commented · Oct 04, 2011 at 12:29 PM

@technette Let me upvote this question to get you off that "lucky 666" karma points :)

0 ·

technette commented · Oct 04, 2011 at 12:30 PM

thanks Oleg... I need to study this point system :)

0 ·

Answer 1 · 2011-10-04T12:02:29Z

WilliamD answered Oct 4, '11

By the look of it, you have forgotten the schema name in the linked server join: LinkedServer.Database.SchemaName.Table That should work fine once the schema name is included.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

technette commented · Oct 04, 2011 at 12:17 PM

Thank you William and Oleg! Really appreciate your quick response.

0 ·

question