Help for Linked server code

Question

Help for Linked server code

Kenny Gua 431

Hi, I am unable to run the following query from server:cd_pdserv02.DBTBB_BACK and getting the linked server error. How I can grant the access of linked server to run the query. OR is there anyway to run the query successfully. Thanks for your help

Select * from [cd_pdserv01].DBTRB_BACK.dbo.[rule]
--Could not find server 'cd_pdserv01' in sys.servers.

Can I run the above query with OPENDATASOURCE? How I can run with OPENDATASOURCE?

Olaf Helper 47,631 Reputation points

2021-05-26T05:50:22.293+00:00

Could not find server 'cd_pdserv01' in sys.servers.

Is there a linked server created in the SQL Server where you are connected to?

Answer accepted by question author

0 additional answers

Your answer

Olaf Helper 47,631 Reputation points

2021-05-26T05:50:22.293+00:00

Could not find server 'cd_pdserv01' in sys.servers.

Is there a linked server created in the SQL Server where you are connected to?

Answer 1

Hi @Kenny Gua ,
Please check if your linked server exists using this query:

SELECT * FROM sys.servers

If it not exists, we need to add a new linked server, for example:

EXEC master.dbo.sp_addlinkedserver @server = N'servername', @srvproduct=N'SQL Server'  
Go  
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'servername', @locallogin = NULL , @useself = N'False', @rmtuser = N'remoteuser', @rmtpassword = N'password'  
GO

Please refer to this article which might help.
In addition, we also can use OPENDATASOURCE to create an ad hoc connection to another server, and Ad Hoc Distributed Queries option should be enabled in order to open a connection to a remote server,
for example:

EXEC sp_configure 'show advanced options', 1  
RECONFIGURE WITH OVERRIDE  
EXEC sp_configure 'Ad Hoc Distributed Queries', 1  
RECONFIGURE WITH OVERRIDE  
Go  
SELECT *    
FROM OPENDATASOURCE('SQLNCLI',    
    'Data Source=servername; Catalog=databasename;User ID=SQLLogin;Password=userpassword;')    
.DBTRB_BACK.dbo.[rule];

Best Regards,
Amelia

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.