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;