error al llamar a una conexión odbc dentro de un triggers CLR.

Question

Hi, I have a problem.

I am performing a CLR triggers in which it is triggered when an insert operation is performed on a certain table. The operation that I must perform from the triggers would be to execute an insert on a table that is on another server. The connection they gave me to get to this table from the other server is an ODBC connection.

What happens is that when I go to open the connection within the Trigger CLR it gives me an error.

The connection is correctly configured, because I have made a windowsform application and it connects correctly.

The trigger is declared with EXTERNAL ACCESS permissions.

public static void SqlTrigger1 ()
{

        SqlContext.Pipe.Send("comienzo");
    OdbcConnection conO = new OdbcConnection("DSN=xxx;UID=xxx;PWD=xxx");
    SqlContext.Pipe.Send("DECLARO CONEXION");

    conO.Open();   
    SqlContext.Pipe.Send("ABRO CONEXION ");


    OdbcCommand cmd = new OdbcCommand();


    cmd.CommandText = "INSERT INTO .........";
    SqlContext.Pipe.Send("DEFINO TEXTO COMANDO ");
    cmd.Connection = conO;
    SqlContext.Pipe.Send("INDICO CONEXION AL COMANDO");

    cmd.ExecuteNonQuery();
    SqlContext.Pipe.Send("EJECUTO COMANDO ");

    conO.Close();
    SqlContext.Pipe.Send("PROCESO FINALIZADO ");


}

I do not understand why the connection is not working from the trigger and if it works from the windowsform application if they are using the same code.

Error that it returns, when executing withO.Open ();

Msg 6522, Level 16, State 1, Procedure SqlTrigger1, Line 1 [Batch Start Line 11]
Error de .NET Framework durante la ejecución de la rutina o agregado definido por el usuario 'SqlTrigger1':
System.Security.SecurityException: Error de solicitud de permiso de tipo 'System.Data.Odbc.OdbcPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.
System.Security.SecurityException:
en System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
en System.Security.PermissionSet.Demand()
en System.Data.Common.DbConnectionOptions.DemandPermission()
en System.Data.Odbc.OdbcConnectionFactory.PermissionDemand(DbConnection outerConnection)
en System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
en System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
en System.Data.Odbc.OdbcConnection.Open()
en Triggers.SqlTrigger1()

Answer 1

This question is clearly not about the Small Basic language.
Please remove the Small Basic tag.

Google translate:
Esta pregunta claramente no se trata del lenguaje Small Basic.
Quite la etiqueta Small Basic.

Answer 2

I have created the triggers as unsafe and mounted it and it is giving me another error.

Msg 6522, Level 16, State 1, Procedure SqlTrigger1, Line 1 [Batch Start Line 11]
Error de .NET Framework durante la ejecución de la rutina o agregado definido por el usuario 'SqlTrigger1':
System.Data.Odbc.OdbcException: ERROR [HY000] [IBM][System i Access ODBC Driver]Enlist with DTC phase failed. 2
System.Data.Odbc.OdbcException:
en System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
en System.Data.Odbc.OdbcConnection.Open_EnlistTransaction(Transaction transaction)
en System.Data.Odbc.OdbcConnection.EnlistTransaction(Transaction transaction)
en System.Data.Odbc.OdbcConnection.Open()
en Triggers.SqlTrigger1()

Answer 3

Yes, that is a very different error. This means that the CLR part of it are working.

What happens now is that the ODBC driver wants to enlist the connection in the transaction that is defined by the statement that fired the trigger.

That may be difficult to solve, and this is probably not the forum to ask about it, as this involves Windows (of which DTC is a part) and your ODBC driver, but not so much SQL Server.

It is not unlikely that your ODBC driver offers an option to decline being enlisted into the transaction. You need to check the documentation. However, this option should be a last resort, because I suspect that you want that distributed transaction. That is, say that your transaction fails on the SQL Server side before it has committed, but after the CLR trigger has completed. This means that the update has been performed on the IBM side, which now may be inconsistent with SQL Server.

Answer 4

I have solved this problem by enabling the msdtc.

ystem.Data.Odbc.OdbcException: ERROR [HY000] [IBM][System i Access ODBC Driver]Enlist with DTC phase failed. 2

The problem now is a different one, I can already connect, and the problem appears when the insert is executed. Now it returns the following error to me:

Error de .NET Framework durante la ejecución de la rutina o agregado definido por el usuario 'SqlTrigger1':
System.Data.Odbc.OdbcException: ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7008 - PRUEBAN in DATA not valid for operation.
System.Data.Odbc.OdbcException:
en System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
en System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
en System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
en System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
en Triggers.SqlTrigger1()