Microsoft SQL Server forum - OpenRowSet BUG?

Author

OpenRowSet BUG?

Bkr

2005-07-18, 8:24 pm

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!

Bhaskar

Simon Hayes

2005-07-19, 3:23 am

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.

Simon

Bkr

2005-07-19, 9:23 am

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
fine.

Did anyone else come across the same problem?

Bhaskar

Simon Hayes

2005-07-19, 9:23 am

It seems likely to be an issue with the Jet OLE DB provider:

http://support.microsoft.com/?id=299484

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.

Simon

Sponsored Links