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.
Subscribe to:
Post Comments (Atom)
1 comment:
It works.
Great!
Post a Comment