Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions

Answer this question

Old Excel files after SQL2000 > SQL2005 migration

PeterTPeterT
SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..
Tzu-Yie
Thank you very much!

That actually worked well

Have a nice weekend!
PCarrier

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.
jimmimacc
We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!
shp21

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.
Sridhar

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available
zuoliang
New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.
Andri
There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.
Hal Kilmer

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.
Stortik
We're talking this over internally now, I'll see what I can find out.
wmmihaa
Also note I filed a bug for this internally so we will get this fixed.
william123
I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this
didier_g
Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source Maybe in the dsn or in msquery itself

We have not at any time used qouted identifiers, so that would not be a problem.
Tomas Scott
How do you get to this VBA macro I can't seem to find it under macros or VBA editor.
Old Excel files after SQL2000 > SQL2005 migration

Answer this question