Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Calling CLR Proc from within T-SQL Transaction

    Hi

    SQL Server 2008
    Visual Studio 2008

    First time calling a CLR sproc from a T-SQL sproc. Briefly, my T-SQL sproc begins a transaction, makes some changes to a table and calls the CLR sproc. I get the below .NET error as the CLR sproc attempts to open a connection:
    Transaction context in use by another session
    Based on some reading
    (AsiaTech: Learning by Practice : System.Transaction may fail in multiple-thread environment
    How to create a second independent transaction inside a CLR stored procedure?)
    As I read it, can't have two threads sharing the same transaction context.
    I have added this to my CLR connection string:
    Code:
    ...Enlist=false;
    This just opens up a new transaction context for the CLR. Or I can move the call to the CLR sproc outside of the T-SQL transaction. The code no longer fails, but my tasks are no longer atomic.

    The solutions are a hack - anyone know another method, ideally keeping everything in a single transaction?

    A better option to the two above I think is to shift the T-SQL data manipulation in to the CLR sproc - I just liked having the CLR sproc scoped only to the stuff it could do better than T-SQL.

    Many thanks geekies
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If you are opening a connection from your CLR, then you're trying to manipulate data. T-SQL is doing it better than CLR, unless it involves extremely complex arithmetic or string algorithms.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rdjabarov
    unless it involves ..... string algorithms.
    Bingo!

    Large batch iterative string manipulation - not one for T-SQL, even in a CTE.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Solved:
    SQL Server Forums - Calling CLR Proc from within T-SQL Transaction

    No thanks to you lot of useless numpties.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    So you post the solution on some other site, only that solution is a link to Microsoft's site? Why not route us through Experts Exchange, Tek-tips, and SQL Server Central, as well? Or were their numpties even less helpful? ;-)

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I was eating lunch. Aint it time for you to be off to the pub anywho?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MCrowley
    So you post the solution on some other site, only that solution is a link to Microsoft's site? Why not route us through Experts Exchange, Tek-tips, and SQL Server Central, as well? Or were their numpties even less helpful? ;-)
    Typical - post a question, get stuff all help in 24 hours. Point out that you are all numpties and what happens? Two posts in 10 minutes and a complaint that the solution is too "hard"!

    Quote Originally Posted by Thrasymachus
    Aint it time for you to be off to the pub anywho?
    By God you are right!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmm...It never made sense to me to even try to open a non-"context connection"...I guess that's why I kinda got puzzled with your question, and thought that I might have missed something somewhere in your original post...Welcome to "context connection" world (why would you want to do it in any other way, if you're calling it from your stored procedure already???)!!!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ignorance - I didn't know there was a "context connection" option. I knew that was broadly what I wanted just didn't know how to express it. As it happens, it still isn't totally perfect since you can only have a single context connection and that connection does not support MARS, but I wanted two simultaneous datareaders.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •