My faith in SQL Server has been knocked [modified]

[Shaun Wilde]

I'll say this now SQL (Server) and I have never been friends - there is something about it that grates with my OO brain; but I get by and I am determined to get much better. Now the rant. Been tracking a (unrepeatable, at the moment, bug in test - happens sporadically on live) bug in our system. Traced/reviewed all the code in SQL, BizTalk and C# moduled, all looks okay; code reviewed by many eyeballs and all involved puzzled. Started hitting google with random queries (usually coffee induced). Came across this article SCOPE_IDENTITY() sometimes returns incorrect value[^] scenario is very much like ours. WTF! SCOPE_IDENTITY() was one of my little comfort blankets when using SQL and now not even it offers the security I thought it did. Now researching the issue more thoroughly and going through the options suggested. I might start crying/drinking or both, if after testing/live the issue is resolved. [edited title due to CP pedantry]

I'll be more enthusiastic about encouraging thinking outside the box when there's evidence of any thinking going on inside it. - pTerry
BizSquawk

modified on Thursday, June 11, 2009 10:15 AM

[Ennis Ray Lynch Jr]

.

Need custom software developed? I do C# development and consulting all over the United States. A man said to the universe: "Sir I exist!" "However," replied the universe, "The fact has not created in me A sense of obligation." --Stephen Crane

[Michael Bookatz]

Couldn't say it better myself :confused::confused:

[Nagy Vilmos]

XIV years ago I similarly had an occassional problem; but this was with an Oracle db. The issue came down to a corruption of session data over the network. Lots of big word were being banded around and we just had to bite the bullet and down grade the application performance to counteract the issue with a lotsa dollar database.

---

Panic, Chaos, Destruction. My work here is done.

[Pete OHanlon]

Damn it. I blithely tell people to use scope_identity because we've been assured that it's guaranteed to be safe.

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys | Onyx

[ToddHileHoffer]

Wow! I have scope_identity() in about 1000 stored procedures that I have written. How can we tell if it will cause a problem. I don't know what the heck a "parallel plan" is.

I didn't get any requirements for the signature

[Shaun Wilde]

My thoughts exactly - for single inserts using INSERT/VALUE it shouldn't be an issue and it is rare you would use it to get the last ID of a multiple insert but in our case we had INSERT X (A, B, C) SELECT @A, @B, @C -- probably a product of an insert that evolved after many changes Easily changed to INSERT/VALUES style but not sure the parrellism would or would not kick in. Have to check the query plan

I'll be more enthusiastic about encouraging thinking outside the box when there's evidence of any thinking going on inside it. - pTerry
BizSquawk

[Giorgi Dalakishvili]

If you use nhibernate, you don't need to worry about such things: NHibernate: Avoid identity generator when possible[^]

Giorgi Dalakishvili #region signature My Articles Asynchronous Registry Notification Using Strongly-typed WMI Classes in .NET [^] My blog #endregion

[leppie]

Shaun Wilde wrote:

Came across this article SCOPE_IDENTITY() sometimes returns incorrect value[^] scenario is very much like ours.

Wont fix for both SQL2005 and SQL2008. Just great MS! It's the bloody Enterprise edition! :wtf:

xacc.ide
IronScheme - 1.0 beta 3 - out now!
((lambda (x) `((lambda (x) ,x) ',x)) '`((lambda (x) ,x) ',x))