sp_addlinkedserver not able to execute with other command line

  • Hi there,

    I am barely new with T-SQL and trying to link to the other server database with sp_addlinkedserver.

    I am able to make connection with only executing the sp_addlinkedserver and then pull the data from the remote server.

    But, when I put everything together, the sp_addlinkedserver stops working and keep on prompt me that the target server is not on the sys.servers.

    sp_addlinkedserver also will not work if it were executed from the stored procedure?

    Working:

    EXECUTE sp_addlinkedserver

    @server = 'pHpnng',

    @srvproduct = '',

    @provider = 'SQLNCLI',

    @datasrc = 'pHpnng',

    @provstr = 'DRIVER=SQL Server;SERVER=pHpnng;UID=sa;PWD=Spm2009!;'

    END

    SELECT * FROM sys.servers WHERE data_source = 'pHpnng'

    Not Working:

    (With the additional line will cause everything to stop working and the 'pHpnng' will not appear on the sys.servers at the end of the execution)

    EXECUTE sp_addlinkedserver

    @server = 'pHpnng',

    @srvproduct = '',

    @provider = 'SQLNCLI',

    @datasrc = 'pHpnng',

    @provstr = 'DRIVER=SQL Server;SERVER=pHpnng;UID=sa;PWD=Spm2009!;'

    END

    SELECT * FROM [pHpnng].[OTestSystemDB].[dbo].[ListName]

    This only works if I ran the 1st code, then run the "SELECT * FROM [pHpnng].[OTestSystemDB].[dbo].[ListName]" seperately.

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply