Solving Excel's "DBCC TRACEON" error with brute force and ignorance

We use excel reports extensively for reporting. They are fast to create and everyone can read and manipulate them. However there are the occasional gotchas. In the past, I have had to muck about with macros to fix connection string errors.

We upgraded to sql server 2008 on the weekend. Following that, nearly every data aware spreadsheet started giving "User 'public' does not have permission to run DBCC TRACEON" errors.

The problem, and a resolution is described here. Older versions of Excel/MS query identified themselves by including APP=Microsoft® Query;" in the connection string. MS also hard coded a check for "Microsoft® Query" in sql server which then runs DBCC TRACEON for no apparent reason.

Under sql server 2000, this in fine, but under sql server 2008, this fails as DBCC requires admin permissions.

The correct approach is change the connection string as described in the linked posts. Previously I have done this with macros. However this time I had 200+ spreadsheets to change :(.

I used a free search and replace utility (ReplaceText) to replace all instances of "APP=Microsoft® Query;" with "APP=Microsoft® WTFIT;". 2 important things here, the search and the replacement text are the same size, and the strings include "App=". Failing to do either will give you a corrupt file.

Problem solved in about an hour. It's not a pretty solution but it's better than spending a week at it.

Changing Excel query connection strings

We use Excel and database queries extensively for reporting purposes. It's quick and easy to set-up, and provides reports that our clients can manipulate.

However I have recently run into a rather painful excel quirk with ODBC connections: Excel stores the database connection string internally. Even if you change the ODBC connection on the computer, excel still uses the original connection from when the query was created.

This bit us when moving the reports to a different machine. Despite having the same ODBC connection set up, excel wouldn't refresh the query and gave the error "[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied."

Aside
A similar problem can happen if you update the database from sql server 2000 to sql server 2005 or 2008. In that case you may get the error "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON." In that case, you need to alter the connection string to change "APP=Microsoft® Query" to something else. Apparently MS hard coded a check for "Microsoft® Query" in sql server which then runs DBCC TRACEON for no apparent reason. I suggest "APP=WTFWYT"


Changing one query
(from here)
"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 =
"

Changing multiple queries in a spreadsheet
(modified from here)
You need to create the following macro (change connection string to suit, see above) and run it in each spreadsheet requiring change. (See the steps below). Once the macro has run successfully, delete it before saving the spreadsheet.

Sub ChangeConnections()
Dim sh As Worksheet, qy As QueryTable
For Each ws In ActiveWorkbook.Sheets
For Each qy In ws.QueryTables
qy.Connection = "..."
On Error Resume Next
qy.Refresh
If Err.Number <> 0 Then MsgBox "Problem refreshing QueryTable: " & Err.Description
Next qy
Next ws
End Sub

Getting the new connection string
The easiest way to find the new connections string is to create a new query and do the ?ActiveCell.QueryTable.Connection trick.