How to query data from linked server

Question

How to query data from linked server

kris1999

SSC Veteran

Points: 287
More actions
May 5, 2010 at 11:47 am

#386857

I know:
Linked server name, data source. How to know table name and retrieve data?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

exec sp_tables_ex MyLinkedServer will show you all the tables in the linked server that you can directly access; from there it's things like select * from MyLinkedServer...Tablename, and you might have to include the dbname or schema name, if it will not use default shortcuts.

MyLinkedServer.dbname.dbo.Tablename

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!

adalberto-339588 SSC Veteran Points: 261 More actions · Answer 2

1- Link the server

EXEC sp_addlinkedserver 'OracleSvr',

'Oracle 7.3',

'MSDAORA',

'ORCLDB'

2-SELECT

SELECT *

FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM albert.titles')

3-UPDATE

UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM albert.titles WHERE id = 101')

4-INSERT

INSERT OPENQUERY (OracleSvr, 'SELECT name FROM albert.titles')

VALUES ('NewTitle');

5-DELETE

DELETE OPENQUERY (OracleSvr, 'SELECT name FROM albert.titles WHERE name = ''NewTitle''')

if more information is needed, please let me know 😉

agnesloyola Old Hand Points: 352 More actions · Answer 3

I have written an stored procedure that read some records from the remote table on the linked server.Actually whats the problem is When I execute the Stored Procedure ,the execution takes more than 30 minutes.Eventhough its taking such a long time its not returning any records.Just an message is there "Executing Query".Can anyone help me please?For more than a week am working on this ,why its not returning any record.

agnesloyola Old Hand Points: 352 More actions · Answer 4

Why you have used OPENQUERY?is that the syntax to fetch the records from the linked server?

Lowell SSC Guru Points: 323518 More actions · Answer 5

agnesloyola (8/30/2010)
I have written an stored procedure that read some records from the remote table on the linked server.Actually whats the problem is When I execute the Stored Procedure ,the execution takes more than 30 minutes.Eventhough its taking such a long time its not returning any records.Just an message is there "Executing Query".Can anyone help me please?For more than a week am working on this ,why its not returning any record.

There are several threads here on SC on Linked server performance;one thread which stated that openquery against a linked server was faster for a linked server vs a query on your server using 4 partnaming conventions;

for example, select * from LinkedServer.Databasename.dbo.MillionRowTable where something=somethingelse

vs

SELECT *

FROM OPENQUERY(LinkedServer, 'select * from MillionRowTable where something=somethingelse');

the reason: the first query gets the million rows locally into temp, then does the filtering, where the openquery does the work on the linkedserver, and returns just the results.

similarly, if you are joining a local table to a linked table to get results, it can take forever, because the MillionRowTable on the linked server gets copied over the wire to temp db, THEN the join to your local table is performed.

You'll have to look at the specific data you are trying to get form the linked server and limit it BEFORE you join to it to prevent that from happening.

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!

agnesloyola Old Hand Points: 352 More actions · Answer 6

Thank you so much Lowell.Now its working fime after using OPENQUERY.Thanks a lot 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 7

agnesloyola (8/30/2010)
Thank you so much Lowell.Now its working fime after using OPENQUERY.Thanks a lot 🙂

glad i could help and thanks for the feedback!

that link server behavior's a good concept to keep in your mental toolbox!

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!