Article ID: 253886
Article Last Modified on 2/12/2007
APPLIES TO
- Microsoft Visual FoxPro 6.0 Professional Edition
- Microsoft Visual FoxPro 7.0 Professional Edition
- Microsoft Visual FoxPro 8.0 Professional Edition
- 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.
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())))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())))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
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- Modify the second line of code to reflect your SQL Server name.
- 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.
Keywords: kbcodesnippet kbdatabase kbprb kbsqlprog KB253886
