Post reply

creating Linked server in Stored procedure

  • August 1, 2005 at 9:31 am

    #90292

    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

    #578601

    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