SQLServer cross-database query Implementation Method

This article provides an SQL statement used to display queries between different databases on the same server. Note that the current connected user must have permissions for both databases.
The complete expression of objects in SQL statements in SQL Server is:

[DatabaseServer]. [DatabaseName]. [DatabaseSchema]. [DatabaseObject]

This article provides an SQL statement used to display queries between different databases on the same server. Note that the current connected user must have permissions for both databases.
Copy codeThe Code is as follows:
Select *
From CfteaDb1.dbo. CfteaTable1 inner join CfteaDb2.dbo. CfteaTable2
On CfteaDb1.dbo. CfteaTable1.Id = CfteaDb2.dbo. CfteaTable2.Id

The test is successful in Management Studio of SQL Server 2008 R2.

Below are some additional information

Provides special connection information without using the server name of the link and uses it as part of the four-part object name.

Syntax
OPENDATASOURCE (provider_name, init_string)

Parameters
Provider_name

Register as the name of the PROGID of the ole db provider used to access the data source. The data type of provider_name is char, with no default value.

Init_string

Connection string, which will be passed to the IDataInitialize interface of the target provider. The provider string syntax is based on key value pairs. These key value pairs are separated by semicolons, for example, "keyword1 = value; keyword2 = value ."

In Microsoft®The basic syntax is defined in the Data Access SDK. For information about supported specific keyword value pairs, see the documentation in the provider. The following table lists the most common keywords in the init_string parameter.

Keywords	Ole db attributes	Valid value and description
Data Source	DBPROP_INIT_DATASOURCE	Name of the data source to be connected. Different providers use different methods to explain this. For the SQL Server OLE DB provider, this indicates the name of the Server. For the Jet ole db provider, This specifies the full path of the. mdb file or. xls file.
Location	DBPROP_INIT_LOCATION	The location of the database to be connected.
Extended attributes	DBPROP_INIT_PROVIDERSTRING	The connection string specified by the provider.
Connection timeout	DBPROP_INIT_TIMEOUT	Timeout value. After the timeout value, the connection attempt will fail.
User ID	DBPROP_AUTH_USERID	The User ID used for the connection.
Password	DBPROP_AUTH_PASSWORD	The password used for the connection.
Directory	DBPROP_INIT_CATALOG	The initial or default directory name when you connect to the data source.

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

Similar to the OPENROWSET function, OPENDATASOURCE should only reference the infrequently accessed ole db data sources. For any data sources with a slight Access frequency, define the linked server for them. Both OPENDATASOURCE and OPENROWSET cannot provide all the functions defined by the linked server, such as security management and directory information query. All connection information (including passwords) must be provided each time OPENDATASOURCE is called ).

Example
The following example accesses data from a table in another instance of SQL Server.
Copy codeThe Code is as follows:
SELECT *
From opendatasource (
'Sqlodb ',
'Data Source = ServerName; User ID = MyUID; Password = mypass'
). Northwind. dbo. Categories

The following is an example of a query. It queries an Excel spreadsheet through the ole db provider for Jet.
Copy codeThe Code is 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