SQL CLR Integration Process

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

1. Launch Visual COBOL.
   Important: On UAC-enabled operating systems, you must start Visual COBOL as an administrator.
2. Create a Visual Studio project using the SQL Server Database Project template.
3. Create or specify a database reference to your SQL CLR-enabled SQL Server data source.
4. If you intend to code your SQL CLR objects using EXEC SQL statements, set your project properties to use the OpenESQL ESQL Preprocessor, and add the DBMAN compiler directive set to the value of ADO.
5. Add any of the following SQL CLR objects in the form of a COBOL program by selecting it either from the Projects menu or the .Publish project's context menu:
   • User-Defined Function
   • Stored Procedure
   • Stored Procedure Definition (.spd) File
   • Aggregate
   • Trigger
   • User-Defined Type
6. Edit the object code to reflect the functionality you require.
   Note: When coding for the OpenESQL preprocessor,

   • Consider using the OpenESQL Assistant to generate EXEC SQL statements.
   • Because SQL CLR manages database updates via the calling program, do not code EXEC SQL COMMIT or EXEC SQL ROLLBACK statements into a stored procedure. Instead, do one of the following:
     • Code EXEC SQL COMMIT and EXEC SQL ROLLBACK statements into the calling program as necessary.
     • Compile the calling program with the SQL(AUTOCOMMIT) directive, ensuring that database updates are automatic after the execution of each EXEC SQL statement.
     • Run the calling program under a managed transaction manager that controls and issues commits and rollbacks.
7. If you have added a stored procedure definition file, optionally generate a COBOL wrapper program for it.
8. Build the project to generate a DLL module that can be accessed from a COBOL application.
9. Deploy the project to copy your code to your SQL Server instance and create 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.