Running Ad Hoc Queries on Remote SQL Server without Linked Server

How to run Ad Hoc T-SQL Queries between SQL Server Instances (on Local or Remote Location) without Linked Server.

 

To execute Ad Hoc T-SQL you need to enable ‘Ad Hoc Distributed Queries’
server option from sp_configure.

 

sp_configure
‘Ad Hoc Distributed Queries’,1

reconfigure
with
override

 

 

TO access remote data from an OLE DB data source
you can use OPENROWSET to Run

 

SELECT a.*

FROM
OPENROWSET(‘SQLNCLI’,Server=ServerNameInstanceName;Trusted_Connection=yes;’,


‘SELECT name from master.sys.databases’)
AS a;

 

 

 

    

Leave a Reply

Your email address will not be published. Required fields are marked *