SQL Server Common cross-Library queries, SQL Servers cross-Library

Blog Source: http://www.bkjia.com/sjkqy/984843.html

1, Temporary use method OpenRowset

A. Using OPENROWSET with SELECT with Microsoft OLE DB Provider for SQL Server
The following example uses Microsoft OLE DB Provider for SQL Server to access the authors table of the pubs database on a remote data server named REMOTE1. Initializes the provider from DataSource, user_id, and password, and uses SELECT to query the data table.

SELECT * from OPENROWSET (' SQLOLEDB ', ' remote1 '; ') Sa '; ' Mypass ', [pubs].dbo.authors] as A

B. Using OPENROWSET with OLE DB Provider for ODBC
The following example uses OLE DB Provider for ODBC with the SQL Server ODBC driver to access the authors table of the pubs database on a remote data server named REMOTE1. Specifies the provider_string initialization provider with the ODBC syntax used by the ODBC provider, and uses the Catalog.schema.object syntax to define the returned database tables.

SELECT * FROM OPENROWSET (' Msdasql ', ' Driver={sql Server}; server=remote1; Uid=sa; Pwd=mypass ', [pubs].dbo.pubs] as A

2, if the frequent access or large amount of data, it is recommended to use a linked server

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

--query Example
SELECT * from Srv_lnk. Database name. dbo. Table name

--import Example
SELECT * into table from Srv_lnk. Database name. dbo. Table name

--delete the linked server after processing is complete
exec sp_dropserver ' srv_lnk ', ' droplogins '

SQL Server Common cross-Library queries, SQL Servers cross-Library