>>>>>>>>>>HI
>>>>>>>>>>
>>>>>>>>>>I have a c# CLR that I'm using to decrypt bank sort codes and account number.
>>>>>>>>>>
>>>>>>>>>>I've has an error that caused SQL to restart
>>>>>>>>>>
>>>>>>>>>>A fatal error occurred in the .NET Framework common language runtime. SQL Server is shutting down. If the error recurs after the server is restarted, contact Customer Support
>>>>>>>>>>
>>>>>>>>>>This seems to occur when I use the CLR in a table update rather than just decrypting one row.
>>>>>>>>>>
>>>>>>>>>>so
>>>>>>>>>>
>>>>>>>>>> update integra_live.dbo.xmasall set sortcode_decrypt = dbo.fn_RMB_RemoveCharacters(dbo.fn_RMB_bank_decrypt([Sort Code],@decrypt_key), '^0123456789') where [DD reference] = 'RWM000196'
>>>>>>>>>>
>>>>>>>>>>works
>>>>>>>>>>
>>>>>>>>>>and
>>>>>>>>>>
>>>>>>>>>> update integra_live.dbo.xmasall set sortcode_decrypt = dbo.fn_RMB_RemoveCharacters(dbo.fn_RMB_bank_decrypt([Sort Code],@decrypt_key), '^0123456789')
>>>>>>>>>>
>>>>>>>>>>fails
>>>>>>>>>>
>>>>>>>>>>any ideas why there should be this differnce
>>>>>>>>>>
>>>>>>>>>>as an extra confusion bith statements work perfectly on my development server.
>>>>>>>>>
>>>>>>>>>Is your development machine running against the same DB ?
>>>>>>>>>If not then I'd guess Naomi is right : bad data (or your function/s not handling some variation correctly)
>>>>>>>>
>>>>>>>>I don't think so. The data is an imported csv file. Imported into a temdb table and I've imported the same file onto both machines.
>>>>>>>
>>>>>>>Hmm. So same data but different instance of SQLServer ? Maybe some difference there causing the problem?
>>>>>>>Also see a few references to this error occurring from high memory use - might be worth checking whether the C# code is being efficient in releasing same - or tweak the memory allocation for SQL....
>>>>>>
>>>>>>Hi Viv
>>>>>>
>>>>>>The c# code is that CLR we discussed a while ago. How would I check it was being efficient and how would I change the memory allocation for SQL ? I do wonder if its some sort of resource problem as maybe the table update using the CLR creates a lot of threads simultanously. I don't know why this is not a problem on the XP development box.
>>>>>
>>>>>I only vaguely remember the CLR discussion. I guess just make sure that Dispose() gets called on anything implementing IDisposable(). I thought of mentioning a possible threading issue but I've no idea how (or if) SQL uses threading with the CLR.
>>>>>
>>>>>I *think* that you can set the maximum memory available to SQL so reducing it should free up more for the CLR. But I don't know enough SQL to be sure of this and, IAC, it might only mask the problem.
>>>>>
>>>>>Can you monitor memory usage to see if the problem does lie in that direction ?
>>>>
>>>>Well it is partly content driven after all.
>>>>
>>>>On the server 2008 box passing an empty string to the function causes SQL to restart
>>>>
>>>>declare @decrypt_key varchar(10)
>>>>set @decrypt_key = 'R@1113rs'
>>>>select dbo.fn_RMB_bank_decrypt('',@decrypt_key)
>>>>
>>>>on the wiodows XP box the CLR returns my (or the original writers) error msg 'Wrong Input. Bad Data. '
>>>
>>>So presumably you can debug the function outside SQL with an empty string and get the same exception ?
>>
>>I haven't yet but yes.
>>
>>My main concern is that on the server 2008 box the CLR managed to cause a restart on the SQL server no matter what was going wrong in the clr.
>
>Then I guess the trick is to make sure nothing *can* go wrong :-}

I'll let you know when I master that :-)