question

KennyGua-7621 avatar image
0 Votes"
KennyGua-7621 asked AmeliaGu-msft answered

Help for Linked server code

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?

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

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

0 Votes 0 ·

1 Answer

AmeliaGu-msft avatar image
1 Vote"
AmeliaGu-msft answered

Hi @KennyGua-7621,
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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.