Cross server queries in SQL Server


Without linking the servers you can do one off queries against another server using the OPENDATASOURCE or OPENROWSET functions. You will need to first enable “Ad Hoc Distributed Queries” as this is disabled by default- this can be achieved using sp_configure (if you’re logged in as sa)

sp_configure 'Ad Hoc Distributed Queries', 1 
reconfigure 

Once active you can use the function like this;

SELECT 
    *
FROM 
    OPENDATASOURCE('SQLOLEDB', 'Data Source=<servername>;User ID=<user>;Password=<password>').<dbname>.<dbo>.<tablename>

There is also an OPENROWSET function;

SELECT 
    source.*
FROM 
    OPENROWSET('SQLOLEDB', 'Data Source=<servername>;User ID=<user>;Password=<password>', 'SELECT * FROM <dbname>.<dbo>.<tablename>') as source

Thanks to Richard who dug this little nugget up from the interwebs.

  1. No comments yet.
(will not be published)