Microsoft KB Archive/318144

= HOW TO: Use the Enhanced Debugger in Visual Studio .NET (SQL Debugging) =

PSS ID Number: 318144

Article Last Modified on 10/26/2002

-

The information in this article applies to:


 * Microsoft Visual Studio .NET (2002), Professional Edition
 * Microsoft Visual Studio .NET (2002), Enterprise Architect Edition
 * Microsoft Visual Studio .NET (2002), Enterprise Developer Edition
 * Microsoft Visual Studio .NET (2002), Academic Edition

-



This article was previously published under Q318144



IN THIS TASK

 * SUMMARY
 * ** Requirements
 * Use the Enhanced Debugger in Visual Studio .NET (SQL Debugging)
 * Complete Code Listing (Form1.vb)
 * Verify That It Works
 * Troubleshooting
 * REFERENCES



SUMMARY
This step-by-step article shows how to set up and use SQL Debugging in a small Windows-based program.

back to the top

Requirements
The following items describe the recommended hardware, software, network infrastructure, skills and knowledge, and service packs that you will need:
 * Microsoft Windows 2000 Professional (or Server), or Windows XP Professional (or Server) with the Microsoft .NET Framework installed.
 * Microsoft SQL Server 7.0, or later, with the Northwind database.
 * Visual Studio .NET Professional Edition, Enterprise Architect Edition, or Enterprise Developer Edition

Applies To:
 * Visual Studio .NET
 * Microsoft Visual Basic .NET
 * C#
 * SQL Server

back to the top

Use the Enhanced Debugger in Visual Studio .NET (SQL Debugging)
The following steps explain how to create a Windows Application project in Visual Studio .NET, configure your computer for SQL debugging, create a Button Click event handler that executes a stored procedure in the Northwind database, and then set a breakpoint in the stored procedure for debugging purposes:  On the Start menu, point to Programs, point to Microsoft Visual Studio .NET, and then click Microsoft Visual Studio .NET. Click the New Project button. In Project Types, click Visual Basic Projects. In Templates, click Windows Application. In the Name text box, type HowToSQLDebug, and then click OK. Press CTRL+ALT+S to open Server Explorer. Right-click Data Connections, and then click Add Connection. In Data Link Properties, type localhost in the '''1. Select or enter a server name''' dropdown list box. Under '''2. Enter information to log on to the server, in the User name text box, type sa, and then click to select Blank Password''' (or type the appropriate credentials to connect to your SQL Server installation). In '''3. Select the database on the server, click Northwind, and then click OK'''. You must set up the MSSQLServer service to run under an account that has Administrative rights. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> On the Start menu, click Control Panel, double-click Administrative Tools, and then double-click Services.</li> In Services, locate the MSSQLSERVER service. Right-click the MSSQLSERVER service, and then click Properties.</li> Click the Log On tab.</li> Type the appropriate credentials for an account that has Administrator rights on the local computer, and then click OK. (If you receive a dialog box to notify you that the account you have added has been given the Log on as a Service right, click OK.).</li> Right-click the MSSQLSERVER service, and then click Restart. The service restarts, and then logs on by using the service account that you specified in step d.</li></ol> </li> Set up the permissions for DCOM. The steps for this vary, depending on your platform, so it is best to visit the following Microsoft Web site, and then follow the steps for your system:

Configuring DCOM for SQL Debugging

For example, the steps for a computer running Windows XP are the following: <ol style="list-style-type: lower-alpha;"> On the Start menu, click Control Panel, double-click Administrative Tools, and then open the Component Services snap-in.</li> Expand Component Services, expand Computers, and then expand My Computer.</li> Right-click My Computer, and then click Properties.</li> Click the Default COM Security tab.</li> Under Access Permissions, click Edit Default.</li> Add your account to the list of users.</li></ol> </li> Turn on SQL Debugging at the Project level. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> In Solution Explorer, right-click the HowToSqlDebug project, and then click Properties.</li> In the left pane, expand Configuration Properties, and then click Debugging.</li> <li>In the lower-right corner, click to select the check box for the SQL Server debugging option, and then click OK.</li></ol> </li> <li>Press CTRL+ALT+X to open the ToolBox. From the Windows Forms group, drag a Button to Form1.vb [Design].</li> <li> Double-click Button1 to create a Click event handler. At the top, add the following namespace declarations so that you can access related classes by using shorthand notation: Imports System.Data Imports System.Data.SqlClient </li> <li> In the event handler, type the following sample code, which calls the stored procedure that you will debug: Dim cn As New SqlConnection(&quot;server=localhost;database=northwind;trusted_connection=true&quot;)

