PRB: Distributed Query With Jet Changes Native Column Order

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: 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