SQL Server cross-Library query implementation method _mssql

Source: Internet
Author: User
Tags ole management studio
This article gives an SQL statement to display the query between different databases on the same server, noting that the current connected user has permission to two libraries
The complete expression of an object in SQL Server SQL statement is:

[Databaseserver]. [DatabaseName]. [Databaseschema]. [Databaseobject]

This article gives an SQL statement to display the query between different databases on the same server, noting that the current connected user has permission to two libraries.
Copy Code code as follows:

SELECT *
From CfteaDb1.dbo.CfteaTable1 INNER JOIN CfteaDb2.dbo.CfteaTable2
On Cfteadb1.dbo.cfteatable1.id=cfteadb2.dbo.cfteatable2.id

The above test succeeded in Management Studio in SQL Server 2008 R2.

Here are some additional information

Does not use the linked server name, but provides special connection information as part of the four-part object name.

Grammar
OpenDataSource (provider_name, init_string)

Parameters
Provider_name

The name of the PROGID registered as the OLE DB provider used to access the data source. The provider_name data type is char and has no default value.

Init_string

Connection strings that will be passed to the IDataInitialize interface of the destination provider. Provider string syntax is based on key value pairs, which are separated by semicolons, for example: "Keyword1=value;" Keyword2=value. "

The basic syntax is defined in the Microsoft®data Access SDK. For information about supported specific keyword value pairs, see the documentation in the provider. The following table lists the most commonly used keywords in the init_string parameter.
Key Words OLE DB Properties valid values and descriptions
Data source Dbprop_init_datasource The name of the data source to connect to. Different providers explain this in a different way. For SQL Server OLE DB providers, this indicates the name of the server. For a Jet OLE DB provider, this indicates the full path of the. mdb file or the. xls file.
Position Dbprop_init_location The location of the database to connect to.
Extended Properties Dbprop_init_providerstring A provider-specific connection string.
Connection Timeout Dbprop_init_timeout Timeout value, after which the connection attempt will fail.
User ID Dbprop_auth_userid The user ID to use for the connection.
Password Dbprop_auth_password The password to use for the connection.
Directory Dbprop_init_catalog The initial or default directory name when connecting to the data source.

Comments
The OPENDATASOURCE function can be used in the same Transact-SQL syntax location that can use the linked server name. Therefore, you can use OpenDataSource as the first part of a four-part name that refers to the name of a table or view in a SELECT, INSERT, UPDATE, or DELETE statement, or a remote stored procedure in an EXECUTE statement. When executing a remote stored procedure, OPENDATASOURCE should refer to another SQL Server. OpenDataSource does not accept parameter variables.

Like the OPENROWSET function, OpenDataSource should refer only to OLE DB data sources that are infrequently accessed. For any data source with a slightly larger number of accesses, define a linked server for them. Neither OpenDataSource nor OPENROWSET can provide the full functionality of the linked server definition, such as security management and the ability to query directory information. Every time you call OpenDataSource, you must provide all the connection information (including the password).

Example
The following example accesses data from a table that is in another instance of SQL Server.
Copy Code code as follows:

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

The following is an example of a query that queries an EXCEL spreadsheet by using the OLE DB provider for Jet.
Copy Code code as follows:

SELECT *
From OpenDataSource (' microsoft.jet.oledb.4.0 ',
' Data source= ' C:\Finance\account.xls '; User id=admin; password=; Extended properties=excel 5.0 ') ... xactions
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.