SQL Server multiple ways to connect across servers

Source: Internet
Author: User
Tags constant ole table name

This article summarizes the way SQL Server connects across servers.

1, OpenDataSource

Open the database example directly with this statement in SQL:

OPENDATASOURCE(
'SQLOLEDB','Data Source=TQDBSV001
;User ID=fish;Password=2312').RackDB.dbo.CS

This approach is relatively simple, but there is the disadvantage is that the speed is very slow.

2, OPENROWSET

Includes all the connection information needed to access remote data from an OLE DB source. This approach is an alternative to connecting the server to the table, and is a specific way to connect and access remote data using OLE DB. The OPENROWSET function can be referenced as a table name in the FROM clause of the query. Depending on the capabilities of OLE DB Provider, the OPENROWSET function can also be referenced as a target table for an Insert, Update, or Delete statement. Although the query may return multiple result sets, OPENROWSET only returns the first result set. Grammar:

OPENROWSET ( 'provider_name'
  , { 'datasource' ; 'user_id' ; 'password'
    | 'provider_string' }
  , { [ catalog.] [ schema.] object
    | 'query' }
  )

Parameters:

' provider_name ': A character string that represents the affinity name of the OLE DB provider specified in the login.

' provider_name ' has no preset value.

' DataSource ': a string constant that corresponds to a particular OLE DB data source.

' DataSource ' is the Dbprop_init_datasource property that is to be routed to the provider's IDBProperties interface to initialize the provider. Generally, this string includes the database file name, the database server name, or the provider's knowledge and use to find the name of the database.

' user_id ': is the user name string constant to be routed to the specified OLE DB Provider. USER_ID Specifies the security content of the line and is routed with the Dbprop_auth_userid property to initialize the provider.

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.