How to use OPENDATASOURCE to pull data from SQL Server to SQL Server
OPENDATASOURCE is a SQL inbuilt function use to pull data from other data sources by utilising the ODBC drivers.
Syntax:
select * from opendatasource('SQLNCLI','Data Source=SERVERNAME;Integrated Security=SSPI').master.sys.database_files
Above syntax is for the Login trying to enter into other server where he has the privileges.
If User1 logged in Server 1 tries to enter the Server2 with User2, how should he proceed ??
Below syntax will allow User1 to log into server2 with user2:
select * from OPENDATASOURCE('SQLNCLI','DATA SOURCE=SERVERNAME;USER ID=sarmadh;PASSWORD=sarmadh').test_123.dbo.sqldb_info
Explanation: In the above Syntax
SERVERNAME = This should be modified accordingly
Test_123 = catalog name or database Name
dbo = Schema name
Sqldb_info = Object name (table/view/ etc)
Hope the above information helps
Thanks
