SQL Server cross-Library query--distributed query

Source: http://www.cnblogs.com/doosmile/archive/2012/03/16/2400646.html

--Connect remote SQL or insert data with OPENROWSET

--if it's just temporary access, you can use OPENROWSET directly

--query Example

SELECT * FROM OPENROWSET (' SQLOLEDB '

, ' SQL Server name '; ' User name '; ' Password

, database name. dbo. Table name)

You need to enable AD HOC distributed for cross-Library queries Queries
Enable AD Hoc distributed Queries:
exec sp_configure ' show advanced options ', 1
Reconfigure
exec sp_configure ' Ad Hoc distributed Queries ', 1
Reconfigure
When you are finished using, close ad Hoc distributed Queries:
exec sp_configure ' Ad Hoc distributed Queries ', 0
Reconfigure
exec sp_configure ' show advanced options ', 0
Reconfigure


--import Example

SELECT * Into table from OPENROWSET (' SQLOLEDB '

, ' SQL Server name '; ' User name '; ' Password

, database name. dbo. Table name)


--Create a linked server

exec sp_addlinkedserver ' srv_lnk ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '

exec sp_addlinkedsrvlogin ' Srv_lnk ', ' false ', NULL, ' username ', ' password '

Go


--query Example

SELECT * from Srv_lnk. Database name. dbo. Table name


--import Example

SELECT * into table from Srv_lnk. Database name. dbo. Table name


--Remove linked server when no longer in use

exec sp_dropserver ' srv_lnk ', ' droplogins '

Go

--The following example accesses data from a table that is in another instance of SQL Server.

SELECT *from opendatasource (' SQLOLEDB ', ' Data source=servername; User Id=myuid; Password=mypass '). Northwind.dbo.Categories

--Here is an example of a query that queries an EXCEL spreadsheet by using the OLE DB provider for Jet.

Select *from opendatasource (' microsoft.jet.oledb.4.0 ', ' Data source= ' C:financeaccount.xls '; User id=admin; password=; Extended properties=excel 5.0 ') ... xactions

SQL Server cross-Library query--distributed query