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.
Related posts:
