Hyland Services in SQL Server CLR

[Jason_Brownlow]

Im using Hyland Services in C# .Net and registering the assemblies in SQL server using CLR. The basic purpose is to run Hyland services code in SQL server via a stored procedure to import a document and start the associated workflow. This requires writing .NET C# code and building it then importing the assemblies into SQL server and registering them for use in a stored procedure or function.  The Hyland dll's are also registered as assemblies in SQL server including Hyland.Types and Hyland.Services and several others that auto register with them.  Hyland seems to be giving me a unique issue.  Im hoping someone else has done this before. The IRequest and Hyland.Public.Factory are not found at run time.  This seems to be a basic problem with the dll's being found or recognized properly in SQL Server. All of this code functions fine in a regular windows form C# .NET application.  I realize this might be a bit obscure but thought Id appeal to the group anyway.  Has anyone successfully used Hyland.Services in CLR?  The error message thrown when I execute the SQL proc is:

Factory for type Hyland.Public.IRequest not Found.  Check dlls Exception caught in Connect() method.
OBArchive Session ID:
Factory for type Hyland.Public.IAuthenticationToken not Found.  Check dlls
Exception caught in OBarchive() method.

The connect .NET code is here (the rest has been left out):

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using Hyland.Public.Services;
using Hyland.Public;
using Hyland.Types;

namespace ManagedCodeAndSQLServer
{
    public class BaseFunctionClass
    {
        
        public BaseFunctionClass()
        {

        }

        [SqlProcedure]
        public static void GetMessage(SqlString strName, out SqlString strMessge)
        {
            strMessge = "Welcome," + strName + ", " + "your code is getting executed under CLR !";
        }

        [SqlProcedure]
        public static void OB_Wire_Import(SqlString wireReqNum, out SqlString retval)

        {

            string g_URL = "http://onbase/AppServer/Service.asmx";
            string g_SessionID = "";
            string imagefile = @"C:\Documents and Settings\JDBrownl\My Documents\My Pictures\Image_2.jpg";
            //connect
            try
            {
                // Create a new request
                Hyland.Public.IRequest request = Hyland.Public.Factory.CreateObject(typeof(Hyland.Public.IRequest)) as Hyland.Public.IRequest;

                // Specify the "OnBase" service provider to handle the request
                request.ServiceProvider = "OnBase";

                // Set the request type to "Connect"
                request.Name = "Connect";

                // Load the OnBase login credentials to the request
                request.AddParameter("username", "MYUSER");
                request.AddParameter("password", "mypassword");
                request.AddParameter("datasource", "myserver");

                // Create a request list
                Hyland.Public.IRequestList requestList = Hyland.Public.Factory.CreateObject(typeof(Hyland.Public.IRequestList)) as Hyland.Public.IRequestList;

                // Add the loaded request to the request list
                requestList.Add(request);

                // Create the service client and add the URL for the location of the Service.asmx file
                Hyland.Public.ISoapServiceClient onbase = Hyland.Public.Factory.CreateObject(typeof(Hyland.Public.ISoapServiceClient)) as Hyland.Public.ISoapServiceClient;
                onbase.URL = g_URL;

                // Execute the request list and obtain the responses
                Hyland.Public.IResponseList responseList = onbase.Execute(requestList);

                // Obtain the 1st response
                Hyland.Public.IResponse response = responseList[0];

                // Check for errors on the server side by using the Validate() call on the response
                // If an error is found, Validate() will throw a ServiceException
                response.Validate();

                // Generate a parameter list from the response
                Hyland.Public.IParameterList parameterList = response.Parameters;

                //Access the sessionID that was generated. This is a parameter in the response list.
                g_SessionID = parameterList.Values["sessionID"].ToString();

                SqlContext.Pipe.Send("Connect Session ID: " + g_SessionID);
            }
            catch (Hyland.Public.ServiceException ex)
            {
                SqlContext.Pipe.Send(ex.Error.Message + " ServiceException caught in Connect() method.");
            }
            catch (Exception exception)
            {
                SqlContext.Pipe.Send(exception.Message + " Exception caught in Connect() method.");
            }
            //connect complete

       }

   }

}

The SQL code for registering the DLLs and stored procs is here.  Some of it is commented out and may only be necessary to run once.  All of it can be run at a SQL Server query command line .....

The user must begranted external access and the database set as trustworthy initially.

--USE [master]
 
--GRANT EXTERNAL ACCESS ASSEMBLY TO myuser 
--GO 
 
--ALTER DATABASE mydatabase SET TRUSTWORTHY ON;
--GO

USE mydatabase
GO

--Assemblies must be dropped before being recreated.
--DROP ASSEMBLIES -- run these drops manually a few times until it is run with no erors
--drop assemblies prior to recreating them
 
IF EXISTS (SELECT [name] FROM sys.procedures WHERE [name] = N'usp_OB_Wire_Import')
 DROP PROCEDURE [usp_OB_Wire_Import]
/*
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'Hyland.Services')
 DROP ASSEMBLY [Hyland.Services]
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'Hyland.Core')
 DROP ASSEMBLY [Hyland.Core]
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'Hyland.Data')
 DROP ASSEMBLY [Hyland.Data]
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'Hyland.Diagnostics')
 DROP ASSEMBLY [Hyland.Diagnostics]
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'Hyland.Types')
 DROP ASSEMBLY [Hyland.Types]
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'Hyland.Types.Emit')
 DROP ASSEMBLY [Hyland.Types.Emit]
--IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'system.web')
-- DROP ASSEMBLY [system.web]
--IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'stdole')
-- DROP ASSEMBLY [stdole]
--IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'system.management')
-- DROP ASSEMBLY [system.management]
*/
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'ManagedCodeAndSQLServer.XmlSerializers')
 DROP ASSEMBLY [ManagedCodeAndSQLServer.XmlSerializers]
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'ManagedCodeAndSQLServer')
 DROP ASSEMBLY [ManagedCodeAndSQLServer]

-- CREATE ASSEMBLIES
--create the stdole assembly
--CREATE ASSEMBLY [stdole]
--from 'C:\Program Files\Microsoft.NET\Primary Interop Assemblies\stdole.dll'
--    WITH PERMISSION_SET = UNSAFE
--create the system.web assembly
--CREATE ASSEMBLY [system.web]
--from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.web.dll'
--    WITH PERMISSION_SET = UNSAFE
--create the system.management assembly
--CREATE ASSEMBLY [system.management]
--from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.management.dll'
--    WITH PERMISSION_SET = UNSAFE
--Create assembly Hyland.Diagnostics
--CREATE ASSEMBLY [Hyland.Diagnostics]
--from 'C:\Program Files\Hyland\Core\Hyland.Diagnostics.dll'
--   WITH PERMISSION_SET = UNSAFE
--Create assembly Hyland.Data
--CREATE ASSEMBLY [Hyland.Data]
--from 'C:\Program Files\Hyland\Core\Hyland.Data.dll'
--   WITH PERMISSION_SET = UNSAFE
--Create assembly Hyland.Core
--CREATE ASSEMBLY [Hyland.Core]
--from 'C:\Program Files\Hyland\Core\Hyland.Core.dll'
--    WITH PERMISSION_SET = UNSAFE
--create the Hyland.Types assembly
--CREATE ASSEMBLY [Hyland.Types]
--from 'C:\Documents and Settings\JDBrownl\My Documents\Visual Studio 2008\Projects\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\Hyland.Types.dll'
--    WITH PERMISSION_SET = UNSAFE
--create the Hyland.Services assembly
--CREATE ASSEMBLY [Hyland.Services]
--from 'C:\Documents and Settings\JDBrownl\My Documents\Visual Studio 2008\Projects\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\Hyland.Services.dll'
--    WITH PERMISSION_SET = UNSAFE
--create the xml assembly
CREATE ASSEMBLY [ManagedCodeAndSQLServer.XmlSerializers]
from 'C:\Documents and Settings\JDBrownl\My Documents\Visual Studio 2008\Projects\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\bin\Debug\ManagedCodeAndSQLServer.XmlSerializers.dll'
WITH permission_set = UNSAFE
GO
--create the ManagedCodeAndSQLServer assembly
CREATE ASSEMBLY ManagedCodeAndSQLServer
AUTHORIZATION dbo
FROM 'C:\Documents and Settings\JDBrownl\My Documents\Visual Studio 2008\Projects\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\bin\Debug\ManagedCodeAndSQLServer.dll'
WITH PERMISSION_SET = UNSAFE
GO

--Sometimes visibility must be set to ON

ALTER ASSEMBLY ManagedCodeAndSQLServer WITH VISIBILITY = ON
GO
ALTER ASSEMBLY [ManagedCodeAndSQLServer.XmlSerializers] WITH VISIBILITY = ON
GO
ALTER ASSEMBLY [Hyland.Services]  WITH VISIBILITY = ON
GO
ALTER ASSEMBLY [Hyland.Types] WITH VISIBILITY = ON
GO
ALTER ASSEMBLY [Hyland.Core] WITH VISIBILITY = ON
GO

--Create the proc for importing a wire
CREATE PROCEDURE usp_OB_Wire_Import
@wireID nvarchar(200),
@retval nvarchar(MAX)OUTPUT
AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.BaseFunctionClass].OB_Wire_Import
GO

To execute the SQL proc:

DECLARE @msg nvarchar(max)
EXEC usp_OB_Wire_Import '5318672',@msg OUTPUT

[Daniel_Quill]

JBrownLow,

We apologize for the delay in responding to your inquiry. Hyland is currently in the process of ensuring all questions are responded to and/or answered. If your inquiry is still relevant or requires further assistance, please do not hesitate to repost your inquiry or contact your first line support team.

Due to nature and complexity of the issue it is recommended that you contact your technical support team for further assistance.  This type of implementation has not tested and may require additional research.

Regards,