Tutorial: SQL CLR Stored Procedures - Call a Stored Procedure Defined in an SPD File

Requirements

Complete the tutorials specified in the Prerequisites topic.

Open the SQLCLRTutorialSPCall Solution

1. Start Enterprise Developer as an administrator. To do this:
   1. From your desktop, click Start > All Programs > Micro Focus Enterprise Developer.
   2. Right-click Enterprise Developer for Visual Studio 2012; then click Run as administrator.
      Note: The Start menu is not available on Windows 8, Windows 10, and Windows Server 2012. You use the Start screen to invoke programs. To start Enterprise Developer as an administrator, go to the Start page; then right-click the Enterprise Developer tile and select Run as administrator at the bottom of the page.

1. In Enterprise Developer, click File > Open > Project/Solution.
2. Browse to the %PUBLIC%\Documents\Micro Focus\Enterprise Developer\Samples\Mainframe\SQL\hcoss\JCLCallingSP\SPDDemo directory.
3. Double-click SQLCLRTutorialSPCall.sln.

Set Project Properties

1. In the Solution Explorer, right-click the SPCall project; then select Properties from the context menu.
2. On the Debug tab (located on the left sidebar), using the drop-down list, set Active Settings to JCL.
3. Click (Save).

Build the Solution

• In the Solution Explorer, right-click the solution; then select Build Solution from the context menu.

Associate the JCLSP Region with the SPCall Project

1. In Server Explorer, expand Micro Focus Servers > localhost.
2. Right-click JCLSP; then select Associate With Project > SPCall from the context menu.

Start the JCLSP Region

1. On the Server Explorer list, right-click JCLSP; then select Start from the context menu.
2. If you are using SQL Server authentication, check Server is secured; then provide your Username and Password.

   If you are using Windows authentication, skip this step.

3. Click OK.

Debug the Program and Stored Procedure

In this section, you use Enterprise Developer to debug the native COBOL program and the SQL CLR stored procedure.

1. In the Solution Explorer, double-click SPCall.cbl to open in the COBOL editor.
2. In the COBOL Editor, scroll to:

   exec sql
        call "SP1"
   end-exec

3. In the leftmost column on the exec sql line, click to set a breakpoint.
4. From the Solution Explorer, open SP1.cbl.
5. Scroll to:

   call "A" using by reference lscountval.

   and set a breakpoint.
6. Click DEBUG > Start Debugging. This builds the project and puts the debugger in a wait state.
7. In Solution Explorer, right-click the SPCall.jcl file; then select Submit JCL from the context menu.

   The JCL script calls the SPCall program and stops on the breakpoint you set in the SPCall.cbl file.

8. Click DEBUG > Attach to Process.
9. On the Attach to Process dialog box, click Select.
10. On the Select Code Type dialog box, click Debug these code types; then check the following boxes:
    • Managed (v4.5, v4.0)
    • T-SQL
11. Click OK.
12. On the Attach to Process dialog box, check Show processes from all users.
13. On the Available Processes list, select the sqlservr.exe entry that has the NT Service\MSSQLServer User Name.
14. Click Attach.
    Note: If a prompt appears, click Attach to clear it.
15. Click DEBUG > Step Into.

    The debugger steps into the managed SQL CLR stored procedure code. This demonstrates debugging in a mixed native/managed environment.

16. To continue debugging, press F10; when the debugger reaches another breakpoint, press F10 again.
17. Press F5 to run the program to its completion.

View JCL Execution Results

1. In the Enterprise Developer Output window, select Enterprise Server from the Show output from drop-down list.
2. CTRL-click the job output link.

   This opens the JCLSP tab, showing the Enterprise Server Job Output window.

3. Click SYSOUT.

   This shows the output from the SQL CLR Stored Procedure.

4. Close the JCLSP tab.

Stop the JCLSP Enterprise Server Region

• On the Server Explorer, right-click JCLSP; then select Stop from the context menu.