General information
Forum:
Microsoft SQL Server
>>>>>>>>>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 :-}
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only