By: Greg Robidoux | Updated: 2021-10-05 | Comments | Related: > Tools
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 a query window in SQL Server Management Studio (SSMS) 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 SSMS 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 SSMS 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 SSMS handles the errors.
--create view CREATE VIEW vwTest AS SELECT * FROM tableDoesNotExist1 t1 INNER JOIN tableDoesNotExist2 t2 ON t1.id = t2.id GO --insert new records INSERT INTO vwTest VALUES(1) INSERT INTO vwTest VALUES(2,3) INSERT INTO vwTest VALUES(3) INSERT INTO vwTest VALUES(4,5,6) GO --create view 2 CREATE VIEW vwTest2 AS SELECT * FROM tableDoesNotExist3 t1 INNER tableDoesNotExist4 t2 ON t1.id = t2.id GO --create proc CREATE PROC uspTest AS SELECT * vwTest2 GO
When we parse the code using SSMS we get the following errors.
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 SSMS 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.
--create view CREATE VIEW vwTest AS SELECT * FROM tableDoesNotExist1 t1 INNER JOIN tableDoesNotExist2 t2 ON t1.id = t2.id GO --insert new records INSERT INTO vwTest VALUES(1) INSERT INTO vwTest VALUES(2,3) INSERT INTO vwTest VALUES(3) INSERT INTO vwTest VALUES(4,5,6) GO --create view 2 CREATE VIEW vwTest2 AS SELECT * FROM tableDoesNotExist3 t1 INNER JOIN tableDoesNotExist4 t2 ON t1.id = t2.id GO --create proc CREATE PROC uspTest AS SELECT * FROM vwTest2 GO
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.
This is the first error:
This is the second error:
This is the third error:
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 this 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 SSMS, but double clicking on the error message still works fine.
Next Steps
- Take a look at this tip for other SQL Server Management Studio Keyboard Shortcuts
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-10-05