March 24, 2010 at 1:38 am
hi all
i recently created my (first) clr stored-proc. it involves calling an external web service, and therefore implements xml serialization stuff, marked as unsafe etc.. and after some struggle it worked fine
i call this sp from certain insert triggers in key tables on our PRODUCTION db
all was good for quite a while
a few days ago, it stopped working and all calls to it failed (either directly or by means of the above triggers) returning errors about trustworthiness, safe/external/unsafe settings (in the heat of the struggle i didnt save the error message syntax)
i ended up deleting and re-creating assemblies, re-setting the db as trusworthy=on and i now works again.
my question: under which circumstances does a database changes it's trustworthy property value? and what about assemblies and their external/unsafe settings?
restarting sql server?, changing recovery model from full to simple (did that a few days ago) ?
thankx in advance
Erez
March 24, 2010 at 3:00 am
hi guys
just found out one of the admins performed a copy-database (which involves a detach/re-attach of the source), and searching bol/msdn that causes the trustworthy property to be reset to false.
so i guess thats what happened.
i'll be happy to know if there are any other events that might cause this...
Erez
March 26, 2010 at 2:29 am
erezmor (3/24/2010)
...calling an external web service...marked as unsafe...call this sp from certain insert triggers in key tables on our PRODUCTION db
I don't want to sound harsh, but this is a terrible design.
You should never call anything external from trigger code, much less something that misuses CLR to call an external web service.
Consider queuing requests from the trigger, using a queue table or Service Broker, and processing the requests asynchronously.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply