Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_addlinkedserver not able to execute with other command line Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 6:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:29 AM
Points: 2, Visits: 11
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.
Post #1433556
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse