Since the installation of Service Pack 1 for Windows Vista and Service Pack
3
for Windows XP, a piece of code is failing which has always worked without
problems before. We've managed to isolate the problem and reproduce it in
a
small sample.
The sample uses the following database objects:
CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[num] [int] NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
CREATE VIEW TestView
WITH VIEW_METADATA
AS
SELECT ID, num FROM TestTable
ALTER TRIGGER TestView_Insert ON TestView
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO TestTable (num) SELECT num FROM inserted
END
Yes, I know the view and its trigger in this case don't make a lot of
sense,
but in our more complex case this approach drastically simplifies the
application code.
On this database the following VB6 code is executed:
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
cn.Provider = "SQLOLEDB"
cn.ConnectionString =
"Server=ServerName;Database=TestDB;Trusted_Connection=No;User
ID=user;Password=pwd"
Call cn.Open
rs.CursorLocation = adUseClient
Call rs.Open("SELECT * FROM TestView WHERE 1 = 0;", cn, adOpenStatic,
adLockBatchOptimistic)
Set rs.ActiveConnection = Nothing
Call cn.Close
With rs
Call .AddNew
.Fields("ID").Value = -1
.Fields("num").Value = 1
Call .Update
End With
With rs
Call .AddNew
.Fields("ID").Value = -2
.Fields("num").Value = 1
Call .Update
End With
Call cn.Open
Set rs.ActiveConnection = cn
Call rs.UpdateBatch(adAffectAllChapters) ' <- fails here
Set rs.ActiveConnection = Nothing
Call cn.Close
The code fails in the marked line with the error:
Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.
At least two rows have to be added to the table for the error two appear.
SQL traces show that the first INSERT statement succeeds, but the second
one
never appears in the trace. This sample manifests the problem on both SQL
Server 2000 and SQL Server 2005. The production code seems to work fine on
SQL Server 2005 but we haven't managed to find out why the behavior is
different in this sample.
As I stated previously this works just fine on Windows Vista without SP1
and
on Windows XP SP2. Something must have changed with the latest service
packs.
It would be really nice to know what exactly is happening here and whether
there is any workaround to make this work. We've already started fixing
our
code to avoid the need for such views since this really is a pressing
matter
for our customers which are just starting to deploy the Service Packs.
|
