February 15, 2006 at 11:56 pm
Hi,
I have a SP, in which I am reading a Feed File and dumping the data, Now, I want to Execute another SP which is in another SQL Server 2000 from the first SP, I have access to that Server, can anybody help me how to go about it.
Like we have to prefix the SP with some syntax like, <ServerName>.<dbo>.<DataBaseName>.<SPName>.
Thanks in Advance
February 16, 2006 at 4:39 am
Welcome
the syntax is
EXECUTE mylinkedserver.myremotedatabase.myremoteobjectowner.USP_MYREMOTESTOREDPROCEDURE @param1=....
February 17, 2006 at 6:08 am
be sure to link the other SQL server to the server that will be executing the query.
So for your example, if SQL1 is the other server and you want to run a SP on SQL1 from SQL2 (the current server), you need to run sp_addlinkedserver on SQL2 to link SQL1 to it.
After you get them linked, you should be able to run SPs and queries on SQL1 from SQL2 with no problem. I do this on a daily basis with applications that join dbs stored on different servers.
February 17, 2006 at 11:07 am
Any performance enhancements needed? I can see where potential "lag time" my take its toll on the environment. Especially if working with VLDB's
Any thoughts?
February 17, 2006 at 11:22 am
Depending on what you are doing, using linked servers can pose some significant perfomance challenges and licensing concerns. Do your homework. Also, consider replication as another alternaive.
February 17, 2006 at 11:50 am
Thanks TDuffy - I am with you on both concerns. A work around too, would be the use of temporary tables and DTS/BCP. We use that on our site and it works out well.
February 17, 2006 at 11:09 pm
exec sp_addlinkedserver 'sql2','','SQLOLEDB','172.16.8.121'
exec sp_droplinkedsrvlogin 'sql2,null
exec sp_addlinkedsrvlogin 'sql2','false','currentsqlloginuser','remoteloginuser','remoteloginpassword'
select * from sql2.db.dbo.tbl
...
exec sp_dropserver 'sql2','droplogins'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply