Home > Uncategorized > Running Ad Hoc Queries on Remote SQL Server without Linked Server

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=ServerName\InstanceName;Trusted_Connection=yes;’,


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

 

 

 

    

Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: