Experience in connecting and querying 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*FromProduct pInner Join 
  
  
Opendatasource ('Sqlodb','Data Source = Macaco-Online; user ID = sa; password = sa password ;'). Company. dbo. Product p2
  
  
OnP. PID = p2.PID
  
  
Go
 
 

An error message is prompted during 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 you only need to enable the Ad Hoc Distributed Queries component of the peripheral application configurator, open the peripheral application configurator of the database server on your machine, find the Ad Hoc Distributed Queries component, and enable it. Step: Right-click your database server, right-click "aspect" and select "aspect". In the displayed window, click "aspect" in the drop-down list and select "peripheral application configurator, in the following section, change the status of AdHocremoteQueriesEnabled to "true" and click "OK ).

Now you can execute the preceding query statement.

However, this does not seem to meet our needs.

For example, what should we do if we want to make such a query in our own program? You can't always drive it like this, but there is a loss of server security. Well, I wouldn'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? Do not try to analyze the reminder when the above error occurs. We can use SP_Configure to enable and disable this component. Now we can change the SQL command as follows:

 
 

  
  
ExecSp_configure'Show advanced options', 1
  
  
Reconfigure
  
  
ExecSp_configure'Ad Hoc Distributed Queries', 1
  
  
Reconfigure
  
  
Go
  
  
 
  
  
Select*FromProduct pInner Join 
  
  
Opendatasource ('Sqlodb','Data Source = Macaco-Online; user ID = sa; password = sa password ;'). Company. dbo. Product p2
  
  
OnP. PID = p2.PID
  
  
Go
  
  
 
  
  
ExecSp_configure'Ad Hoc Distributed Queries', 0
  
  
Reconfigure
  
  
ExecSp_configure'Show advanced options', 0
  
  
Reconfigure
  
  

  
  
 
  
  
Go
 
 

Well, we can enable this feature when we need to enable this component. Note: Ad Hoc Distributed Queries is an advanced configuration, so we need to 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 friends are confused. You are a stored procedure and an SQL statement, what type of commands should I execute in the program? In fact, when executing commands on the SqlCommand object, it is like using Microsoft SQL Server Management Studio to create a query window. In this query window, you can execute any SqlCommand object, so you only need to replace the linefeed in the preceding SQL command with a space to form a string and assign it to the CommadnTest attribute of the SqlCommand object, then execute the command. It seems that I have not used this method to create a database. However, if you have enough user permissions to connect to the database, there should be no problem !)

Additional reading:

Source: blog

Author: Yan Zedong

1,Query two tables of two different databases on one server

Select * from database 1. Table Name A, database 2. Table name B on A. Id = B. Ids

2,Two or multiple tables in the same database

Two-Table querySelect * from TB1 left join TB2 On tb1.where = tb2.whereOrSelect * from TB1, TB2 where tb1.where = tb2.where

Multi-Table querySelect * from tb1 left join tb2 on tb1.where = tb2.where inner join fig (tb3.wehre = tb2.where) inner join tb4 .......................

Cross join: the product of the flute. without any constraints, the number of rows in a table is multiplied by the number of rows in another table.
Left join: return all rows of table name 1. Replace null values with records that do not meet the on condition in table name 2.
Ristmjoin: returns all rows with table name 2. Replace null values with records that do not meet the on condition in table name 1.
Full join: return all records in the two tables. Replace null values with records that do not meet the on condition.
Inner jon: returns only records that meet the on condition in both tables.

Link: http://www.cnblogs.com/zouzedong/archive/2011/02/27/1966534.html

Edit recommendations]

1. Some common SQL statements
2. SQL statement used to query the table structure
3. Use SQL statements to query Time Periods
4. Use SQL statements to delete duplicate records
5. SQL statement for determining whether a field exists