Bug in ADO, SQL Server and latest OS SPs

[Tony Toews [MVP]]

Thanks to a posting by fellow MVP Steve Foster

On a computer that is running Windows Vista, Windows Server 2008, or
Windows XP, an incorrect value is returned when an application queries
the identity column value of a newly inserted row in various versions
of SQL Server 2005 and of SQL Server 2000
http://support.microsoft.com/kb/951937

Now I don't *KNOW* that this affects Access but the KB article states
"Applications that use the ActiveX Data Objects (ADO) interface ..."

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[lyle fairfield]

This (the kb article is not wonderfully informative.

How does an application query "the identity column of a newly inserted
row"? Are we talking @@Identity?
How long is a newly inserted row a newly inserted row?

This occurs, according to the article, when we use a client side cursor.
So let's check:

First, do I qualify?
Microsoft SQL Server Management Studio Express 9.00.2047.00
Microsoft Data Access Components (MDAC) 6.0.6000.16386 (vista_rtm.061101-
2205)
Operating System 6.0.6000
Think so, but who can be sure?

Sub temp()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Set c = New ADODB.Connection

With c
..CursorLocation = adUseClient
..Open CurrentProject.BaseConnectionString
..Execute ("SET NOCOUNT ON")
End With

Set r = New ADODB.Recordset
With r
..ActiveConnection = c
..CursorLocation = adUseClient
..CursorType = adOpenStatic
..LockType = adLockOptimistic
..Open "SELECT * FROM Schools"

..AddNew Array(1), Array("Test")
Debug.Print .ActiveConnection.Execute("SELECT @@Identity")(0)
‘ 5280 – no bug here

..AddNew Array(1), Array("A Second Test")
Debug.Print .ActiveConnection.Execute("SELECT ID FROM Schools WHERE Name
= 'A Second Test'")(0)
‘ 5281 – no bug here

End With

Whew! My personal VBA code and ASP code and HTA Script may not be
affected.

But can I be sure that bound forms in Access aren’t affected? I don’t
know. Access is an application. One has only to look at Northwinds, the
code produced by Access Wizards, and various KB solutions to realize that
MS is indifferent to good programming and coding practices. So does this
article mean a bound form in an ADP may fail?

I have a couple ADPs in operation but no problem like the one described
has ever been reported to me.

I won’t worry about this right now, but perhaps, someone else will post
code that will demonstrate the bug?
"Tony Toews [MVP]" <tt****@telusplanet.netwrote in
news:g6********************************@4ax.com:

Thanks to a posting by fellow MVP Steve Foster

On a computer that is running Windows Vista, Windows Server 2008, or
Windows XP, an incorrect value is returned when an application queries
the identity column value of a newly inserted row in various versions
of SQL Server 2005 and of SQL Server 2000
http://support.microsoft.com/kb/951937

[Tony Toews [MVP]]

lyle fairfield <ly******@yah00.cawrote:

>This (the kb article is not wonderfully informative.

How does an application query "the identity column of a newly inserted
row"? Are we talking @@Identity?

I would think so but I don't know.

>How long is a newly inserted row a newly inserted row?

No idea.

>Operating System 6.0.6000
Think so, but who can be sure?

I can't by sure what OS and SP you are running. When I look at
Control Panel >System >General on my system I see Win XP SP2 so
I'm not affected. When I run msinfo32 I see an OS version of
5.1.2600.

I suspect you are running Windows Vista with no SP judging by the
middle 0 thus you wouldn't be affected.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Sylvain Lafontaine]

The article KB951937 makes a direct reference to the article KB940569 and
this second one is about this strange hotfix solving (?) the decade long
problem of using @@identity versus scope_identity() when there is a trigger
making insertions on the server side; for example when you are using merge
replication. (And it's far from necessary to use merge replication to
encounter this problem. There are lot and lot of other exemples of this
problem creeping in without using merge replication. I have myself had to
deal with this problem and ADP in the past.)

So, it's quite possible that to have an understanding of KB951937, we must
also read and test KB940569. Personnally, I never applied the hotfix
provided by KB940569 on a WinXP-Sp2 system because this article shows a
total lack of information about what it's doing exactly and its possible
consequences. (And for people have this problem, I would instead suggest the
usual solution of storing the value of @@identity in a local variable at the
beginning of the insert trigger and reset the value of @@identity to this
stored value at the end of the trigger. For an exemple of code, see:
http://groups.google.ca/group/micros...5ce86f0fe13b0a )
..

It's funny to see that at the end of the article 951937, there is a direct
reference about the Microsoft Retail System (RMS) and its famous problem
with WinXP-SP3. This is the (in-)famous problem that has delayed the wide
release of SP3 but has never been explained publicly. I suppose that we can
now see what happened between SP3 and RMS. Another funny thing is to see
that for people with WinXP-Sp3, KB951937 suggests to wait for the next
service pack (SP4?) instead of applying immediately this hotfix.
Personally, I will be very surprised if SP4 ever sees the light of the day;
especially in a timely manner.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:g6********************************@4ax.com...

Thanks to a posting by fellow MVP Steve Foster

On a computer that is running Windows Vista, Windows Server 2008, or
Windows XP, an incorrect value is returned when an application queries
the identity column value of a newly inserted row in various versions
of SQL Server 2005 and of SQL Server 2000
http://support.microsoft.com/kb/951937

Now I don't *KNOW* that this affects Access but the KB article states
"Applications that use the ActiveX Data Objects (ADO) interface ..."

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[lyle fairfield]

I installed Vista SP1 and ran the code. Again there was no error.

(Note change in MDAC as well as OS)
Microsoft SQL Server Management Studio Express 9.00.2047.00
Microsoft Data Access Components (MDAC) 6.0.6001.18000 (longhorn_rtm.
080118-1840)
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 8.0.6001.17184
Microsoft .NET Framework 2.0.50727.1434
Operating System 6.0.6001

On May 11, 2:33*pm, "Tony Toews [MVP]" <[e-mail]:

I suspect you are running Windows Vista with no SP judging by the
middle 0 thus you wouldn't be affected.

[Tony Toews [MVP]]

"Tony Toews [MVP]" <tt****@telusplanet.netwrote:

>On a computer that is running Windows Vista, Windows Server 2008, or
Windows XP, an incorrect value is returned when an application queries
the identity column value of a newly inserted row in various versions
of SQL Server 2005 and of SQL Server 2000
http://support.microsoft.com/kb/951937

Comments indicate that this problem applies to client side cursors and not server
side cursors. Access apparently uses service side cursors by default. So this may
not be a big deal at all.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[lyle fairfield]

I don't think Access uses Server Side Cursors be default.

Try this in an ADP:

Sub temp()
Debug.Print CurrentProject.Connection.CursorLocation = adUseServer
' shows False

' ------------
Debug.Print CurrentProject.Connection.CursorLocation = adUseClient
' shows True
' ------------

Is there something "non-defaultish" about the connection?
Debug.Print CurrentProject.BaseConnectionString

'PROVIDER=SQLOLEDB.1
;INTEGRATED SECURITY=SSPI
;PERSIST SECURITY INFO=FALSE
;INITIAL CATALOG=ESOII
;DATA SOURCE=VOSTRO-DESKTOP\SQLEXPRESS

... Not that jumps out at me.

Of course, Microsoft might say that Access uses Server Side Cursors by
default.

As usual with Microsoft statements that means that there is a fifty
per cent chance that Access uses Server Side Cursors by default.

On May 12, 5:17*pm, "Tony Toews [MVP]" <[e-mail]:

"Tony Toews [MVP]" <[e-mail]:

On a computer that is running Windows Vista, Windows Server 2008, or
Windows XP, an incorrect value is returned when an application queries
the identity column value of a newly inserted row in various versions
of SQL Server 2005 and of SQL Server 2000
http://support.microsoft.com/kb/951937

Comments indicate that this problem applies to client side cursors and notserver
side cursors. *Access apparently uses service side cursors by default. *So this may
not be a big deal at all.

Tony
--
Tony Toews, Microsoft Access MVP
* *Please respond only in the newsgroups so that others can
read the entire thread of messages.
* *Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
* *Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

[Tony Toews [MVP]]

lyle fairfield <ly************@gmail.comwrote:

>I don't think Access uses Server Side Cursors be default.

I have no idea about all this stuff. Sorry.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[aaron.kempf]

what is your point Tony?

Just demonstrating a lack of knowledge about the most popular database
in the world?

www.microsoft.com/sql

-Aaron

On May 10, 10:34*pm, "Tony Toews [MVP]" <[e-mail]>
wrote:

Thanks to a posting by fellow MVP Steve Foster

On a computer that is running Windows Vista, Windows Server 2008, or
Windows XP, an incorrect value is returned when an application queries
the identity column value of a newly inserted row in various versions
of SQL Server 2005 and of SQL Server 2000http://support.microsoft.com/kb/951937

Now I don't *KNOW* that this affects Access but the KB article states
"Applications that use the ActiveX Data Objects (ADO) interface ..."

Tony
--
Tony Toews, Microsoft Access MVP
* *Please respond only in the newsgroups so that others can
read the entire thread of messages.
* *Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
* *Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/