Tutorial: Debug a SQL CLR Stored Procedure in a Development Environment

Requirements

Before attempting this tutorial, you must complete the following tutorials in the order listed:

• Tutorial: Enable SQL CLR Integration
• Tutorial: Create a Sample Database
• Tutorial: Create and Configure a Database Project
• Tutorial: Create, Run, and Call a SQL CLR Stored Procedure

Prepare to Debug in a Development Environment

This section takes you through the process of preparing your Visual Studio project such that you can debug your stored procedure in a development environment either locally or remotely.

Add the SQLCLRTutorialCall Project to the SQLCLRTutorial Solution

1. If you have closed the SQLCLRTutorial solution you created and updated in previous tutorials, open it in Visual Studio.
2. From the Solution Explorer, right-click the SQLCLRTutorial solution and select Add > Existing Project.
3. Browse to the folder containing the SQLCLRTutorialCall project.
4. Select the SQLCLRTutorialCall.cblproj file; then click Open.

   Visual Studio adds the SQLCLRTutorialCall project to the SQLCLRTutorial solution.

Set Properties for the SQLCLRTutorialCall Project

1. From the Solution Explorer, open the Properties for the SQLCLRTurotialCall project.
2. On the Debug tab, check Enable SQL Server debugging, located in the Debug Options group.

   Because Visual Studio runs in 32-bit, and the connection to SQL Server that you created previously using the ADO.NET Connection Editor runs in 32-bit, you need to set the Platform target to build the console application as a 32-bit program before you can execute your stored procedure.

3. On the COBOL tab, select x86 from the Platform target drop-down list.
4. Click Save (); then close the Properties window.

Debug Locally in a Development Environment

This section takes you through the process of locally debugging your published stored procedure in a development environment.

Set the Startup Project

Because the program in the SQLCLRTutorialCall project (Program 1.cbl) calls the SQL CLR stored procedure in the SQLCLRTutorial project (SQLCLRTutorial.cbl), you need to set SQLCLRTutorialCall as the startup project before debugging.

• From the Solution Explorer, right-click the SQLCLRTutorialCall project; then select Set as StartUp Project from the context menu.

Set Breakpoints

To debug from the COBOL client console application into the stored procedure code, you must have a breakpoint set in both the console application code and the stored procedure code. The console application breakpoint is required to access the code in the stored procedure.

1. From the Solution Explorer, double-click the Program1.cbl program to open it in the COBOL editor.
2. Insert a breakpoint on the following lines of code:

   :spReturnCode = call "SQLCLRturorial" (:empid INOUT, :lastname OUT, :firstname OUT)

   and

   goback.

3. From the Solution Explorer, double-click the SQLCLRTutorial.cbl program to open it in the editor.
4. Insert a breakpoint on the following EXEC SQL statement:

   EXEC SQL 
    SELECT
           A.EMPNO 
          ,A.FIRSTNME 
          ,A.LASTNAME 
    INTO  
           :EMP-EMPNO 
          ,:EMP-FIRSTNME 
          ,:EMP-LASTNAME 
      FROM TEST.EMP A 
     WHERE (A.EMPNO = :EMP-EMPNO) 
   END-EXEC

Run the Application

1. Press F5 to start the debugger.
2. When the debugger hits the first breakpoint in the Program1.cbl program, press F5 again to execute the SQLCLRTutorial.cbl stored procedure.
3. While executing the SQLCLRTutorial.cbl stored procedure, optionally step through line by line or examine variables as you would during any debugging process.
4. When the debugger hits the breakpoint on the goback statement for the second time, press F5 again.
   You should see the following in a generated console window as a result of calling the stored procedure:

   User = THOMPSON                 MICHAEL

5. Press F5 to stop debugging.

Debug Remotely in a Development Environment

Takes you through the process of remotely debugging your published stored procedure in a development environment.

Requirements

You must be able to run the Microsoft Remote Debugging Monitor (msvsmon.exe) on the remote machine. To do this, install the Microsoft Visual Studio Remote Debugger on the remote machine. You can download this software from the Microsoft Web site.

Configure a Connection on the Development Machine

When you created the SQLCLRTutorial connection in the ADO.NET Connection Editor, you specified the local SQL Server instance name on your development machine, represented by a "." (dot). You must change this to use a SQL Server instance on a remote machine.

