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;