Linked server script

[EBee]

I tried to see if there is a good website or book that tells me more about linked server scripting.

I am able to create a linked server from SQL 2k to a Progress database. Now, I am trying to create stored procedure, views. . etc. to to view data out of the linked server. I don't see much info for scripting using linked server.

select * from openquery(SACDEV,'select * from carrier')

is pretty much limited. I wanted to create stored prcedures that can pass parameter and using variable in linked servers.


Thank you in advance
Erwin

 

[mrdenny]

You can select directly from a linked server.

select *
from SACDEV.database.owner.object

If the remote database only supports a three part name (like Oracle does)

select *
from SACDEV..owner.object

You can also execute stored procedures on the remote server and return data, just like it's a local procedure.

declare @i int
set @i = 10
exec SACDEV.database.owner.procedure @i=@i

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[EBee]

select * from openquery(sacdev,'Select * from pub.occupant')
where lastname ='Smith'

select * from sacdev_cmaddress..pub.occupant
where lastname = 'Smith'

Thank you for your response. . is there a big difference between this two. Is one more efficient that the other. And does it get evaluated in the SQL server or the remote server

 

[Catadmin]

The best way to check efficiency is to open them up in QA and do a Query Execution Plan. Look at the time on the bottom and perhaps run Profiler with a workload while you're doing it. This will give you the best estimation of how the two differ and whether one or the other will cause problems if run while you have a full load on the server.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"