locked
Old Excel files after SQL2000 > SQL2005 migration RRS feed

  • Question

  • 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?

    Monday, March 27, 2006 2:40 PM

Answers

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

    Wednesday, March 29, 2006 2:35 AM
  • 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.

    Friday, March 31, 2006 5:39 AM

All replies

  • 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!

     

    Monday, March 27, 2006 3:26 PM
  • 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.

    Wednesday, March 29, 2006 2:35 AM
  • 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.

     

    Wednesday, March 29, 2006 7:42 AM
  • We're talking this over internally now, I'll see what I can find out.
    Wednesday, March 29, 2006 6:05 PM
  • 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.

    Wednesday, March 29, 2006 11:52 PM
  • 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.

    Friday, March 31, 2006 5:39 AM
  • Also note I filed a bug for this internally so we will get this fixed.
    Friday, March 31, 2006 5:47 AM
  • Thank you very much!

    That actually worked well

    Have a nice weekend!

    Friday, March 31, 2006 11:32 AM
  • How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

     

    Thursday, April 20, 2006 2:40 PM
  • 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.
    Thursday, April 20, 2006 5:22 PM
  • 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?

    Thursday, April 20, 2006 5:26 PM
  • 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.
    Thursday, April 20, 2006 5:34 PM
  • Any news on a fix for this issue

     

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

    Tuesday, May 23, 2006 5:28 PM
  • SP1 did not change anything for us..

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

     

    Wednesday, May 24, 2006 1:34 PM
  • I had this problem on an Excel sheet that was getting data from SQL Server 2000 (it started to happen when we migrated the DBMS to SQL Server 2005) and deleting the "Microsoft® Query" string from the "Application Name" field in Excel's "SQL Server login" box (under Options) solved it
    Monday, March 12, 2007 5:41 PM
  • Service Pack 2 for SQL Server 2005 did not fix this issue either.  There has to be another workaround to this issue other than allowing the SQL login used to be a sysadmin.  My environment has a ton of spreadsheets that do this type of action, so altering every single one is not much of an option.
    Monday, June 25, 2007 1:38 PM
  • I talked to the SNAC (SQL Native Client) PM and he indicated that MDAC is designed to work better with Excel, best bet is to create your DSN using the MDAC SQL Server driver and not SQL Native Client driver.
    Wednesday, June 27, 2007 8:38 PM
  • Yes and we did not fix this in SQL Server 2005 SP2, this is correct.

    Best bet is to switch to using MDAC SQL Server driver.

    Wednesday, June 27, 2007 8:39 PM
  • After spending most of today trying to solve this problem I have found a solution...
    Excel embeds the query string into the excel document and therefore if ODBC is changed it has no effect.

    By opening the excel document and replacing the connection string I found it to work seemlessly.

    See following....

    Open the worksheet and place the cursor on a cell within the cell range of
    the query.  Press Alt-F11 to open the VBEditor.  Press Ctrl-G to open the
    Intermediate window.  Type the command:  ? ActiveCell.QueryTable.Connection.  
    The embedded connection string will be echoed back to the screen.  Put double
    quotes around the string and update the connection information with the new
    server info.  Move the cursor to the beginning of the connection string and
    insert the following in front of the string:  

    ActiveCell.QueryTable.Connection =

    Move the cursor to the end of the string and press enter.  This will store
    the updated connection string back into the worksheet.  Try your query now.

    Repeat for any other embedded queries in your worksheet.


    -------------


    I have set SQL Server to use a fixed port, therefore the string needed the default port changed.


    Good luck!


    Scott


    Tuesday, September 4, 2007 2:43 PM
  • Install the SQL Native client sqlncli.msi change your odbc connection to use it and the problem will go away
    We just upgraded the server and have gone crazy until we found your posting.

    I know that you have undoubtly solved it by now since your post is a 18 months old.
    Thursday, November 8, 2007 6:48 PM
  • Thanks!

     

    Your two year old message saved my day. Just after an otherwise succesful 2000->2005 upgrade one of our users told us that his old Excel sheet couldn't connect to SQL Server. And they were important. Your advice came to rescue.

     

    Thank you very much.

     

    Esa

    Monday, April 21, 2008 10:18 AM
  • This problem is related to the upgrade to SQL 2005 and the changes to 'public' permissions, in that they cannot run DBCC TRACEON(208) to enable Quoted Identifiers.

    A simple way to fix the problem in an Excel document:
    - for each of the affected queries, right click and select "Refresh Query"
    - this will cause a series of error messages ('public', 'us_english', 'db_name', etc)
    - next, it will bring up the ODBC selection pop-up
    - on the ODBC selection pop-up, pick the correct connection and click OK
    - ** IMPORTANT ** on the next screen, expand the "Advanced Options >>" (or just "Options >>") and remove "Microsoft @Query" from the Application Name.
    - hit okay and then "Refresh" the query again.
    --Save the document

    picked this from a url , works a treat ....

    Tuesday, September 1, 2009 3:21 PM
  • I have the same problem but with mail merge and Microsoft Word 2000, using a system DSN (SQL Server), does anyone know a way round this one please! can't seem to find anything similar around......
    Wednesday, September 30, 2009 8:42 AM
  • Perfect, thats the solution. Thanks

    Thursday, August 30, 2012 2:15 PM