locked
Context connection works but a full connection string fails - why? RRS feed

  • Question

  • Greetings:

    I am attempting to create a CLR procedure that will be called from T-SQL.  The CLR procedure (my first ever) worked fine with a connection string of context connection=true. However, I changed the connection string to a "traditional" connection string of "Data Source=ESIhm1xx1;Initial Catalog=EMS2;Integrated Security=True".  Now when I execute the procedure I get the following error:

    Msg 6522, Level 16, State 1, Procedure usp_Log, Line 0
    A .NET Framework error occurred during execution of user-defined routine or aggregate "usp_Log":
    System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
    System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
    at System.Security.PermissionSet.Demand()
    at System.Data.Common.DbConnectionOptions.DemandPermission()
    at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
    at System.Data.SqlClient.SqlConnection.Open()
    at StoredProcedures.usp_Log()

    I believe I need to set a permission soemwhere but I don't know what to do.  The procedure is shown in object explorer with a small padlock on its icon.  I'm trying to use the CLR procedure with a traditional connection string because I need to do commits and rollbacks in my T-SQL code and I wanted to use the CLR procedure to do logging that would always be committed and never rolled back if the T-SQL issues a rollback.  Does this sound possible.  Again, I'm in alien territory with the CLR stuff although I work with T-SQL daily so I'm theorizing about being able to insert rows that won't roll back if the T-SQL issues a rollback.
    Thanks for any help.

    BCB

    Tuesday, June 9, 2009 8:03 PM

Answers

  • If the CLR procedure uses transactions they are composed with the outer (T-SQL) transaction. You could use a SqlConnection with an ordinary connection string "Server=.;integrated security=sspi;database=databaseB", but then you would have two connections (one internal connection, one external connection, wasteful in terms of resources, and they could deadlock each other). These would have autonomous transaction scopes and lock spaces.
     
    If I'm reading your use case correctly, what you're asking for is autonomous transactions, and SQL Server does not support these. You can accomplish most (but not all) of the same type of functionality by using named savepoints. Interleaved inserts would be one place where named savepoints wouldn't do it, but could you break up your transaction into multiple separate transactions, each with a write and an audit?
     
    Hope this helps,
    Bob Beauchemin
    SQLskills
    "BlackCatBone" wrote in message news:65e78105-1d56-438 f-be73-55afe13dbc19...
    Thanks, Jens.

    I know from my simple testing that a T-SQL procedure can call a CLR procedure.  Am I correct in my theory that the CLR procedure would be able to do its own transaction management independent of the "outer" T-SQL procedure?  I am pursuing the CLR approach because I now have a T-SQL procedure that does a lot of inserts to database A and a lot of inserts to database B.  The inserts are mixed together (interleaved).  I occassionally need to do a rollback in database A but I never want to roll back anything written to database B.  (Database B is an audit database that must list all inserts performed or attempted to database A.)  This presents a big problem with a straight T-SQL approach because a rollback to database A would also roll back changes to database B.

    I'd appreciate it if you or someone else could comment on the ability of a CLR procedure to provide transaction management independent of the T-SQL procedure that called it.

    Gracias!

    BCB
    • Marked as answer by BlackCatBone Thursday, June 18, 2009 6:37 PM
    Monday, June 15, 2009 8:54 PM

All replies

  • THis requires the assembly to have EXTERNAL_ACCESS if you marked it as SAFE it only works for context connections.

    -Jens
    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Wednesday, June 10, 2009 7:50 AM
  • Thanks, Jens.

    I know from my simple testing that a T-SQL procedure can call a CLR procedure.  Am I correct in my theory that the CLR procedure would be able to do its own transaction management independent of the "outer" T-SQL procedure?  I am pursuing the CLR approach because I now have a T-SQL procedure that does a lot of inserts to database A and a lot of inserts to database B.  The inserts are mixed together (interleaved).  I occassionally need to do a rollback in database A but I never want to roll back anything written to database B.  (Database B is an audit database that must list all inserts performed or attempted to database A.)  This presents a big problem with a straight T-SQL approach because a rollback to database A would also roll back changes to database B.

    I'd appreciate it if you or someone else could comment on the ability of a CLR procedure to provide transaction management independent of the T-SQL procedure that called it.

    Gracias!

    BCB
    Wednesday, June 10, 2009 12:42 PM
  • If the CLR procedure uses transactions they are composed with the outer (T-SQL) transaction. You could use a SqlConnection with an ordinary connection string "Server=.;integrated security=sspi;database=databaseB", but then you would have two connections (one internal connection, one external connection, wasteful in terms of resources, and they could deadlock each other). These would have autonomous transaction scopes and lock spaces.
     
    If I'm reading your use case correctly, what you're asking for is autonomous transactions, and SQL Server does not support these. You can accomplish most (but not all) of the same type of functionality by using named savepoints. Interleaved inserts would be one place where named savepoints wouldn't do it, but could you break up your transaction into multiple separate transactions, each with a write and an audit?
     
    Hope this helps,
    Bob Beauchemin
    SQLskills
    "BlackCatBone" wrote in message news:65e78105-1d56-438 f-be73-55afe13dbc19...
    Thanks, Jens.

    I know from my simple testing that a T-SQL procedure can call a CLR procedure.  Am I correct in my theory that the CLR procedure would be able to do its own transaction management independent of the "outer" T-SQL procedure?  I am pursuing the CLR approach because I now have a T-SQL procedure that does a lot of inserts to database A and a lot of inserts to database B.  The inserts are mixed together (interleaved).  I occassionally need to do a rollback in database A but I never want to roll back anything written to database B.  (Database B is an audit database that must list all inserts performed or attempted to database A.)  This presents a big problem with a straight T-SQL approach because a rollback to database A would also roll back changes to database B.

    I'd appreciate it if you or someone else could comment on the ability of a CLR procedure to provide transaction management independent of the T-SQL procedure that called it.

    Gracias!

    BCB
    • Marked as answer by BlackCatBone Thursday, June 18, 2009 6:37 PM
    Monday, June 15, 2009 8:54 PM
  • Thanks Bob.
    Thursday, June 18, 2009 6:37 PM