sp_addlinkedserver not able to execute with other command line

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » sp_addlinkedserver not able to execute with...

sp_addlinkedserver not able to execute with other command line

Rate Topic

Display Mode

Topic Options

Author

Message

yun83

Posted Wednesday, March 20, 2013 6:15 PM

Forum 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 »

Permissions