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]]

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/micro...rt+trigger+@@identity&rnum=1#2f5ce86f0fe13b0a )
..

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)

 

[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

 

[Charles Wang [MSFT]]

Hi Tony and Lyle,
The KB article does not refer to using @@IDENTITY since "SELECT @@IDENTITY"
also happens at SQL Server side. The issue refers to using ADO Recordset to
retrieve the identity column value after you newly insert one row. I
reproduce this issue with the following code:
=====================================
1. Create the following table in SQL Server 2005
CREATE TABLE [dbo].[supplier]
( [ID] [int] IDENTITY(1,1) NOT NULL,[supplierName] [nchar] (10) NULL)

2. In Access 2007, write the following code:
Dim conn As ADODB.Connection
Dim supplier As ADODB.Recordset

Set conn = New ADODB.Connection
Set supplier = New ADODB.Recordset


supplier.CursorLocation = 3
supplier.CursorTYpe = 2
supplier.lockType = 4

conn.Open "Provider=SQLOLEDB;Data Source=Charles;Initial
Catalog=TestDB;Integrated Security=SSPI"
supplier.Open "set nocount on; select * from supplier", conn
supplier.AddNew
supplier("supplierName") = "xxxx"
supplier.updateBatch
supplierID = supplier("ID")
MsgBox supplierID
=====================================

My test is on Vista SP1. This issue does not happen on Vista RTM version.
If you have some code like my sample code and your OS is indeed one of the
listed OSes in the KB951937, please install the hotfix.

Hope this helps!

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!


Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Tony Toews [MVP]]

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.

 

[Tony Toews [MVP]]

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

 

[Charles Wang [MSFT]]

Hi All,
Regarding cursors in Access, you can find the following description in the
article, http://msdn.microsoft.com/en-us/library/aa141688(office.10).aspx.
==========================================
Recordset objects always use the client-side cursor engine
(CursorLocation=adUseClient). Even if you set the CursorLocation property
of a Recordset object to adUseServer prior to creating a Recordset object
by using the Execute method of a Connection object, the Execute method of a
Command object, or the Open method of a Recordset object, you will always
get a client-side cursor.
==========================================

Hope this helps.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Sylvain Lafontaine]

Recordset objects always use the client-side cursor engine

(CursorLocation=adUseClient). Even if you set the CursorLocation property
of a Recordset object to adUseServer prior to creating a Recordset object
by using the Execute method of a Connection object, the Execute method of
a
Command object, or the Open method of a Recordset object, you will always
get a client-side cursor.

This information is only for the Connection of the CurrentProject object in
an ADP project; it's not true for opening an ADO recordset in general like
in the exemple that you have given us in your earlier post.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hi All,
Regarding cursors in Access, you can find the following description in the
article, http://msdn.microsoft.com/en-us/library/aa141688(office.10).aspx.
==========================================
Recordset objects always use the client-side cursor engine
(CursorLocation=adUseClient). Even if you set the CursorLocation property
of a Recordset object to adUseServer prior to creating a Recordset object
by using the Execute method of a Connection object, the Execute method of
a
Command object, or the Open method of a Recordset object, you will always
get a client-side cursor.
==========================================

Hope this helps.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
=========================================================

 

[Charles Wang [MSFT]]

Hi Syvain,
Yes, you are right. I am sorry that I did not notice that it was dedicated
for ADP. From my further research, for Access database, by default ADO will
use adUseServer as the cursorlocation.You can find the following
description in this article,
http://msdn.microsoft.com/en-us/library/aa141422(office.10).aspx.
==================================
To specify the cursor engine for a Recordset object, you must set the
CursorLocation property before opening the Recordset object. If you set the
CursorLocation property of a Connection object, any Recordset object you
open while using that Connection object will inherit that connection's
setting. By default, when opening a Connection object for an Access
database, the CursorLocation property is adUseServer.
==================================

Hope this clear the confusions.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================