An ODBC error may occur when you update the DateTime data in Microsoft SQL Server 7.0 or later versions and in Visual FoxPro (253886)


The information in this article applies to:

  • Microsoft Visual FoxPro for Windows 6.0
  • Microsoft Visual FoxPro for Windows 7.0
  • Microsoft Visual FoxPro 8.0
  • Microsoft Visual FoxPro 9.0 Professional Edition
This article was previously published under Q253886

SYMPTOMS

When the DateTime data type is updated from remote views, an ODBC error may occur if the DateTime data type is first populated in Microsoft SQL Server 7.0 or later versions. This issue occurs in SQL Server 7.0, in SQL Server 2000, or in Microsoft Data Engine. An update conflict occurs because of the difference in the precision with which SQL Server 7.0 or later versions store DateTime data and the precision with which Microsoft Visual FoxPro stores DateTime data.

The precision in SQL Server 7.0 or later versions is in milliseconds. The precision in Visual FoxPro does not include milliseconds. When the data is updated, the ODBC Driver detects the milliseconds in SQL Server 7.0 or later versions. Then, the ODBC Driver incorrectly interprets that the data has been updated. This creates an update conflict.

WORKAROUND

There are three different workarounds for this problem. Any one of them must be accomplished against the SQL Server database.
  1. Change the DateTime data type to a SmallDateTime data type.

    Create a stored procedure that returns the data stored in SQL Server in a converted format with millisecond precision removed. For example:
    (convert(datetime,convert(char,getdate())))
    If you are setting a default date in the column, use the following code to remove the millisecond precision:
    (convert(datetime,convert(char,getdate())))
  2. Create a stored procedure that returns the data stored in SQL Server in a converted format with millisecond precision removed. For example:
    (convert(datetime,convert(char,getdate())))
    If you are setting a default date in the column, use the following code to remove the millisecond precision:
    (convert(datetime,convert(char,getdate())))
  3. If you are setting a default date in the column, use the following code to remove the millisecond precision:
    (convert(datetime,convert(char,getdate())))

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Cut and paste the following code in a Visual FoxPro program.
    #DEFINE vfCRLF   CHR(13) + CHR(10)<BR/>

cServer = "MyServer"    && *** ADD YOUR SERVER NAME HERE ****
<BR/>
hdl = SQLSTRINGCONNECT("DRIVER=SQL Server;SERVER=" + cServer + ;
	";UID=sa;PWD=;APP=Microsoft Open Database Connectivity;DATABASE=tempdb")
IF hdl > 0
	cStr = "drop table [dbo].[SQLSetTime] "
	= sqlexec(hdl, cStr)
	cStr = "CREATE TABLE [dbo].[SQLSetTime] ( " + ;
		"[field_1] [int] IDENTITY (1, 1) , " + ;
		"[field_2] [int] , " + ;
		"[date1] [datetime] , " + ;
		"[date2] [smalldatetime] , " + ;
		"[date3] [datetime] " + ;
		") "
	= sqlexec(hdl, cStr)
	cStr = "ALTER TABLE [dbo].[SQLSetTime] WITH NOCHECK ADD " + vfCRLF + ;
		"CONSTRAINT [DF_SQLSetTime_date1] DEFAULT (getdate()) FOR [date1], " + vfCRLF + ;
		"CONSTRAINT [DF_SQLSetTime_date2] DEFAULT (getdate()) FOR [date2], " + vfCRLF + ;
		"CONSTRAINT [DF_SQLSetTime_date3] DEFAULT (convert(datetime,convert(char,getdate()))) FOR [date3] "
	= sqlexec(hdl, cStr)
	cStr = "SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON  "
	= sqlexec(hdl, cStr)
	cStr = "SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON  "
	= sqlexec(hdl, cStr)
	FOR x = 1 TO 10
		cStr = "INSERT INTO SqlSetTime (field_2) VALUES (?x)"
		= sqlexec(hdl, cStr)
	ENDFOR
	= sqldisconnect(hdl)

ENDIF

DisplayStatus([Creating database...])
CLOSE DATA ALL
CREATE DATABASE 'DATA1.DBC'
DisplayStatus([Creating connection CONNECT1...])
MakeConn_CONNECT1()
DisplayStatus([Creating view RV_SQLSETTIME...])
MakeView_RV_SQLSETTIME()
DisplayStatus([Finished.])
USE rv_sqlsettime

REPLACE date1 WITH DATETIME() - 1000000
SKIP                         && This line will cause an update conflict error
REPLACE date2 WITH DATETIME() - 1000000
SKIP
REPLACE date3 WITH DATETIME() - 1000000
SKIP
BROWSE

FUNCTION MakeConn_CONNECT1
	***************** Connection Definitions CONNECT1 ***************
	CREATE CONNECTION CONNECT1 ;
		CONNSTRING "DRIVER=SQL Server;SERVER=" + cServer + ";UID=sa;PWD=;APP=Microsoft Open Database Connectivity;DATABASE=tempdb"
	****
	DBSETPROP('CONNECT1', 'Connection', 'Asynchronous', .F.)
	DBSETPROP('CONNECT1', 'Connection', 'BatchMode', .T.)
	DBSETPROP('CONNECT1', 'Connection', 'Comment', '')
	DBSETPROP('CONNECT1', 'Connection', 'DispLogin', 1)
	DBSETPROP('CONNECT1', 'Connection', 'ConnectTimeOut', 15)
	DBSETPROP('CONNECT1', 'Connection', 'DispWarnings', .F.)
	DBSETPROP('CONNECT1', 'Connection', 'IdleTimeOut', 0)
	DBSETPROP('CONNECT1', 'Connection', 'QueryTimeOut', 0)
	DBSETPROP('CONNECT1', 'Connection', 'Transactions', 1)
	DBSETPROP('CONNECT1', 'Connection', 'Database', '')
ENDFUNC
FUNCTION MakeView_RV_SQLSETTIME
	***************** View setup for RV_SQLSETTIME ***************
	CREATE SQL VIEW "RV_SQLSETTIME" ;
		REMOTE CONNECT "Connect1" ;
		AS SELECT Sqlsettime.field_1, Sqlsettime.date1, Sqlsettime.date2,  Sqlsettime.date3 FROM dbo.Sqlsettime Sqlsettime
	DBSETPROP('RV_SQLSETTIME', 'View', 'UpdateType', 1)
	DBSETPROP('RV_SQLSETTIME', 'View', 'WhereType', 3)
	DBSETPROP('RV_SQLSETTIME', 'View', 'FetchMemo', .T.)
	DBSETPROP('RV_SQLSETTIME', 'View', 'SendUpdates', .T.)
	DBSETPROP('RV_SQLSETTIME', 'View', 'UseMemoSize', 255)
	DBSETPROP('RV_SQLSETTIME', 'View', 'FetchSize', 100)
	DBSETPROP('RV_SQLSETTIME', 'View', 'MaxRecords', -1)
	DBSETPROP('RV_SQLSETTIME', 'View', 'Tables', 'dbo.SQLSetTime')
	DBSETPROP('RV_SQLSETTIME', 'View', 'Prepared', .F.)
	DBSETPROP('RV_SQLSETTIME', 'View', 'CompareMemo', .T.)
	DBSETPROP('RV_SQLSETTIME', 'View', 'FetchAsNeeded', .F.)
	DBSETPROP('RV_SQLSETTIME', 'View', 'FetchSize', 100)
	DBSETPROP('RV_SQLSETTIME', 'View', 'Comment', "")
	DBSETPROP('RV_SQLSETTIME', 'View', 'BatchUpdateCount', 1)
	DBSETPROP('RV_SQLSETTIME', 'View', 'ShareConnection', .F.)
	*!* Field Level Properties for RV_SQLSETTIME
	* Props for the RV_SQLSETTIME.field_1 field.
	DBSETPROP('RV_SQLSETTIME.field_1', 'Field', 'KeyField', .T.)
	DBSETPROP('RV_SQLSETTIME.field_1', 'Field', 'Updatable', .T.)
	DBSETPROP('RV_SQLSETTIME.field_1', 'Field', 'UpdateName', 'dbo.SQLSetTime.field_1')
	DBSETPROP('RV_SQLSETTIME.field_1', 'Field', 'DataType', "I")
	* Props for the RV_SQLSETTIME.date1 field.
	DBSETPROP('RV_SQLSETTIME.date1', 'Field', 'KeyField', .F.)
	DBSETPROP('RV_SQLSETTIME.date1', 'Field', 'Updatable', .T.)
	DBSETPROP('RV_SQLSETTIME.date1', 'Field', 'UpdateName', 'dbo.SQLSetTime.date1')
	DBSETPROP('RV_SQLSETTIME.date1', 'Field', 'Caption', "DateTime")
	DBSETPROP('RV_SQLSETTIME.date1', 'Field', 'DataType', "T")
	* Props for the RV_SQLSETTIME.date2 field.
	DBSETPROP('RV_SQLSETTIME.date2', 'Field', 'KeyField', .F.)
	DBSETPROP('RV_SQLSETTIME.date2', 'Field', 'Updatable', .T.)
	DBSETPROP('RV_SQLSETTIME.date2', 'Field', 'UpdateName', 'dbo.SQLSetTime.date2')
	DBSETPROP('RV_SQLSETTIME.date2', 'Field', 'Caption', "SmallDateTime")
	DBSETPROP('RV_SQLSETTIME.date2', 'Field', 'DataType', "T")
	* Props for the RV_SQLSETTIME.date3 field.
	DBSETPROP('RV_SQLSETTIME.date3', 'Field', 'KeyField', .F.)
	DBSETPROP('RV_SQLSETTIME.date3', 'Field', 'Updatable', .T.)
	DBSETPROP('RV_SQLSETTIME.date3', 'Field', 'UpdateName', 'dbo.SQLSetTime.date3')
	DBSETPROP('RV_SQLSETTIME.date3', 'Field', 'Caption', "DateTime (converted)")
	DBSETPROP('RV_SQLSETTIME.date3', 'Field', 'DataType', "T")
ENDFUNC
FUNCTION DisplayStatus(lcMessage)
	WAIT WINDOW NOWAIT lcMessage
ENDFUNC
  2. Modify the second line of code to reflect your SQL Server name.
  3. Save and run the program.Expected Results:

    The preceding code creates a table in SQL Server and populates it with 10 records with default date values. It then creates a Visual FoxPro database with a connection to the SQL Server (you need to supply the name of the server). Then it modifies the date field and tries to move off the record. An Update Conflict occurs after the first occurrence because there is extra data in date1 (the milliseconds) on SQL Server. Visual FoxPro incorrectly interprets that the data has been altered on SQL Server and reports the error.
Modification Type:MajorLast Reviewed:3/18/2005
Keywords:kbCodeSnippet kbDatabase kbprb kbSQLProg KB253886
©2004 Microsoft Corporation. All rights reserved.