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

Source: Internet
Author: User
Tags ole

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.