SQL statement query two tables in different databases on the server

Join query of two tables in different databases on the same server

This method is only applicable to databases on the same server.
1. Create two databases: Database A (Table a) and database B (Table B ).
2. Allocate the same account permissions to the two databases.
3. Use the following statement during query:

The code is as follows:	Copy code
Select * from A. dbo. a, B. dbo. B where A. dbo. a. id = B. dbo. B. id

 
The result of this SQL statement is the data with the same IDs in tables A and B in databases a and B.

The above method is a simple connection table query for different databases on the same server.

Query the connection between two tables in different databases on different servers

Use SQL statements to connect two tables in different databases on two different servers. Initially, the SQL statements are written as follows:

Select * from Product p inner join
Opendatasource ('sqloledb', 'data Source = Macaco-Online; user ID = sa; password = sa password; '). Company. dbo. Product p2
On P. PID = p2.PID
The following error occurs during go execution:

-----------------------------------------------------------------

The SQL Server of message 15281, level 16, status 1, and 1st blocks access to the STATEMENT 'openrowset/OpenDatasource 'of the 'ad Hoc Distributed Queries' component, this component is disabled as part of the server security configuration. The system administrator can enable 'ad Hoc Distributed Queries 'by using sp_configure '. For more information about enabling 'ad Hoc Distributed querys', see "peripheral application configurator" in SQL Server books online ".

-----------------------------------------------------------------

Well, this prompt seems very clear. From the above prompt, we can know that as long as the Ad Hoc Distributed Queries component of the peripheral application configurator is enabled, we can immediately open the peripheral application configurator of the database server on our machine and find the Ad hoc Distributed Queries component and enable it (steps: right-click your database server, and choose "aspect" from the shortcut menu. In the displayed window, a drop-down list named aspect is displayed. Click on the items first, and then select the "peripheral application configurator" option, in the following aspect property window, change the status of AdHocremoteQueriesEnabled to true and click OK)

Now you can execute the preceding query statement.
This does not seem to satisfy our needs.

For example, what should we do if we want to make such a query in our own program? I can't keep it on like this, but this is a waste of security on the server. Well, I won't just reduce the security level of my server.

Okay, now we need to use SQL commands to enable and disable this component. But how can this problem be solved? Don't try to analyze the reminder when the above error occurs. We can know that using SP_Configure, we can enable and disable this component. Now our SQL command is changed as follows:

The code is as follows:

Copy code

Exec sp_configure 'show advanced options', 1 Reconfigure Exec sp_configure 'ad Hoc Distributed Queries ', 1 Reconfigure Go Select * from Product p inner join Opendatasource ('sqloledb', 'data Source = Macaco-Online; user ID = sa; password = sa password; '). Company. dbo. Product p2 On P. PID = p2.PID Go Exec sp_configure 'ad Hoc Distributed Queries ', 0 Reconfigure Exec sp_configure 'show advanced options', 0 Reconfigure   Go

In this way, you can enable this component when you need to enable it (note: ad Hoc Distributed Queries is an advanced configuration, so you must enable Show advanced options first. That is to say, whether you want to enable Ad Hoc Distributed Queries or disable it, you must ensure that show advanced options is enabled) some of my friends wondered what type of commands I should execute in the program because of the stored procedure and SQL statement. In fact, when executing commands on the SqlCommand object, we use Microsoft SQL Server Management. studio creates a new query window. In this query window, you can execute any SqlCommand object. Therefore, you only need to replace the linefeed in the preceding SQL command with a space to form a string and then assign the value to the CommadnTest attribute of the SqlCommand object and then execute it.