Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 196293 - Last Review: March 14, 2005 - Revision: 2.2
PRB: Distributed Query With Jet Changes Native Column Order
This article was previously published under Q196293
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.
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.
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
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.
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft OLE DB Provider for Jet 3.51
| kbdatabase kbprb kbprovider KB196293 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate