Name of the four parts of Distributed Query, OpenDataSource (), openquery (), OpenRowSet ()

Http://lihb810.blog.163.com/blog/static/59451162200862685726759/

Local Distributed Query:

1. Use a name composed of four parts. Server Name: Database Name: Username: Object Name (table, view)

2. Use the OpenDataSource () function

OpenDataSource (provider_name, init_string)

Initstring is a semicolon-separated string, which contains many parameters: data source, location, extended attributes, connection timeout, user ID, password, directory, etc.

If you use OpenDataSource () to connect to another SQL server instance running on Windows, Kerberos security authorization is required.

Instance: Select AA, BB from OpenDataSource ('sqlodb', 'datasource = 255.255.255.255; user id = Joe; Password = J'). database. DBO. Table

 

When accessing access, because access only has one database, and the table does not have the owner's concept of morality, the database and owner can be omitted in all four parts.

Select AA, BB from

OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'Data source = C:/chai_customers.mdb ')... Customers customers is the table name

 

When accessing excel, this distributed sqlserver query will use ole db to call the jet engine to open the Excel table. The OpenDataSource () function only provides the server name section in four sections, like access, the database name and owner name can be omitted.

Update OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'Data source = C:/chai_schules.xls; user id = admin; Password =; extended properties = Excel 5.0 ')... Tour

Set [base camp] = 'aaa' where id = '2' tour is a previously defined naming range. [base camp] and ID are field names.

)

 

Direct transmission of distributed queries is to query on external data sources and return the results to sqlserver, reducing the traffic of servers (external data sources) and customers (sqlserver,

However, the external data source query method is required to directly pass the query.

If you want to use direct transfer to query and modify data, the type of the Remote Data Source will decide whether to modify the data locally or remotely:

1. If you modify data of another sqlserver, the modification is performed on a remote sqlserver.

2. If you modify the data from a non-sqlserver source, the corresponding data provider will decide where to modify the data. In most cases, directly passing the query will only read data remotely, return the selected data to sqlserver, modify it in sqlserver, and then return the Modification result to the data source.

 

Among the four distributed queries, the names composed of the four parts and the openquery () function are the best two methods. Both of them can use predefined links, if the two methods have similar effects, we recommend that you use the names of the four parts because they support SQL Server to automatically optimize distributed queries.

 

The openquery () function is equivalent to a table in SQL DML. There are two parameters: the name of the linked server and directly passing the query,

Select instance: Select * From openquery (chai_schedule, 'select * from tour where id = "2" ') chai_schedule is the name range, ID is the field name, And chai_schedule is the linked server.

Update instance: openquery () Only returns the data to be modified through the jet engine. The actual modification is performed on the current SQL Server, and the operation result is written back to the external data source.

Update openquery (chai_schedule, 'select * from tour where id = "2 "') set [base camp] = 'aaa' where id = '2' chai_schedule linked server tour is a previously defined naming range. [base camp] and ID are field names.

 

The OpenRowSet () function directly transmits the OpenDataSource function for query. The difference is that the OpenRowSet function has a specified parameter while directly passing the query.

Select * From OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'c:/chai_customers.mdb'; 'admin'; '', 'select * from MERs where id = 1 ')

 

When using the OpenRowSet function to execute the update command, the function should be used as the table to be modified. The WHERE clause in the update command will be processed by the OpenRowSet function in direct transmission mode.

Update OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'c:/chai_customers.mdb'; 'admin'; '', 'select * from MERs where id = 1') set conn = 'www'

MERs indicates the table name ID and conn indicates the field name.