1. Start the ADO.NET Connection Editor. If you need instructions, see To start the ADO.NET Connection Editor as a user.
   Important: If UAC is enabled, start the ADO.NET Connection Editor as an administrator. In this case, see To start the ADO.NET Connection Editor as an administrator for instructions.
2. Select the SQLCLRTutorial connection.
3. Change the value of the Data Source key to point to a SQL Server instance on a remote machine.
4. Do one of the following to set authentication for this SQL Server instance:
   • If you want to connect using Windows authentication (recommended), change the value for the Integrated Security key to True.
   • If you want to connect by providing SQL Server-specific login credentials, type your SQL Server user ID and password into the Value fields for the User ID and Password keys respectively.
5. Save the connection and close the ADO.NET Connection Editor.

Set Properties for the SQLCLRTutorial.Publish Project

1. From the Solution Explorer in Visual Studio, open the properties for the SQLCLRTutorial.Publish project.
2. On the Debug tab, click Edit in the Target Connection String group.
3. In the Server name field, type the name of your remote SQL Server instance.
4. If necessary, make authentication changes to the information in the Log on to the server group.
5. Click OK to save the changes; then save the solution.

Configure Firewall Settings on the Development Machine

Note: See your Microsoft documentation or your firewall vendor documentation for details on opening firewall ports.

Open the following firewall ports:

• TCP: 135 - required
• UDP: 500 and 4500 - required when your domain policy requires that network communication be performed through IPSec.

Configure Firewall Settings on the Remote Machine

Note: See your Microsoft documentation or your firewall vendor documentation for details on opening firewall ports.

Open the following firewall ports:

• TCP: 135, 139, 455 - required
• UDP: 137, 138 - required
• UDP: 500 and 4500 - required when your domain policy requires that network communication be performed through IPSec
• TCP: 80 - required for Web Server debugging

Set Up Symbol Files on the Remote Machine

To debug a managed application remotely, the most recently generated debug symbol files for the application running on the development machine must exist on the same path(s) on the remote machine. These include the .pdb and .idy files.

Note: Native applications require the debug symbol files to be located on the Visual Studio host computer.

1. On the development machine, build the application.
2. Copy the resulting .pdb and .idy files from the development machine to the same directory or directories on the remote machine.

Set Permissions

You must set the appropriate permissions on the remote machine to enable the development machine to access it. For complete information regarding permissions, see the Microsoft Web site.

Install Enterprise Developer or Enterprise Server on the Remote Machine

To successfully debug any application remotely, you must install the same version of Enterprise Developer or Enterprise Server on the remote machine as you have installed and running on the development machine.

Start the Remote Debugging Monitor

Because you are debugging a 32-bit application, you must start the 32-bit version of the Microsoft Remote Debugging Monitor (msvsmon.exe), available from the Start menu.

Also, if UAC is enabled on the remote machine, you must start the Microsoft Remote Debugging Monitor as an administrator.

Set Breakpoints

To debug on the remote machine from the development machine, you must have a breakpoint set in both the console application code and the stored procedure code on the development machine. The console application breakpoint is required to access the code in the stored procedure. On the development machine:

1. From the Solution Explorer, double-click the Program1.cbl program to open it in the COBOL editor.
2. Insert a breakpoint on the following lines of code:

   :spReturnCode = call "SQLCLRturorial" (:empid INOUT, :lastname OUT, :firstname OUT)

   and

   goback.

3. From the Solution Explorer, double-click the SQLCLRTutorial.cbl program to open it in the editor.
4. Insert a breakpoint on the following EXEC SQL statement:

   EXEC SQL 
    SELECT
           A.EMPNO 
          ,A.FIRSTNME 
          ,A.LASTNAME 
    INTO  
           :EMP-EMPNO 
          ,:EMP-FIRSTNME 
          ,:EMP-LASTNAME 
      FROM TEST.EMP A 
     WHERE (A.EMPNO = :EMP-EMPNO) 
   END-EXEC

Run the Application

1. Press F5 to start the debugger.
2. When the debugger hits the first breakpoint in the Program1.cbl program, press F5 again to execute the SQLCLRTutorial.cbl stored procedure.
3. While executing the SQLCLRTutorial.cbl stored procedure, optionally step through line by line or examine variables as you would during any debugging process.
4. When the debugger hits the breakpoint on the goback statement for the second time, press F5 again.
   You should see the following in a generated console window as a result of calling the stored procedure:

   User = THOMPSON                 MICHAEL

5. Press F5 to stop debugging.