Microsoft KB Archive/113437

= BUG: SQL Server GetDate Function Error: Record is Deleted =

Article ID: 113437

Article Last Modified on 11/7/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q113437



SYMPTOMS
Error 3167 "Record is deleted" can occur as records from the Dynaset are fetched when both of the following conditions are present:


 * The Dynaset is opened on a table in a SQL Server database that has an index based on a field of type date/time.
 * That indexed date/time field has been populated by the native SQL server function GetDate.

The records on which the error will occur are not predictable but are consistent.



WORKAROUND
Any one of the following three possible workarounds will work:


 * Use a snapshot object instead, or use a Dynaset with the DB_SQLPASSTHROUGH option, which is functionally the same thing as a snapshot.
 * Drop the index before and rebuild it after Visual Basic does the update if a non-passthrough Dynaset is needed in order to update the table in question.
 * Let Visual Basic do the updates to that table using the Now function instead of the stored procedure.



STATUS
Microsoft has confirmed this to be a bug in Visual Basic version 3.0 for Windows. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.



MORE INFORMATION
If records are added directly by Visual Basic (filling the date/time field by using the Now function), there is no problem. But if the indexed date/time field is assigned by the GetDate function, the error occurs.

Using GetDate and a Stored Procedure that May Result in the Problem
The GetDate function is an intrinsic function native to Microsoft SQL Server. The most likely situation is for this to be called from within a stored procedure, which in turn is called from a Visual Basic program. The following is the SQL Server syntax used to create a stored procedure that adds records to a SQL Server table named GetDateBugTab: create proc getdatebug As declare @dt datetime, @messagestr varchar(39) select @dt=GetDate select @messagestr = "This record added from stored proc" insert into GetDateBugTab (fDateTime, fsourceofdate) select @dt,@messagestr The structure of GetDateBugTab is reported by executing the system procedure sp_help GetDateBugTab against the SQL Server database.

Results look somewhat like this: Name                          Owner                        Type GetDateBugTab                 dbo                          user table Column_name  Type             Length Nulls Default_name    Rule_name - -- - --- --- fDateTime     datetime         8       1     (null)         (null) fsourceofdate varchar         39      1     (null)         (null) index_name   index_description                             index_keys }ndx         nonclustered, unique located on default       fDateTime The stored procedure would be executed from Visual Basic by using code such as the following.

Note You must change Username= and PWD = to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database. Dim db As database ' Enter the following two lines as one, single line of code: Set db = OpenDatabase("", 0, 0,  "odbc;Username= ;PWD= ;DSN=sqlserver2;database=playpen2;") label1 = db.ExecuteSQL("getdatebug")

Filling the Table by Using Visual Basic Directly Causes No Problem
By contrast, if the table is filled by Visual Basic code, no problem occurs. For example, the following code works without a problem.

Note You must change Username= and PWD = to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database. Dim db As database ' Enter the following two lines as one, single line of code: Set db = OpenDatabase("", 0, 0,  "odbc;Username= ;PWD= ;DSN=sqlserver2;database=playpen2;") dt$ = Now ' Enter the following two lines as one, single line of code: label1 = db.ExecuteSQL("insert into GetDateBugTab (fDateTime,fsourceofdate)  select '" & dt$ & "', 'This is from VB Now function'")

Using SQLPASSTHROUGH Still Causes a Problem
Alternatively, the entire body of the stored procedure can be sent to the SQL Server from Visual Basic. This is because the ExecuteSQL uses the SQLPASSTHROUGH flag and sends the syntax to the SQL Server for processing. This will still cause the error, however.

Note You must change Username= and PWD = to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database. Dim db As database ' Enter the following two lines as one, single line of code: Set db = OpenDatabase("", 0, 0,  "odbc;Username= ;PWD= ;DSN=sqlserver2;database=playpen2;") dt$ = Now ' Enter the following four lines as one, single line of code: label1 = db.ExecuteSQL("declare @dt datetime, @messagestr varchar(39)select  @dt=GetDate select @messagestr = "This record added from stored proc"   insert into GetDateBugTab (fDateTime,fsourceofdate)   select @dt,@messagestr")

