--- OpenDataSource Example for connectiong another SQL Server and collecting data
---(Provides ad hoc connection information as part of a four-part object name without using a linked server name)
select * from
opendatasource('SQLNCLI','Data Source=muralixp;Integrated Security=SSPI;Connect Timeout=10').master.sys.databases
select * from
opendatasource('SQLNCLI','Data Source=murali-HP;User ID=sa;Password=xxxxxxx;Connect Timeout=10').master.sys.databases
--- OpenDataSource Example for connectiong Excel Sheet and collecting data. Shall mention linked server if we get error
--- Provides ad hoc connection information as part of a four-part object name without using a linked server name
select * from
opendatasource('Microsoft.Jet.OLEDB.4.0',
'Data Source=I:\SQL Server Own Material\Testing\Stars_Info.xls;Extended Properties=EXCEL 12.0')...[Info$] ;
--- OpenRowSet Example for connection another SQL Server and collecting data
select a.* from
openrowset('SQLNCLI','Server=murali-hp;trusted_connection=yes;',
'select * from master.sys.databases') as a
select *
from openrowset('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=I:\SQL Server Own Material\Testing\Stars_Info.xlsx;',
'select * from [info$]')
--- OpenQuery Example, connecting to linked server and collecting data
select * from openquery([MURALI-HP], 'SELECT employeeid FROM mssqltips.dbo.employee WHERE employeeid>1')
