Query parses fine; DTA reports syntax error

  • 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.

     

     

  • 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.

  • 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

  • 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.

  • 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

  • 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.

  • 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/

  • 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

  • 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

  • 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.

  • 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.

  • Thank you Zubeyir

    your solution has helped me and the error stoped.

    msb

    http://www.compuhot.com

  • You're welcome 🙂

  • 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.

    😎

  • 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