SQL CLR Integration Process

The process of effectively using the Enterprise Developer SQL CLR integration feature to create or add user-defined functions and types, aggregates, and triggers in managed code, and access stored procedures, is as follows:

1. If using one or more stored procedures, determine the type of data type parameters you want to use (COBOL or .NET) and make any necessary adjustments. See Data Type Parameters in COBOL and SQL CLR Stored Procedures for details.
2. Launch Enterprise Developer.
   Important: On UAC-enabled operating systems, you must start Enterprise Developer as an administrator.
3. Create a Visual Studio solution using the SQL Server Database Project template, which includes two projects in the solution – a COBOL managed Class Library project for your COBOL stored procedure code, and a .Publish project.
   Note:
   For more information, see SQL Server Database Project Template.
4. Optionally add one or more additional COBOL managed or native projects to the solution. See Working with Multiple COBOL Managed Projects for details.
5. Set the properties for each COBOL managed project in the solution:
   • If you are using SQL Server 2008 R2, specify .NET Framework 3.5 or earlier; for later versions of SQL Server, specify .NET ???.
   • If you intend to code new SQL CLR objects or add existing SQL CLR objects that use EXEC SQL statements, set the project properties to use the OpenESQL ESQL Preprocessor, and add the DBMAN compiler directive set to the value of ADO.
6. Do either or both of the following:
   • For each COBOL managed project in the solution, add any of the following as a new SQL CLR object in the form of a COBOL program by right-clicking the project; then selecting Add > New Item from the context menu:
     • User-Defined Function
     • Stored Procedure
     • Stored Procedure Definition (.spd) File
     • Aggregate
     • Trigger
     • User-Defined Type

     Enterprise Developer opens the new object in the editor where you can manually complete the coding to reflect the functionality you require. To ensure correct coding of data type parameters in SPD files, see Data Type Parameters in COBOL and SQL CLR Stored Procedures.

   • Add an existing SQL CLR object to any COBOL managed project using the Add > Existing Item option on the context menu.
     Note: If you add an SPD file in this manner, be sure to generate the COBOL wrapper program before continuing. See To generate a COBOL wrapper for a SQL CLR stored procedure for instructions.
7. Build the project to generate a DLL module that can be accessed from a COBOL application.
8. Under the .Publish project, expand References and verify that all COBOL managed projects in the solution that contain stored procedures are referenced. If not, create a reference for each. See To add a project reference to a .Publish project for instructions.
9. Right-click the .Publish project; then select Publish from the context menu.

   This deploys your stored procedure code to your SQL Server instance and creates an object in the SQL Server database to use the generated DLL module.

10. Execute the SQL CLR object, providing input values as necessary, so you can see the output it generates.

EXEC SQL :spReturnCode = call "objectName" args

We recommend that you complete the OpenESQL tutorials that take you through the process of creating and calling a SQL CLR stored procedure. These are available from the Tutorials: SQL CLR Integration topic.