renu.iitkgp (5/9/2014)
Hi Solomon,
Thank you for the reply, that is very helpful.
When the memory footprint was so large and it wasn't been freed up, I tried dropping the user defined function and assembly and waited. Nothing happened and then I restarted the sql server service. I will try changing the PERMISSION_SET.
Can you please explain a little what exactly you mean by unloading the app domain? Is the app domain here, a host process which hosts CLR assemblies?
I used SqlString instead of SqlDateTime because, some of those many fields are strings.. Can you think of another alternative? Would it be better if I overload the function definition? (because in that case, return type also has to be different..)
(Also the tables and the fields on which this operation has to be done will be different across various client databases. So, the update statement is generated in a C# console app, using our own metadata layer..)
Thanks and Regards,
Venkat
Hi there.
Create one function using SqlDateTime for input and output parameter types. Then do CONVERT(DATETIME,...) and CONVERT(VARCHAR(30),...) on the way in and out of the UPDATE statements when needed.
App Domains are separate memory / processing spaces that are per Database and per Owner of the Assembly. Unloading it will clear out the memory. Look at the dmv: sys.app_domains (or something like that).
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR