Connect To Remote Database Without A Linked Server using OPENDATASOURCE In SQL SERVER

According to MSDN, OPENDATASOURCE Provides ad hoc connection information as part of a four-part object name without using a linked server name.

The syntax is:- OPENDATASOURCE ( provider_name, init_string ) click here to know more about this.First You need to enable Ad Hoc Distributed Queries component of SQL Server.

sp_configure 'show advanced options', 1;

RECONFIGURE;
GO

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

Then also enable Allow Inprocess property of SQL SERVER NATIVE CLIENT SQLNCL from Server Objects/Linked Server/Provider in SSMS.Then run your sql query as

SELECT *

FROM   OPENDATASOURCE('SQLNCLI', 'Data Source=servername;user id=username;password=password')

.[databasename].schemaname.tablename

OR, you can pass dyanamic parameter by using dyanamic sql query as

DECLARE @server nvarchar(256)='servername'

EXEC ('

SELECT *

FROM   OPENDATASOURCE(''SQLNCLI'', ''Data Source='+@server+';user id=username;password=password'')

.[databasename].schemaname.tablename')

Using the OpenDataSource function is the same as using a four-part name to access a Linked Server, except the OpenDataSource() function defines the link within the function instead of referencing a predefined linked server. While defining the link in code bypasses the linked server requirement, if the link location changes, then the change will affect every query that uses OpenDataSource(). In addition, OpenDataSource() will not accept variables as parameters.

Read more at http://programming4.us/database/2068.aspx#JdLyiAcwPwqwAmQf.99