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


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

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create an ODBC connection to a SQL Server 7.0 or SQL Server 2000 database and name it SQL7INSPROB.
  2. 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
  3. 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
  4. 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.)
*!* Field Level Properties for RV_SYSTEMINFO
* Props for the RV_SYSTEMINFO.iid field.
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")
* Props for the RV_SYSTEMINFO.versionnumber field.
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")
* Props for the RV_SYSTEMINFO.dbcfile field.
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")
* Props for the RV_SYSTEMINFO.dcxfile field.
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")
* Props for the RV_SYSTEMINFO.dctfile field.
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")
* Props for the RV_SYSTEMINFO.bizfile field.
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")
* Props for the RV_SYSTEMINFO.formfile field.
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")
* Props for the RV_SYSTEMINFO.dataenvfile field.
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")
* Props for the RV_SYSTEMINFO.otherfile field.
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")
* Props for the RV_SYSTEMINFO.generalfile field.
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")
* Props for the RV_SYSTEMINFO.lastupdate field.
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")
*** End of Code
  5. 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)
* Table update will return false.
? TABLEUPDATE(.T.)
  6. If you double-click the remote view in the Database designer, the preceding error message appears.
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

REFERENCES

For additional information on how to set the compatibility mode of the SQL driver, click the article number below to view the article in the Microsoft Knowledge Base:

231812 HOWTO: Programmatically Set the Compatibility Level of the SQL Server ODBC Driver

Modification Type:MajorLast Reviewed:3/17/2005
Keywords:kbCodeSnippet kbprb kbSQLProg KB245714
©2004 Microsoft Corporation. All rights reserved.