db042188


after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected Is the fact that debugger doesnt show this behavior also expected




Re: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp

nielsb


You should be very, very careful using static variables!!! Yes, it is expecte behavior that they are being re-used, that are static after all. And in order to use static variables, your assembly has to be deployed as UNSAFE.

As for the debugger not showing this, I am a bit surprised by that.

Niels





Re: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp

db042188

is it fair to assume then that the sp's method(s) itself is also remaining in "memory" (ie not getting reloaded) between executions, unless SQL had to reclaim that memory from SQLCLR previously






Re: static variables in a SQLCLR stored proc seem to get reused from execution to execution of the sp

nielsb

Well, as the methods are static - the class constructor (cctor) will not run until the memory is being reclaimed (or the app-domain has been torn down etc). So in essence you are right with your assumption above.

Niels