BUG: SQL Server GetDate() Function Error: Record is Deleted (113437)


The information in this article applies to:

  • Microsoft Visual Basic Professional Edition for Windows 3.0
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= <username> and PWD =<strong password> 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=<username>;PWD=<strong password>;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= <username> and PWD =<strong password> 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=<username>;PWD=<strong password>;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= <username> and PWD =<strong password> 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=<username>;PWD=<strong password>;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")

REFERENCES

More information about calling stored procedures is documented in the following Microsoft SQL manual which covers the Visual Basic Library for SQL Server: "Microsoft SQL Server Programmer's Reference for Visual Basic."
Modification Type:MajorLast Reviewed:11/7/2003
Keywords:kbbug KB113437
©2003 Microsoft Corporation. All rights reserved.