Microsoft KB Archive/196293

= PRB: Distributed Query With Jet Changes Native Column Order =

Article ID: 196293

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft OLE DB Provider for Jet 3.51

-



This article was previously published under Q196293



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

