SQL Server cross-Library query implementation method

Provides special connection information as part of a four-part object name without using the linked server name

This article gives an SQL statement to show the query between different databases on the same server, noting that the current connected user has permissions on two libraries
The complete expression for an object in an SQL statement in SQL Server is:

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

This article gives an SQL statement to show the different database queries on the same server, noting that the current connected user has permissions on the two libraries.

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 above tests were successful in Management Studio in SQL Server 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 that is registered as the OLE DB provider used to access the data source. The data type of the provider_name is char and has no default value.

Init_string

The connection string that will be passed to the target provider's IDataInitialize interface. The provider string syntax is based on keyword 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 interpret this in different ways. For SQL server OLE DB providers, this indicates the name of the server. For Jet OLE DB providers, this indicates the full path to the. mdb file or. xls file.
Position	Dbprop_init_location	The location of the database to connect to.
Extended Properties	Dbprop_init_providerstring	Provides a provider-specific connection string.
Connection timed out	Dbprop_init_timeout	Timeout value after which the connection attempt fails.
User ID	Dbprop_auth_userid	The user ID used 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 you execute 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 OLE DB data sources that are infrequently accessed. For any data source with a slightly larger number of accesses, define the linked server for them. Neither OpenDataSource nor OPENROWSET can provide all of the functionality defined by the linked server, 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 CodeThe code is 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 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 http://www.jb51.net/article/30017.htm

SQL Server cross-Library query implementation method