Most of the time that people connect to a remote database they use linked servers. But have you ever wanted to connect to another database server without creating a linked server? This post will show you how to connect to an ad hoc database server while executing a query.
To accomplish this, one method (samples below) that SQL Server provides is to use opendatasource. Opendatasource allows you to connect and query a remote data source by passing your connection parameters in to the command.
NOTE: Microsoft does not recommend using this method of connecting to a remote database server if this data source is accessed more than a few times. If the data source is frequently used, they recommend that you create a linked server.
Connect To Remote Database Using Windows Authentication
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteServerName;Integrated Security=SSPI').Billing.dbo.Invoices
Connect To Remote Database Using SQL Server Authentication
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteServerName;user id=username;password=password).Billing.dbo.Invoices
In the examples above, SQLNCLI tells SQL Server to connect to the database using an SQL Server Native Client provider. This will automatically use the latest SQL Server Native Client OLE DB Provider.
Reference: http://technet.microsoft.com/en-us/library/ms179856.aspx – OPENDATASOURCE (Transact-SQL)