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

- Professional Edition of Microsoft Visual Basic for Windows,

  version 3.0

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().

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

Dim db As database

' Enter the following two lines as one, single line of code:

   "odbc;uid=sa;pwd=;DSN=sqlserver2;database=playpen2;")

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:

Dim db As database

' Enter the following two lines as one, single line of code:

   "odbc;uid=sa;pwd=;DSN=sqlserver2;database=playpen2;")

' Enter the following two lines as one, single line of code:

   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.

Dim db As database

' Enter the following two lines as one, single line of code:

   "odbc;uid=sa;pwd=;DSN=sqlserver2;database=playpen2;")

' Enter the following four lines as one, single line of code:

   @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."