March 20, 2013 at 6:15 pm
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