Help for Linked server code

Question

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?

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.