Microsoft KB Archive/245714

= You cannot insert more than 250 KB of data in a SQL Server 7.0 or SQL Server 2000 Image field from Visual FoxPro =

Article ID: 245714

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 Q245714



SYMPTOMS
In Microsoft Visual FoxPro, if you use a remote view to insert or replace more than approximately 250 KB of data in an image field in a Microsoft SQL Server 7.0 or SQL Server 2000 table, causes the TableUpdate function to fail. You receive the following error message if you try to browse the table by double-clicking it in the remote view:

Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid locator de-referenced.



RESOLUTION
Here are two ways to work around this problem:   Use a TABLEUPDATE(.T.) after the first replace statement. For example use the following code: APPEND BLANK REPLACE IID WITH RECCOUNT+1, VERSIONNUMBER WITH 1, LASTUPDATE WITH DATETIME ? TABLEUPDATE(.T.) REPLACE DBCFILE WITH REPLICATE("@", 200000), DCXFILE WITH REPLICATE("@", 250000), DCTFILE WITH REPLICATE("@", 900000) ? TABLEUPDATE(.T.) -or-

 Set the driver in SQL 6.5 compatibility mode. For more information on how to set the compatibility mode of the SQL driver, see the References section.



Steps to Reproduce Behavior
 Create an ODBC connection to a SQL Server 7.0 or SQL Server 2000 database and name it SQL7INSPROB.  Use the following code to create a table in a SQL Server 7.0 or SQL Server 2000 database. This can be copied into the Query Analyzer and executed. Be sure to create the table in the database that you used in the ODBC connection. if exists (select * from sysobjects where id = object_id(N'[dbo].[systeminfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[systeminfo] GO CREATE TABLE [dbo].[systeminfo] ( [iid] [int] NOT NULL, [versionnumber] [int] NULL , [dbcfile] [image] NULL , [dcxfile] [image] NULL , [dctfile] [image] NULL , [bizfile] [image] NULL , [formfile] [image] NULL , [dataenvfile] [image] NULL , [otherfile] [image] NULL , [generalfile] [image] NULL , [lastupdate] [datetime] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[systeminfo] WITH NOCHECK ADD CONSTRAINT [PK_systeminfo] PRIMARY KEY NONCLUSTERED ( [iid] )  ON [PRIMARY] GO   Create and open a database in Visual FoxPro by entering the following commands in the Command window: CREATE DATABASE MyDatabase OPEN DATABASE MyDatabase MODIFY DATABASE NOWAIT </li>  Copy the following code into a program. Run the program to create a remote view in Visual FoxPro: ** Start of CODE CREATE SQL VIEW "RV_SYSTEMINFO" ; REMOTE CONNECT SQL7INSPROB; AS SELECT * FROM dbo.systeminfo Systeminfo DBSetProp('RV_SYSTEMINFO', 'View', 'UpdateType', 2) DBSetProp('RV_SYSTEMINFO', 'View', 'WhereType', 1) DBSetProp('RV_SYSTEMINFO', 'View', 'FetchMemo', .T.) DBSetProp('RV_SYSTEMINFO', 'View', 'SendUpdates', .T.) DBSetProp('RV_SYSTEMINFO', 'View', 'UseMemoSize', 255) DBSetProp('RV_SYSTEMINFO', 'View', 'FetchSize', 100) DBSetProp('RV_SYSTEMINFO', 'View', 'MaxRecords', -1) DBSetProp('RV_SYSTEMINFO', 'View', 'Tables', 'dbo.systeminfo') DBSetProp('RV_SYSTEMINFO', 'View', 'Prepared', .F.) DBSetProp('RV_SYSTEMINFO', 'View', 'CompareMemo', .T.) DBSetProp('RV_SYSTEMINFO', 'View', 'FetchAsNeeded', .F.) DBSetProp('RV_SYSTEMINFO', 'View', 'FetchSize', 100) DBSetProp('RV_SYSTEMINFO', 'View', 'Comment', "") DBSetProp('RV_SYSTEMINFO', 'View', 'BatchUpdateCount', 1) DBSetProp('RV_SYSTEMINFO', 'View', 'ShareConnection', .T.) DBSetProp('RV_SYSTEMINFO.iid', 'Field', 'KeyField', .T.) DBSetProp('RV_SYSTEMINFO.iid', 'Field', 'Updatable', .T.) DBSetProp('RV_SYSTEMINFO.iid', 'Field', 'UpdateName', 'dbo.systeminfo.iid') DBSetProp('RV_SYSTEMINFO.iid', 'Field', 'DataType', "I") DBSetProp('RV_SYSTEMINFO.versionnumber', 'Field', 'KeyField', .F.) DBSetProp('RV_SYSTEMINFO.versionnumber', 'Field', 'Updatable', .T.) DBSetProp('RV_SYSTEMINFO.versionnumber', 'Field', 'UpdateName', 'dbo.systeminfo.versionnumber') DBSetProp('RV_SYSTEMINFO.versionnumber', 'Field', 'DataType', "I") DBSetProp('RV_SYSTEMINFO.dbcfile', 'Field', 'KeyField', .F.) DBSetProp('RV_SYSTEMINFO.dbcfile', 'Field', 'Updatable', .T.) DBSetProp('RV_SYSTEMINFO.dbcfile', 'Field', 'UpdateName', 'dbo.systeminfo.dbcfile') DBSetProp('RV_SYSTEMINFO.dbcfile', 'Field', 'DataType', "M NOCPTRANS") DBSetProp('RV_SYSTEMINFO.dcxfile', 'Field', 'KeyField', .F.) DBSetProp('RV_SYSTEMINFO.dcxfile', 'Field', 'Updatable', .T.) DBSetProp('RV_SYSTEMINFO.dcxfile', 'Field', 'UpdateName', 'dbo.systeminfo.dcxfile') DBSetProp('RV_SYSTEMINFO.dcxfile', 'Field', 'DataType', "M NOCPTRANS") DBSetProp('RV_SYSTEMINFO.dctfile', 'Field', 'KeyField', .F.) DBSetProp('RV_SYSTEMINFO.dctfile', 'Field', 'Updatable', .T.) DBSetProp('RV_SYSTEMINFO.dctfile', 'Field', 'UpdateName', 'dbo.systeminfo.dctfile') DBSetProp('RV_SYSTEMINFO.dctfile', 'Field', 'DataType', "M NOCPTRANS") DBSetProp('RV_SYSTEMINFO.bizfile', 'Field', 'KeyField', .F.) DBSetProp('RV_SYSTEMINFO.bizfile', 'Field', 'Updatable', .T.) DBSetProp('RV_SYSTEMINFO.bizfile', 'Field', 'UpdateName', 'dbo.systeminfo.bizfile') DBSetProp('RV_SYSTEMINFO.bizfile', 'Field', 'DataType', "M NOCPTRANS") DBSetProp('RV_SYSTEMINFO.formfile', 'Field', 'KeyField', .F.) DBSetProp('RV_SYSTEMINFO.formfile', 'Field', 'Updatable', .T.) DBSetProp('RV_SYSTEMINFO.formfile', 'Field', 'UpdateName', 'dbo.systeminfo.formfile') DBSetProp('RV_SYSTEMINFO.formfile', 'Field', 'DataType', "M NOCPTRANS") DBSetProp('RV_SYSTEMINFO.dataenvfile', 'Field', 'KeyField', .F.) DBSetProp('RV_SYSTEMINFO.dataenvfile', 'Field', 'Updatable', .T.) DBSetProp('RV_SYSTEMINFO.dataenvfile', 'Field', 'UpdateName', 'dbo.systeminfo.dataenvfile') DBSetProp('RV_SYSTEMINFO.dataenvfile', 'Field', 'DataType', "M NOCPTRANS") DBSetProp('RV_SYSTEMINFO.otherfile', 'Field', 'KeyField', .F.) DBSetProp('RV_SYSTEMINFO.otherfile', 'Field', 'Updatable', .T.) DBSetProp('RV_SYSTEMINFO.otherfile', 'Field', 'UpdateName', 'dbo.systeminfo.otherfile') DBSetProp('RV_SYSTEMINFO.otherfile', 'Field', 'DataType', "M NOCPTRANS") DBSetProp('RV_SYSTEMINFO.generalfile', 'Field', 'KeyField', .F.) DBSetProp('RV_SYSTEMINFO.generalfile', 'Field', 'Updatable', .T.) DBSetProp('RV_SYSTEMINFO.generalfile', 'Field', 'UpdateName', 'dbo.systeminfo.generalfile') DBSetProp('RV_SYSTEMINFO.generalfile', 'Field', 'DataType', "M NOCPTRANS") DBSetProp('RV_SYSTEMINFO.lastupdate', 'Field', 'KeyField', .F.) DBSetProp('RV_SYSTEMINFO.lastupdate', 'Field', 'Updatable', .T.) DBSetProp('RV_SYSTEMINFO.lastupdate', 'Field', 'UpdateName', 'dbo.systeminfo.lastupdate') DBSetProp('RV_SYSTEMINFO.lastupdate', 'Field', 'DataType', "T") </li>  With the database still open, enter the following commands in the Command window or copy them into a program and run the program: *** Start of Code USE rv_systeminfo APPEND BLANK REPLACE IID WITH RECCOUNT+1, VERSIONNUMBER WITH 1, LASTUPDATE WITH DATETIME REPLACE DBCFILE WITH REPLICATE("@", 200000), DCXFILE WITH REPLICATE("@", 250000), DCTFILE WITH REPLICATE("@", 900000) ? TABLEUPDATE(.T.) </li> If you double-click the remote view in the Database designer, the preceding error message appears.</li></ol>
 * !* Field Level Properties for RV_SYSTEMINFO
 * Props for the RV_SYSTEMINFO.iid field.
 * Props for the RV_SYSTEMINFO.versionnumber field.
 * Props for the RV_SYSTEMINFO.dbcfile field.
 * Props for the RV_SYSTEMINFO.dcxfile field.
 * Props for the RV_SYSTEMINFO.dctfile field.
 * Props for the RV_SYSTEMINFO.bizfile field.
 * Props for the RV_SYSTEMINFO.formfile field.
 * Props for the RV_SYSTEMINFO.dataenvfile field.
 * Props for the RV_SYSTEMINFO.otherfile field.
 * Props for the RV_SYSTEMINFO.generalfile field.
 * Props for the RV_SYSTEMINFO.lastupdate field.
 * End of Code
 * Table update will return false.

The following INSERT INTO statement also causes the problem: *** Start of Code insert into rv_systeminfo (iid, versionnumber, dbcfile, dcxfile, dctfile) VALUES (reccount+1, 1, replicate("@", 200000), replicate("@", 250000), replicate("@", 900000))
 * End of Code

<div class="references_section">