Finding SQL Server Code Errors using Query Analyzer

Problem
When dealing with large scripts it is sometimes difficult to find all errors in your code prior to executing the code.  You can review the code and look for syntax issues as well as do searches to make sure the code is intact, but when dealing with a large amount of code some problems are sure to surface.

Solution
By using Query Analyzer or a query window in SQL Server Management Studio to parse your query to determine if there are any syntax errors is a great step to ensure your code will work when executed.  This is a simple process by either pasting your code into a query window or opening the query file.  You can then click the check mark or press Ctrl+F5 to parse the code.  If your code is free of any syntax errors query analyzer will return the message "The command(s) completed successfully.".

But if there are errors finding the problem code could be a bit of a challenge, but luckily Query Analyzer has a built in function to find the code in question by just double clicking on the error line.

Here is example code that we will parse. These are bogus objects that don't exist and some of the code is broken so we can see how query analyzer handles the errors.

When we parse the code using Query Analyzer we get the following errors.  The results using SQL Server Management Studio are very similiar.

At this point we see there are two errors; one referring to a join issue and the other referring to incorrect syntax.  From the error message it says the name of the Procedure and the line #.  We could do a search for the procedure name and look for the line #, but a simpler approach is to double click on the red error line and Query Analyzer will bring you right to the line of code in question.

After clicking on error 1

After clicking on error 2

For the first error the "join" clause was not included and for the second the "from" clause was excluded.  At this point the code can be fixed and parsed again.  When the parse is run for the second time there are no errors.

Now that the parse returns no errors, we are pretty confident the code will execute. So at this point we can execute the query by pressing F5 or clicking on the execute icon.  After we execute the code, we get the following list of new errors.  Unfortunately the parse process only looks for syntax issues not object issues, so new errors surface.

By doing the same process of double clicking on the red error message, Query Analyzer will take us right to the line of code in question.

If you run this code in your test environment you will see that the views do not get created, but the stored procedure still gets created even though the view referenced in the code did not get created.  Although this will not catch everything, it is still a much simpler process to double click on the line in question instead of having to search through your code to find the line of code that is in error.

Note: if you edit the source code and you insert new lines or delete lines of code the reference marks in the error messages to the code in question no longer work.  So this is one draw back to using Query Analyzer to find and fix the problems.  One approach would be to copy the source code to an editor and fix the code after you find the errors in Query Analyzer.

Next Steps

• Take a look at this tip for other query analyzer shortcuts
• Along with using the parse and execute functions also use the error shortcuts to find the code in question
• Use this process for either SQL Server 2000 or SQL Server 2005

About the author

Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

Related Resources

• More SQL Server DBA Tips...