Dim cmd As New SqlCommand(&quot;CustOrdersDetail&quot;, cn)

cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New SqlParameter(&quot;@OrderID&quot;, SqlDbType.Int)).Value = 10256

cn.Open cmd.ExecuteReader(CommandBehavior.CloseConnection) MessageBox.Show(&quot;SPROC Successfully Executed&quot;) </li> <li>Set a breakpoint in the stored procedure. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>Press CTRL+ALT+S to open Server Explorer.</li> <li>Expand Data Connections, expand  , and then expand Stored Procedures.</li> <li>Double-click CustOrdersDetail. Position the insertion point anywhere in the WHERE clause, and then press F9. A burgundy-colored dot appears to the left, which indicates that a breakpoint is set.</li></ol> </li></ol>

back to the top

Complete Code Listing (Form1.vb)
Imports System.Data Imports System.Data.SqlClient

Public Class Form1 Inherits System.Windows.Forms.Form


 * 1) Region &quot; Windows Form Designer generated code &quot;

Public Sub New MyBase.New

'Windows Form Designer requires this call. InitializeComponent

'Add any initialization after the InitializeComponent call.

End Sub

'Form overrides dispose to clean up the component list. Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose End If       End If        MyBase.Dispose(disposing) End Sub

'Required by Windows Form Designer. Private components As System.ComponentModel.IContainer

'NOTE: Windows Form Designer requires the following procedure. 'You can modified the procedure by using the Windows Form Designer. 'Do not modify the procedure by using the code editor. Friend WithEvents Button1 As System.Windows.Forms.Button <System.Diagnostics.DebuggerStepThrough> Private Sub InitializeComponent Me.Button1 = New System.Windows.Forms.Button Me.SuspendLayout '       'Button1 '       Me.Button1.Location = New System.Drawing.Point(128, 40) Me.Button1.Name = &quot;Button1&quot; Me.Button1.TabIndex = 0 Me.Button1.Text = &quot;Button1&quot; '       'Form1 '       Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) Me.ClientSize = New System.Drawing.Size(292, 266) Me.Controls.AddRange(New System.Windows.Forms.Control {Me.Button1}) Me.Name = &quot;Form1&quot; Me.Text = &quot;Form1&quot; Me.ResumeLayout(False)

End Sub


 * 1) End Region

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim cn As New SqlConnection(&quot;server=localhost;database=northwind;trusted_connection=true&quot;) Dim cmd As New SqlCommand(&quot;CustOrdersDetail&quot;, cn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New SqlParameter(&quot;@OrderID&quot;, SqlDbType.Int)).Value = 10256

cn.Open cmd.ExecuteReader(CommandBehavior.CloseConnection) MessageBox.Show(&quot;SPROC Successfully Executed&quot;) End Sub End Class back to the top

Verify That It Works

 * 1) Press F5 to run the program in debug mode.
 * 2) When the form loads, click Button1.
 * 3) The program stops on the SELECT statement (note that although you set the breakpoint in the WHERE clause, SQL treats all of the code in the SQL block as one instruction).
 * 4) Press F11 to step into the stored procedure.
 * 5) The SQL statement runs, and then the message box appears.

back to the top

Troubleshooting
You must use additional steps to debug stored procedures that are found on a Desktop Engine installation. To view these steps, visit the following Microsoft Web site:

Enabling SQL Debugging on SQL 2000 Desktop Engines

back to the top

<div class="references_section">