OpenDataSource, openquery, and OpenRowset usage

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

 

 

I,OpenDataSource ##############################

1. Operate SQL server data, such as SQL Server/Oracle

Format:

Select top 10 *

From OpenDataSource (

'Providername ',

'Data source = servername; user id = loginuser; Password = password'

). [Databasename]. DBO. tablename

The above is my company database, ordermaster is the sales form

For example:

Select top 10 *

From OpenDataSource (

'Sqlodb ',

'Data source = globe01; user id = xw_cai; Password = 661'

). [Order]. DBO. ordermaster

2. operate an Excel table

Format:

Select *

From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = "Excel file storage path"; user id =; Password =; extended properties = Excel 5.0 ')... sheetname $

Sheetname: name of the workspace. The name of the workspace must be suffixed with $.

For example:

Select *

From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = "G:/workeveryday/daydo/orderlist"; user id =; Password =; extended properties = Excel 5.0 ')... orderlist $

3. Operate text files

Format:

Select * from

OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Text; HDR = no; database = path for storing text file')... [textname # TXT]

Textnameis the suffix file name, And txtis the suffix of the suffix file, so you cannot change the suffix to.. The format textname.txt is incorrect, and the correct textname # TXT. Database = '/' the last/cannot be less.

Example:

Select * from

OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Text; HDR = no; database = E:/study/C #/data/learning materials/text/')... [textname # TXT]

Ii. openquery#############################

 

Syntax

Openquery (pai_server, 'query ')

Variable description

Pai_server

The identifier used to connect to other database servers. That is, you have established a connection with a database service.

 

'Query'

SQL query statement

Format:

Select *

From openquery (servername/IPaddress, 'select * From databasename. DBO. tablename ')

Example:

Select *

From openquery ([192.168.42.43], 'select top 10 * from pubs. DBO. Authors order by au_lname, au_fname ')

Note: The name of the connection, and the name and IP address of the server can be accessed.

Iii. OpenRowSet ###########################

Select * From OpenRowSet ('msdasql ',

'Driver = Microsoft Visual FoxPro driver; sourcetype = DBF; sourcedb = D :/',

'Select * from Table name ')

Bytes -------------------------------------------------------------------------------------------------------------

This includes all the metadata required to access the local data from the ole db data source. This method is an alternative to accessing a data table on the Indexing Server, and is a specific method that uses ole db metadata to access data on the indexing end. The OpenRowSet function can be used as the data table name in the from clause of the query result to perform the test. The capability of the root role ole db provider. The OpenRowSet function can also be used as an insert, update, or delete statement object table. However, checking the result may retrieve multiple result sets. OpenRowSet only returns the first result set.

Limit Method

OpenRowSet ('provider _ name'

, {'Datasource '; 'user _ id'; 'Password'

| 'Provider _ string '}

, {[Catalog.] [schema.] Object

| 'Query '}

)

Reference

'Provider _ name'

Represents the character string of the parent and namespace of the ole db Provider specified in login. Provider_name has no preset value.

'Datasource'

Is the String constant that corresponds to the special ole db data source. Datasource is sent to the idbproperties interface of the provider to initialize the dbprop_init_datasource adequacy of the provider. Generally, this string includes the information statement name, the information server name, or the name of the provider that the provider understands and uses to find the information.

'User _ id'

Is the user name limit String constant that will be sent to the specified ole db Provider. User_id specifies the security content of the zookeeper and sends it to the initialization provider as dbprop_auth_userid authentication.

'Password'

Is the user password string constant that will be sent to the ole db Provider by the producer. When initializing the provider, the password is sent using dbprop_auth_password encoding.

'Provider _ string'

It is a string sent to the specific provider that initializes ole db provider using dbprop_init_providerstring annotation. Provider_string generally contains all the metadata required when initializing the provider.

Catalog

Indicates the item category or item name.

Schema

Is the structure description of the specified object or the name of the specified object.

Object

The name of the object that you want to operate on is unique.

'Query'

The String constant number sent to the provider and sent by the provider. Microsoftr SQL server? This check item is not handled, but the result of the check item returned by the provider is returned by the reason ). Checking tables is useful when querying tables that are exposed by the command provider instead of the database table name. As long as the query Provider supports the ole db command object and its trusted interface, the producer server supports the checking worker. If you need to renew your database, please refer to the SQL Server OLE DB programmer's reference.

Zookeeper

If the ole db Provider supports multiple object and structure descriptions in a specified resource, the object and structure description name are required. If ole db provider does not support this function, you can omit the value of catalog and schema.

If the provider only supports the endpoint description, you must specify the name of the two parts in schema. Object format. If the provider only supports the metadata category, you must specify the names of the three parts in catalog. schema. Object format.

OpenRowSet does not accept changes as its reference.

Permission

The OpenRowSet permission is determined by the permission of the user name secret sent by the consumer to the ole db Provider.

Example

A. Use OpenRowSet with select and Microsoft ole db provider for SQL Server

In the following example, Microsoft ole db provider for SQL Server is used to access the authors table of the pubs resource on the client server named seattle1. Initialize the provider from datasource, user_id, and password, and use select to define the returned data column set.

Format:

Select .*

From OpenRowSet ('sqloledb', 'servername'; 'loginuser'; 'Password ',

'Select * from [databasename]. DBO. tablename order by colname1, colname12 ') as

Description: query the provided Driver (sqloledb queries SQL Server). query the data in the data tablename table in databasename under SQL Server servername. In fact, the user permission is loginuser.

Example:

Use pubs

Go

Select .*

From OpenRowSet ('sqloledb', 'seattle1'; 'sa'; 'mypass ',

'Select * from pubs. DBO. Authors order by au_lname, au_fname ') as

 

Go

B. Use OpenRowSet with objects and OLE DB provider for ODBC

The following example uses the ole db provider for ODBC and SQL Server ODBC notebook to access the authors table of the pubs Information table on the local server named seattle1. Initialize the provider using the provider_string specified by the ODBC compiler method used by the ODBC provider, and use the catalog. schema. object definition method to define the returned data column set.

Format: the connection method is the ODBC data driver.

Select .*

From OpenRowSet ('msdasql ',

'Driver = {SQL Server}; server = servername; uid = loginuser; Pwd = password,

[Databasename]. DBO. tablename) as

Order by colname1, colname12

 

Use pubs

Go

Select .*

From OpenRowSet ('msdasql ',

'Driver = {SQL Server}; server = seattle1; uid = sa; Pwd = mypass ',

Pubs. DBO. Authors) as

Order by A. au_lname, A. au_fname

Go

C. Use Microsoft ole db provider for jet

The following example uses Microsoft ole db provider for jet to access the orders table in the Microsoft Access northwind resource.

1. The following example is provided with security access.

1,

Use pubs

Go

Select .*

From OpenRowSet ('Microsoft. Jet. oledb.4.0 ',

'C:/msoffice/access/samplesorthwind. mdb '; 'admin'; 'mypwd', orders)

As

Go

2,

Select * From OpenRowSet ('Microsoft. Jet. oledb.4.0 ',

'E:/study/access/test. mdb '; 'admin'; '', 'select * from student ')

Or

Select .*

From OpenRowSet ('Microsoft. Jet. oledb.4.0 ',

'E:/study/access/test. mdb '; 'admin'; '', student)

As

2. Take excel as an example. Excel must be installed

Select *

From

OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'excel 5.0; HDR = yes; database = G:/workeveryday/daydo/orderlist', orderlist $)

Database = G:/workeveryday/daydo/orderlist is the name and path of the Excel table.

, Orderlist $ is the workspace name Microsoft. jet. oledb.4.0 is an Excel driver. You can also use Microsoft. jet. oledb.5.0, Microsoft. jet. oledb.8.0: the number of Windows 2003 and SQL Server 2000 used in the test. The value $ in orderlist $ must not be less than the value $. If yes, the error cannot be reported. The value $ indicates work zone 3. using VFP (DBF file name)

Select * From OpenRowSet ('msdasql ', 'driver = Microsoft Visual FoxPro driver; sourcetype = DBF; sourcedb = D:/', 'select * from [temp. DBF] ')

Description: sourcetype is a data source type, sourcedb is a data source, followed by an SQL query statement that operates DBF.

 

D. Use other data tables in OpenRowSet and inner join

In the following example, all the data in the MERs table of the SQL Server northwind data table on the same computer is selected, and all the resources in the orders table of the access northwind resource.

The following example is provided to set the security access.

Use pubs

Go

Select C. *, O .*

From northwind. DBO. Customers as C inner join

OpenRowSet ('Microsoft. Jet. oledb.4.0 ',

'C:/msoffice/access/samplesorthwind. mdb '; 'admin'; 'mypwd', orders)

As O

On C. customerid = O. customerid

Go

E. Insert the record set of the stored procedure into the virtual table (the Global Virtual table is not recommended for the stored procedure)

For example:

Select * into # T

From openquery ([192.168.42.43], 'exec [order]. DBO. or_select_backlistdetail ''bk0607190001 ''')

The following error occurs: Create Table # T must be created first, and the parameters of this virtual table must be the same as those of the stored procedure.

Insert into # T

Exec [order]. DBO. or_select_backlistdetail 'bk0607190001'

F: insert the corresponding files (DBF, exec, and so on) into SQL

Insert

-- Select * from

OpenRowSet ('msdasql ', 'driver = Microsoft Visual FoxPro driver; sourcetype = DBF; sourcedb = D :/',

'Select * from temp. dbf ')

Select Top 100 orderno, shipto from ordermaster

Note:

1. The two numeric segments have the same structure (length and type)

2. Ensure that no fields are null or empty in the export table.

3. Change the preceding statement to select * from TMP. DBF, that is, do not []