DBCC TRACEON in Microsoft Excel

I recently spent some time migrating data to a new Microsoft SQL (MSSQL) Server, from SQL 2000 to SQL 2008. One thing that we missed, and that broke after migration, was Excel documents containing Pivot tables. Users were reporting they’d stopped working, now displaying the following error:

"[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

TRACEON is intended to enable certain trace flags and requires membership of the sysadmin role. Not something I wanted to give out. I spent a few hours investigating but none of the Excel functionality was executing DBCC TRACEON. So what was going on?

It appears as if SQL 2005 and later checks if the client is sending an application identifier of “Microosft® Query” and if it is, tries to turn quoted identifiers on (SET QUOTED IDENTIFIER ON). This can be mitigated by adjusting the connection string in Excel documents and changing “APP=Microsoft® Query”.

You can change “APP=Microsoft® Query” either inside Excel, or by editing the file in Notepad and replacing the word “Query” with something else of the same length. SQL then doesn’t try and enable quoted identifiers and all is fine once more.

Published by

Dave Hope

Dave works in IT for a leading UK based retirement developer, in his spare time he enjoys tinkering with technology and rock climbing.

One thought on “DBCC TRACEON in Microsoft Excel”

  1. Hi Dave,

    Thank You very much for this solution. You have saved me from hours of head ache. keep up the good work.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.