ADO showing "hidden columns" with SQL Native Client

Question

I'm working on a legacy application using VB6 and Classic ASP. We're using disconnected ADO recordsets to pass data back and forth. Normally this works. But what has started happening recently is for any inner/outer join, ADO is including these in the available records to choose from. So when were specifying a column to update (in the cases it errors out, the primary key column), it in turns updates the wrong column (with the same name). I know it's normal for ADO to pull the primary keys for any joined tables, but the default for this is for ADO to hide them. In our case ADO isn't hiding them.

What I've narrowed it down to is the SQL Native Client driver is not working correctly. I can go back to the SQL Server driver (SQL 2000) and it works great, but as soon as I switch back to SQL Native Client, it exhibits the behavior above. I've checked the properties on the open connection and the properties of the recordsets themselves, they match in every instance except one (the count of how many hidden columns there are which makes sense, as SQL Native isn't hiding them).

I've tried everything from deleting the MSADC folder from IIS and re-adding it, I've uninstalled SQL Native and reinstalled it (and subsequently upgraded it to the newest version). I've recreated the ODBC connection several times as well in the process of troubleshooting it. At this point I'm at a loss.

Also one thing to add, it appears SQL Native Client works fine on our other servers and no one else is having this issue. Anyone might have an idea of what could be happening? Thanks!

Edit : Example of what's happening (this occurs on for any query (stored procedures if it matters) and with >= 1 joins of any kind)

select temp_id, temp_value on temp_test inner join another_table on another_table.temp_id = temp_test.temp_id inner join yet_another_table on yet_another_table.another_id = another_table.another_id

this'll produce in the ado recordset :

SQL Native Client

(0) temp_id

(1) temp_value

(2) temp_id (primary key of another_table)

(3) another_id (primary key of yet another_table)

SQL Server driver

(0) temp_id

(1) temp_value

SQL Server 2005 will show it as it should be as : temp_id, temp_value

Answer 1

this occurs on for any query (stored procedures if it matters)

It's not the issue described here is it? :

If a change in the connection string changes the behavior, I would suppose that you have two different schemas, and then two versions of the same stored procedure; and the one that is executed with SQL Nativ Client is the incorrect one.

Answer 2

I have exactly the same scenario, and have had it for over a year on our servers and servers at our client. I never found a solution and as a result we simply have to use the SQL Server driver, which is a shame as SQL Native seems to connect significantly faster.

It's nothing to do with different schemas or different versions of the same stored proceedure as suggested above. I use a file dsn and simply changing the driver name changes the behaviour to/from that mentioned above. It seems to happen to all views (probably stored proceedures too as indicated)

If anyone does find a solution I'd be keen to hear about it.

Warwick