By Garth Jones

Before starting to write a SQL Server Reporting Services (SSRS) report, you need to create a SQL query that will be used within the report and I always recommend that you create your query within SQL Server Management Studio (SSMS).

How do you troubleshoot queries when you get an error?

When you use SSMS, it is fairly easy to troubleshoot query issues because SSMS will provide you with information about the error.

Take the following example. You’ll notice that SSMS tells me there is a syntax error and it is found on Line 5.

SQL Queries Troubleshooting Tip-Syntax Error

You might ask, “Which line is Line 5?” Notice that the red arrow in the screenshot below is pointing to my cursor. Now see that the black arrow is telling me that I’m on Line 2. Given this information, I know that a few more lines down will be the incorrect syntax.

SQL Queries Troubleshooting Tip-Line Information

Quickly looking at that line I can tell that my query has the wrong quotation marks within it. After I fix the quotation marks this query will work great.

Once the query is good to go, I copy it to SQL Server Data Tools for Visual Studio 2013 (SSDT) / SQL Server Business Intelligence Development Studio (BIDS).