Solved

MS Query with Excel 2003 no longer working with MS SQL 2008 error 2571

Posted on 2010-09-02
12
1,234 Views
Last Modified: 2012-05-10
I need the existing System data source (QL Live) to work again.

Having upgraded to SQL 2008 from SQL2000 all my MS Query's (used in Excel 2003) fail due to the increased security introduced in SQL 2005.  This is the Microsft SQL Server Login error:


Connection Failed:
SQLState: '37000'
SQL Server Error: 2571
[Microsoft][ODBC SQL Server Driver][SQL Server]User 'query' does not have permission to run DBCC TRACEON

MS Query uses a system DSN called QL Live (part of the build for all machines on our network)


The solution to this error (posted elsewhere on this site) is to replace the 'Application Name:' from the default "Microsoft(r) Query", to something else, such as "Microsoft Query 2003".

However if I replace this manually, it doesn't always work (still fails) and when I open the options on the query, it hasn't changed the application name.  I find using lowercase and no spaces seems less likely to fail. On other occasions, when it does change, it doesn't save with the spreadsheet, so the next time I open the spreadsheet with the embeded query, it fails again (because the Application Name is still wrong).

I have created a new system data source, and this strangely defaults to an Application Name of 'Microsoft Query 2003' (and works).  

I've searched the registry in vain for 'Application Name' and it doesn't appear to stored in an ini file either.  What's more, I cannot find the Application names of "Microsoft(r) Query" or "Microsoft Query 2003" anywhere in the registry either.

What I have found is the 'QL Live' entry in the registry under "My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\"

I have manually added a value in here of "Application Name" with a value of ms2003

I then rebooted my machine, but alas, it still didn't appear in the SQL Server Login options on MS Query.

I would ideally like roll out a fix via group policy.

0
Comment
Question by:vstromkk
  • 4
  • 3
  • 2
  • +1
12 Comments
 
LVL 11

Expert Comment

by:jkpieterse
ID: 33594321
As far as I know, In Excel files, the connection strings to external databases are just that: a connection string. SO my guess would be that you'd need to update the connection strings in the Excel files.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 33594364
Hi,

Have you installed the SQL Server Native Client 10.0? that is, the net libraries?

In OSDBC what do you get towards the bottom of the list in the Drivers tab? What version is SQL Server?

Does the DSN test okay?

Can you find a copy of odbcping and use that to verify that the DSN and credentials work okay?

Regards
  David
0
 

Author Comment

by:vstromkk
ID: 33596724
kpieterse - yes as I can't find any changes to files on the PC, the query arguments must be being stored in the excel file somewhere.  if I save the query it's stored as a *.dqy file in the default directory, but that's not the case for the excel spreadsheets that I have been looking at.  But I could be looking in the wrong place.

dtodd - Hmm, I've posed the question to my software house whether I should be running SQL native client rather than MDAC 2.8.  Waiting for their response.

MS Query is version 11.5510.8221
MS SQL Server ODBC Driver version 03.85.1132
SQL Driver 2000.85.1132.00

When creating a new MS Query all works fine (as it defaults to an 'Application Name' of 'Microsoft Office 2003')
0
 
LVL 11

Accepted Solution

by:
jkpieterse earned 500 total points
ID: 33597039
Look on the data tab of the ribbon (you do have Excel 2007, right?), there is a connections tab there where you can look at the connections.
Alternatively, download my QueryManager tool from www.jkp-ads.com/download.asp
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 33597049
I see now that you have 2003. In that case, my query manager will prove helpful.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 51

Expert Comment

by:Mark Wills
ID: 33597367
Agree with David, make sure you are using SQLNCLI10 drivers...

But... SQLNCLI10 should have installed with SQL 2008

Check first by reading : http://msdn.microsoft.com/en-us/library/ms131321.aspx

and if needed, download the latest via (scroll down a bit over half way) : http://www.microsoft.com/downloads/en/details.aspx?familyid=CEB4346F-657F-4D28-83F5-AAE0C5C83D52&displaylang=en

Then create your new connection using the SQL native drivers



0
 

Author Comment

by:vstromkk
ID: 33625981
Thanks for your responses, I have found why the workaround (type in a different application name) was not working:  the query has to be run.  This highlighted another problem - i.e. if there was another Query, this would not work if the existing new application name was used.
So first Query; Application Name: ms2003, run query, wait for spreadsheet to recalc (twice for some reason), second query; Application Name: ms2k3, run query, wait for spreadsheet to recalc (twice).  

Look at spreadsheet fully populated with accurate data with a smile and save.

Reopen spreadsheet - no errors and queries run in the background as expected.

I am still at a loss as to where Excel is storing this information - so currently no way of running a job in the background to fix it.


I will be looking at jkpieterse's query manager and of course am interested in using the native client if that provides performance improvements.

I did try installing SQL 2008 standard on my PC (XP SP3) so I could administer the server, and it crashed out with the installer trying to run all the time.  In the end had to go back a few weeks to a restore point and manualy unregister and delete the windows installer.  When I feel brave I'll try installing the Native client again

Can I leave awarding points until I've identified if either jkpieterse or dtodd:was a solution as opposed to mine?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33626204
No problems as far as I am concerned - *laughing* I am pretty much out of it anyway...

jkpieterse, got some pretty cool utilities there, have bookmarked your site :)
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 33626700
Thanx Mark. Enjoy them!

If you don't like any of them, let me know.
If you like them, let others know.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 33631219
Hi,

I was suggesting installing the client connectivity, not the whole thing! Especially it is unlikely that standard is licenced for your machine. I'm presuming that the cal - Client Access Licence - is though.

Regards
  David
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33912317
Second that recommendation :) Great tools live there :)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now