:: infotechGuyz.com ::
> For the IT community by the IT community

• BlackBerry Server (BES)
• Excel Macro/VBA
• Exchange 2007
• Exchange 2010
• Lync 2010
• Scripting/Command-line
• SQL Server 2008
• SQL Server 2012
• Team Foundation Server
• VMware
• Windows 7
• Windows 8
• Windows Server 2008
• Windows Server 2012

SQL Server 2008 R2 T-SQL Debugging

 

 

You are able to debug T-SQL from within the SQL Server development environment . Yes, you could do this kind of thing using Visual Studio, but DB developers should be able to debug in the environment where they generally develop their SQL statements—within SSMS. SQL Server 2008 offers this ability, and it works well. The complicated part of debugging can be starting the debugger. It is not all that difficult but can be less than apparent for some. As an example, let's say you can want to debug a stored procedure. To do this, you RightClick on the stored procedure in the Object Explorer and choose Script Stored Procedure As, Execute To, New Query Editor Window, and a script for executing the procedure is produced. If the stored procedure has parameters, you add the SQL to assign a value to those parameters to the script. Now you are ready to debug this script and the related stored procedure. To start debugging, you LeftClick on the green arrow on the SQL Server menu bar. When you start debugging, several new debugging windows are added to the SSMS display, and the query editor pane displays a yellow arrow in the left margin then to the line in the script that is about to be run. You can now use the debug toolbar at the top of the SSMS screen to step all through your code. If you LeftClick the Step Into button, the current statement executes, and the script progresses to the then available statement. The debugging environment enables you to view values assigned to variables, review the call stack, set breakpoints, and execute debugging much like you would do in development environments such as Visual Studio.

Another slick new option available with SQL Server 2008 is the ability to execute a script on multiple servers at once. Multiserver queries permit the contents of a single query editor window to be run against all the servers defined in a given registered server group.  After the group is generated and servers are registered in the group, you can RightClick on the group and choose the New Query option to create a query window that can be run against all the servers in the group. LeftClick on the Execute button, and the query is run against all the servers.  4.30 displays a server group named MyTestGroup containing three servers registered in that group, a sample query to run against these servers, and a single result window that displays the results of the query for all servers in the group.