Is this serious SQL/CLR bug present in SQL Server 2012?

Question

I'm rather surprised to see that even after two months nobody on the SQL/CLR team has made any comment about this very serious bug report that pertains to SQL/CLR trigger support in SQL Server 2008 R2.

I am also now wondering if this same bug is present in the upcoming next version - SQL Server 2012 - because the SQL/CLR team appear to be totally disinterested in this I have no idea on workarounds or fixes etc.

I have not had an opportunity yet to subject SQL Server 2012 to any testing in this area, but others might be evaluating it right now, in which case why not see if you can repro it? I'd be happy to answer questions about it here too, I did a great deal of in-depth development and testing with SQL/CLR - far more than most - and got impressive results but also found some serious shortcomings, this one being a show-stopper for us.

 

Thanks

Hugh

 

 

 

---

Hugh Moran - http://www.morantex.com

Answer 1

Hi Hugh,

 

Did you see this knowledge base article? http://support.microsoft.com/kb/960922. It appears to be by design, and I’ve heard nothing of it changing it SQL Server 2012.

 

Cheers,

Bob

Answer 2

Hi Hugh,

 

Did you see this knowledge base article? http://support.microsoft.com/kb/960922. It appears to be by design, and I’ve heard nothing of it changing it SQL Server 2012.

 

Cheers,

Bob

Hi Bob;

Yes I am actually aware of the article, I discovered it whilst investigating the bug. It is not clear though, it reads to me as if this can happen during the brief time that the connection is used or opened. The error that I reported occurs after that, just enumerating through a SqlDataReader object looking at the returned fields in the result rows.

Furthermore I find this bizzare indeed, MS only approve a small set of assemblies for use in SQL/CLR code and the SqlConnection and SqlDataReader are all part of that approved list. I suspect that the design of the SQL CLR policy is sound BUT the current design of the SqlDataReader class involving a syncblock lock is unsuited to use in SQL/CLR.

The resolution here is to improve these SQL classes so that they never use syncblock locks and conform to the rules of SQL/CLR design that we developers are told to adhere to, it is no good if our own code obeys the rules yet Microsoft's does not.

This is all very muddy really and I'd love to get some specific response from a member of the SQL/CLR team.

Many thanks again!

 

Hugh

 

 

 

 

 

---

Hugh Moran - http://www.morantex.com

Answer 3

hi Moran

Similar Error is happenning in our Production system. I have a SQL CLR component which hits a web service.. I see you saying that you could repro this error.(http://connect.microsoft.com/SQLServer/feedback/details/708939/simple-clr-trigger-using-sqldatareader-via-context-connection-causes-appdomain-unload).. How did you repro? Appreciate your pointers..

Answer 4

hi Moran

Similar Error is happenning in our Production system. I have a SQL CLR component which hits a web service.. I see you saying that you could repro this error.(http://connect.microsoft.com/SQLServer/feedback/details/708939/simple-clr-trigger-using-sqldatareader-via-context-connection-causes-appdomain-unload).. How did you repro? Appreciate your pointers..

Hello - sorry but I only just discovered your post here.

Unfortunately Microsoft have expressed no interest whatsoever in this bug - it is readily reproducible and my bug report has an attached zip for them to use.

The demo code is simply a managed trigger that loops doing a lookup on a table with the context connection, it loops only to help reveal the bug.

I did a great deal of SQL/CLR this past two years and had my foot to the metal with this technology - I found a host of oddities.

One major pain is that NOTHING is documented about how cancel (in SSMS) is represented to managed code - not a SINGLE document explains this so you are left to find out the hard way.

The bottom line is that cancelling in SSMS will force the entire AppDomain to get silently unloaded and impact all other connections that might be running managed code in that AppDomain, This ONLY happens if the thread experiencing the cancel happens to hold any locks at the instant of the cancel.

For this reason managed code is heavily discouraged from using locks (and hence from sharing static data).

However Microsoft's OWN code (ADO etc) relies internally on such locks and so using ADO within SQL/CLR is itself as risky as using your own locks!

They don't tell you this in any documentation and so you have to find out the hard way and then discover that its too late, after developing loads of code that is unreliable because MS themselves have not adhered to their own rules!

This cost me a ton of time and a customer was impacted too - to add insult to injury the bug report is flagged as 'duplicate' still even though I've repeatedly explained to them that it is not.

MS do not seem to attach much value to user feedback when it comes to SQL/CLR.

Cap'n

 

---

Hugh Moran - http://www.morantex.com