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
