June 29, 2009 at 8:44 am
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?
June 30, 2009 at 10:33 am
That's a very good question... one I'd be asking the architect of that design.
The probability of survival is inversely proportional to the angle of arrival.
June 30, 2009 at 10:52 am
- 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?
July 2, 2009 at 7:24 am
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-2= dbo.myfunction() --outside the scope of the trans
hope this helps.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply