PRB: Distributed Query With Jet Changes Native Column Order

ID: Q196293

The information in this article applies to:

Microsoft SQL Server version 7.0
Microsoft OLE DB Provider for Jet, version 3.51

SYMPTOMS

When you use SQL Server 7.0 Distributed Query with Jet 4.0 OLE DB Provider, if you do not ask for specific columns to retrieve, the result-set will show the columns sorted alphabetically instead of using the natural order of columns in the native table.

STATUS

This is by design. With a select * you are not guaranteed any column order with SQL Distributed query. If you want a specific column order then specify a column list.

MORE INFORMATION

Microsoft SQL Server version 7.0 provides the ability to perform queries against OLE DB providers. This is done by using the OpenQuery or OpenRowset Transact-SQL functions or by using a query with four-part names including a linked-server name.

Steps to Reproduce Behavior

Run the following query in SQL Query Analyzer:

SELECT * From OpenRowset('Microsoft.Jet.OLEDB.4.0',
'd:\program files\microsoft office\office\samples\northwind.mdb'; 'admin';'', customers)

The query will return a result-set where the columns are sorted alphabetically.

If you open the .mdb file using Access 97 you will see a different order of columns.

Using Jet OLEDB provider directly preserves the natural order of columns as shown by the ADO code given below:


   Dim cn As ADODB.Connection, rs As ADODB.Recordset, I As Long

     Set cn = New ADODB.Connection
     Set rs = New ADODB.Recordset
     cn.Provider = "Microsoft.Jet.OLEDB.4.0"
     cn.Open "d:\program files\microsoft
                 office\office\samples\northwind.mdb"
     rs.Open "SELECT * FROM Customers", cn, adOpenKeyset, adLockReadOnly,
                                        adCmdText
     For I = 0 To rs.Fields.Count - 1
       Debug.Print rs(I).Name
     Next I
     rs.Close
     cn.Close

REFERENCES

For more information on setting up and using Distributed Queries, search on sp_addlinkedserver, OpenQuery, OpenRowset, and related topics in SQL 7.0 Books Online.

For more information on Jet OLEDB provider, refer to Data Access SDK documentation in the Microsoft Developer Network (MSDN) documentation.

Additional query words:

Keywords : kbDatabase kbOLEDB kbProvider
Version : WINDOWS:3.51; winnt:7.0
Platform : WINDOWS winnt
Issue type : kbprb