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