PRB: Inserting > than 250K of Data in a SQL 7 Image Field Fails

ID: Q245714

The information in this article applies to:
  • Microsoft Visual FoxPro for Windows, version 6.0

SYMPTOMS

Inserting or replacing more than approximately 250K of data in an image field in a SQL 7.0 table, using a remote view, causes the tableupdate to fail. The following error message appears 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 7.0 database and name it SQL7INSPROB.


  2. Use the following code to create a table in a SQL 7.0 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.


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


    4. 
CREATE DATABASE MyDatabase
OPEN DATABASE MyDatabase
MODIFY DATABASE
  4. Copy the following code into a program. Run the program to create a remote view in Visual FoxPro:


    5. 
** 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:


    6. 
*** 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:

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

Additional query words: KBDSE

Keywords : kbSQL kbVFp600 KbDBFDBC kbGrpFox kbDSupport kbCodeSnippet
Version : WINDOWS:6.0
Platform : WINDOWS
Issue type : kbprb


Last Reviewed: November 24, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.