August 1, 2005 at 9:31 am
hi
is it possble to create and use a linked server in a storedproc
CREATE PROC [DataCopyStoredProcedure]
AS
--Creating linked server
IF NOT EXISTS (SELECT SRVNAME FROM master..sysservers WHERE SRVNAME='sql')
EXEC sp_addlinkedserver 'sql' ,N'SQL Server'
EXEC sp_addlinkedsrvlogin 'sql', 'false', NULL, 'sa', ''
INSERT INTO [Shippers] ( ShipperID ) SELECT ShipperID FROM sql.northwind.dbo.Shippers
Above statement sends an error message
Could not find server 'sql' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
Is there a way to make it work?
Thanks
Amrita
August 1, 2005 at 10:11 am
For these cases is a lot better to use OPENROWSET or OPENDATABASE. Linked servers are supposed to be used as permanent resources. IF you still insist in using that method you need to change the insert statement to dynamic SQL but I would still try the recomendation above before going the dynamic route
* Noel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply