SCOPE_IDENTITY Fails Intermittently

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SCOPE_IDENTITY Fails Intermittently

Author

Topic

mleerog
Starting Member

3 Posts

Posted - 2007-03-28 : 14:17:06

A couple of classic ASP applications which communicate with different SQL Server 2000 databases use SCOPE_IDENTITY in embedded SQL to retrieve the key value of a record that was just inserted. It works--most of the time. However, from time to time the identity value is not retrieved. Evidence suggests that in these cases, a null value is being retrieved. This has forced me to come up with less-than-ideal workarounds for the missing identity value.

A review of posts on various sites indicates that the majority opinion favors using stored procedures to handle this kind of operation instead of embedded SQL. Is a stored procedure required in this situation instead of embedded SQL? There are no triggers on the table that could interfere with the Web page as it calls the data base to perform the insert.

It would be easier to understand if SCOPE_IDENTITY did not work at all, but intermittent failures are somewhat puzzling. Does anyone have any idea why SCOPE_IDENTITY sometimes fails to retrieve the identity value and transmit it back to the Web page? Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-28 : 14:23:07

SCOPE_IDENTITY does indeed work. You will need to post your code in order for us to determine where the bug is.

BTW, what service pack are you running for SQL Server 2000?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 14:37:36

I'm with Tara here.

I have never experienced SCOPE_IDENTITY() to fail. I have seen @@IDENTITY and IDENT_CURRENT to return another value than expected, but never NULL.

Peter Larsson
Helsingborg, Sweden

mleerog
Starting Member

3 Posts

Posted - 2007-03-28 : 14:42:29

We are running SQL Server 2000 SP 4.

Here is the classic ASP code that causes the problem. The error never happens when I am using the application, only when other people use it. Even then, it only happens occasionally.

varSQL1 = "SET NOCOUNT ON "
varSQL1 = varSQL1 & "INSERT INTO tblSchCalEvents "
varSQL1 = varSQL1 & "(EventDateTime, "
varSQL1 = varSQL1 & "EventEntryDateTime, "
If (Len(varEventHeadline) > 0) Then
    varSQL1 = varSQL1 & "EventHeadline, "
End If
varSQL1 = varSQL1 & "EventDescription) "
varSQL1 = varSQL1 & "VALUES "
varSQL1 = varSQL1 & "('" & varEventDateTime & "', "
varSQL1 = varSQL1 & "'" & varEventEntryDateTime & "', "
If (Len(varEventHeadline) > 0) Then
    varSQL1 = varSQL1 & "'" & varEventHeadline & "', "
End If
varSQL1 = varSQL1 & "'" & varEventDescription & "') "
varSQL1 = varSQL1 & "SELECT InsIdent = SCOPE_IDENTITY() "
varSQL1 = varSQL1 & "SET NOCOUNT OFF"
Set objSchCalEvent = Server.CreateObject("ADODB.Recordset")
objSchCalEvent.Open varSQL1, objConn, adOpenDynamic, adLockOptimistic, adCmdText
varEventID = objSchCalEvent("InsIdent")
varEventIDDisplay = CStr(varEventID)
Set objSchCalEvent = Nothing

Any ideas would be appreciated. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-28 : 14:47:18

I do all of my data access through stored procedures. You may want to make the switch in case there is some known issue with the driver that you are using.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 14:49:38

This is wrong on so many levels...

What you can experience is that the dynamic SQL exits before getting the IDENTITY value, thus the entire records is empty.
Zero columns.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-28 : 14:49:50

Maybe this will be a good opportunity to use stored procedures and parameters? Sure makes your code shorter and easier to debug and work with. there are so many possible points of failure here that it is hard to isolate exactly where the problem may be. The biggest issue is this: If someone enters a headline with a ' character in it, the whole statement fails since the SQL produced is invalid! of course, that will result in no scope_identity being returned. Let me guess: is there an ON ERROR RESUME NEXT right before all this?

As a rule, always, always, always use parameters; never concatenate together a big sql string like that. see:
http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

the examples cover .net, but the exact same principles apply to ASP.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 14:51:38

Please read this topic. It applies also to web applications.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70783

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-28 : 14:59:11

nice link, Peso! It is so rare that someone actually says "wow, at first I completely disagreed with you, but I have thought about it, you are right, thank you!"

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 15:07:42

Yes, those moments are rare. And you feel you make a difference.
Most OP's just go on, not thinking about nor trying to understand the solutions they are given. Thus next question is about same problem again! In less than a day or two...

The OP in the link above has not posted a similar problem since.

Peter Larsson
Helsingborg, Sweden

mleerog
Starting Member

3 Posts

Posted - 2007-03-28 : 15:25:32

I will read the referral posts and consider how to proceed.

One tends to do these jobs using whatever code first shows up. That is why I still use embedded SQL. This is the first time it has caused a problem like this.

The apostrophe in a text field could disable the SQL string. In some inherited code elsewhere on the web site, the single apostrophes have been escaped to double apostrophes.

One last question. Aside from a problem with the SQL in the embedded string, is it possible for SCOPE_IDENTITY to go out of scope in a single Web page? The table does not have any triggers to mess with the execution scope.

Thanks for the ideas.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 15:36:48

Both @@IDENTITY and IDENT_CURRENT looks at the table itself, in any scope.
If two users INSERT record at the almost exact time, there is a small, tiny, possibility that the first insert get the identity of the second insert (other user).

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources