Quickly Find SQL Server Code Errors when using SSMS

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

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips