Saturday, 13 October 2012

OpenDataSource OpenQuery OpenRowset

--- 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')

delete OPENQUERY ([MURALI-HP], 'SELECT employeeid FROM mssqltips.dbo.employee WHERE employeeid=6')

No comments:

Post a Comment