469,579 Members | 1,214 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

OpenRowSet BUG?

Hi Folks,

I am trying to load data from a table in MS Access to SQL Server 2000
using T-SQL OPENROWSET. When I select data from the remote database (MS
Access) using SQL Query analyzer, the columns do NOT appear in the same
order as seen in Access directly.

For e.g. if Access table has columns Cy, Cx, Cz the output in Query
analyzer appears as Cx, Cy, Cz. It appears to arrange the fields
alphabetically. This causes problems when I do a 'insert into select *
from' as the field definitions do not agree.

Is this a bug or is there a setting in Access/SQL which I am missing?
Also, please let me know if there is a workaround for this issue.

Thanks in advance!


Jul 23 '05 #1
3 1735
If you want the columns in a certain order, then don't use SELECT *,
just specify the order explicitly:

insert into dbo.MyTable (col1, col2, col3)
select Cy, Cx, Cz
from OPENROWSET(...)

In general, you shouldn't use SELECT *, because then you avoid issues
like this, and your code still works even if someone adds a column to
the table or changes the column order.


Jul 23 '05 #2
Thanks Simon. I will use that workaround for now. I am still quite
surprised though.
Also if I use DTS and load the data from Access to SQL it goes through

Did anyone else come across the same problem?


Jul 23 '05 #3
It seems likely to be an issue with the Jet OLE DB provider:


You probably don't see an issue in DTS because the Transform Data task
maps columns by name, not position (as far as possible), so if both
source and target have the same column names, then it will work fine..

However, from the point of view of writing SQL statements and queries,
I would suggest always naming the columns explicitly - as I mentioned
previously, it makes your code more robust, and it's generally
considered good practice to do so.


Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Mahesh Hardikar | last post: by
3 posts views Thread by Pachydermitis | last post: by
1 post views Thread by Rudi | last post: by
1 post views Thread by Shilpa | last post: by
2 posts views Thread by Gugale at Lincoln | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.