|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, July 02, 2009 7:27 AM
Points: 4,
Visits: 12
|
|
Hello,
I have found very strange thing. Its looks like one of my CLR-based udf's trying to write something into transacion log. For example:
1: begin tran
2: insert into table select 123
3: select @var1 = dbo.udf_function(@var2)
4: commit
If insert statement finishing with an error transacion going to be Uncommittable. Its OK.
But in Line 3: I will get additional error:
Msg 3930, Level 16, State 1, Line 3
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
I have couple of CLR-based udf's but only one who, works with XML produce this error.
How its possible? Why simple function which just works with an XML variable triyng to write something into transacion log?
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 2:53 PM
Points: 168,
Visits: 513
|
|
| That's a very good question... one I'd be asking the architect of that design.
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 6:51 PM
Points: 6,188,
Visits: 8,923
|
|
- If the insert has an error, the function didn't fire.
- SQL Server considers Functions to be "no modify", so by their design, they are not supposed to make any persistent changes (like, say, writing to a log file). Anything that manages to get past that requirement, for example by being CLR instead of t-sql (which would prevent you from creating the function) will likely be "cranky".
Finally - what's the initial error you're getting?
Sounds to be you should be looking into SQL 2005 and beyond error handling.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Monday, February 08, 2010 1:05 PM
Points: 48,
Visits: 90
|
|
hi,
You should try to keep the scope of the transaction small, so you can commit after the insert statement (after error check). Do the read outside the scope of the transaction and validate the result of the variable
begin trans
insert/update statement
if (@@rowcount > 0)
--ok process
commit/rollback --depending on your validation
select @v= dbo.myfunction() --outside the scope of the trans
hope this helps.
|
|
|
|
