This tutorial walks you through the process of preparing the debug environment and then debugging a SQL CLR stored procedure.
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.
Note: When connecting to your SQL Server, if Microsoft SQL Server 2012 prompts you with an Attach Security Warning, please click
Attach to clear the prompt.
- Add the SQLCLRTutorialCall Project to the SQLCLRTutorial Solution
-
- From the Visual Studio IDE, open the
SQLCLRTutorial solution you created and updated in previous tutorials.
- From the Solution Explorer, right-click the
SQLCLRTutorial solution and select
Add > Existing Project.
- Browse to the folder containing the
SQLCLRTutorialCall project.
- Select the
SQLCLRTutorialCall project; then click
Open.
- Set Properties for the SQLCLRTutorialCall Project
-
- From the Solution Explorer, open the Properties for the
SQLCLRTurotialCall project.
- 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 you've created using the ADO.NET Connection Editor to SQL Server 2012 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.
- On the
COBOL tab, select
x86 from the
Platform target drop-down list.
- Save the project and close the Properties dialog box.
Note: Now that you are prepared to debug in a development environment, you are ready to complete either or both of the
Debug Locally in a Development Environment and
Debug Remotely in a Development Environment sections in this tutorial.
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 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.
- If you have closed the
SQLCLRTutorial solution, open it again.
- From the Solution Explorer, double-click the
Program1.cbl program to open it in the editor.
- Insert a breakpoint on the following lines of code:
:spReturnCode = call "SQLCLRturorial" (:empid INOUT, :lastname OUT, :firstname OUT)
and
goback.
- From the Solution Explorer, double-click the
SQLCLRTutorial.cbl program to open it in the editor.
- 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
-
- Press
F5 to start the debugger.
- When the debugger hits the first breakpoint in the
Program1.cbl program, press
F5 again to execute the
SQLCLRTutorial.cbl stored procedure.
- While executing the
SQLCLRTutorial.cbl stored procedure, optionally step through line by line or examine variables as you would during any debugging process.
When the debugger hits the breakpoint on the
goback statement, the following appears in the Output window as a result of calling the stored procedure:
User = MICHAEL THOMPSON
- 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.
Note: When connecting to your SQL Server, if Microsoft SQL Server 2012 prompts you with an Attach Security Warning, please click
Attach to clear the prompt.
- 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.
- 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.
- Select the
SQLCLRTutorial connection.
- Change the value of the
Data Source key to point to a SQL Server instance on a remote machine.
- 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.
- Save the connection and close the ADO.NET Connection Editor.
- Set Properties for the SQLCLRTutorial.Publish Project
-
- From the Solution Explorer in Visual Studio, open the properties for the
SQLCLRTutorial.Publish project.
- On the
Debug tab, click
Edit in the
Target Connection String group.
- In the
Server name field, type the name of your remote SQL Server instance.
- If necessary, make authentication changes to the information in the
Log on to the server group.
- 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.
- On the development machine, build the application.
- 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:
- If you have closed the
SQLCLRTutorial solution, open it again.
- From the Solution Explorer, double-click the
Program1.cbl program to open it in the editor.
- Insert a breakpoint on the following lines of code:
:spReturnCode = call "SQLCLRturorial" (:empid INOUT, :lastname OUT, :firstname OUT)
and
goback.
- From the Solution Explorer, double-click the
SQLCLRTutorial.cbl program to open it in the editor.
- 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
-
- Press
F5 to start the debugger.
- When the debugger hits the first breakpoint in the
Program1.cbl program, press
F5 again to execute the
SQLCLRTutorial.cbl stored procedure.
- While executing the
SQLCLRTutorial.cbl stored procedure, optionally step through line by line or examine variables as you would during any debugging process.
When the debugger hits the breakpoint on the
goback statement, the following appears in the Output window as a result of calling the stored procedure:
User = MICHAEL THOMPSON
- Press
F5 to stop debugging.