May 3, 2007 at 12:22 pm
The query parses fine under Query Editor but generates an error when run under the Database Tuning Advisor. Here's the error message.
"All the events in the workload were ignored due to syntax errors. The most common reason for this error is that the database to connect has not been set correctly."
What does "not set correctly" mean? Any idea what the real problem is?
Bill
P.S. Unfortunately the query is mind-numbingly long or I'd reproduce it here.
May 4, 2007 at 5:59 am
You said it parses fine, but does it actually run? There are a few things that will let the query parse fine, but not run.
May 16, 2007 at 2:20 am
Are you using the same account for both trying the query for yourself and with DTA? The default db's could be different. Furthermore there some issues with DTA pre SP2. Strongly suggest you to apply SP2.
Cheers
Zubeyir
December 20, 2007 at 1:02 pm
I'm running into this issue as well. What are the settings that would cause the query to parse and run, but err out in the DTA.
December 20, 2007 at 1:12 pm
select your query in Management Studio in a query window. Right click and select "Analyse Query in Database Tuning Advisor" then enter the same credentials when you prompted that you use to login to the server. Then you should experience no problem.
Zubeyir
December 20, 2007 at 1:52 pm
I tried it using both trusted connections(as a sysadmin) and with the sa login/password. Both ways result in the same error.
I saved off the query in a trace, and attempted to DTA that as well. Same connection error.
December 21, 2007 at 7:37 am
this may have no relevance but if you're running SSMS from a client have you service packed the client tools on the workstation? It seems most patch their servers but you should also apply the same packs and patches to all sql client tool sets ( even if they don't have a local database )
It can sometimes cause awful problems.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 21, 2007 at 11:13 am
I have a solution - don't use DTA! 🙂 Tune your stuff yourself or hire someone to do it for you. DTA often does bad things.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 10, 2009 at 7:02 am
Hi
This really help me to work , and also i achived the performance by implementing the mentioned indexed scripts ,
As i am new to optimizing the queris directly applying to DTA it will not good why because there is no mind work .
for this the rest there is asny way any links is available it will be helpfull for me
Tanks in advance for alll
regards
sat
June 11, 2009 at 7:15 am
Also, if you have variables they have to be defined with values or it will error and if you have temp tables they have to be real tables...at least in my experience.
November 2, 2009 at 10:03 am
It is probably due to a temp table that is created via SELECT * INTO [#YOUR_TEMP_TABLE]
You will need to explicitly create your #YOUR_TEMP_TABLE at the top of the script.
June 23, 2010 at 1:35 pm
June 24, 2010 at 7:03 am
You're welcome 🙂
September 8, 2011 at 7:23 am
I ran into the same error and google brought me here. Just to contribute to the knowledge base: my solution was using three-part name (DATABASE.dbo.TABLE) in all objects referenced in the query (tables, views, functions). That solved my problem.
😎
July 9, 2012 at 6:28 am
You need to select the appropriate database name for option "Database for workload analysis:" and select databases and tables to tune.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply