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?

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!

|||

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.

|||

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.

|||We're talking this over internally now, I'll see what I can find out.|||

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.

|||

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.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

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

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?

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

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..