Solved

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

Posted on 2010-09-02
12
1,242 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

863 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

18 Experts available now in Live!

Get 1:1 Help Now