DOCUMENT:Q245714  27-JUL-2001  [foxpro]
TITLE   :PRB: Inserting > than 250K of Data in a SQL 7 Image Field Fails
PRODUCT :Microsoft FoxPro
PROD/VER:WINDOWS:2.5,6.0
OPER/SYS:
KEYWORDS:kbSQL kbvfp600 KbDBFDBC kbGrpDSFox kbDSupport kbMDAC250 kbCodeSnippet kbSQLProg

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, version 6.0 
 - Microsoft Data Access Components version 2.5 
-------------------------------------------------------------------------------

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.

   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

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:

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

Additional query words: KBDSE

======================================================================
Keywords          : kbSQL kbvfp600 KbDBFDBC kbGrpDSFox kbDSupport kbMDAC250 kbCodeSnippet kbSQLProg 
Technology        : kbVFPsearch kbAudDeveloper kbMDACSearch kbMDAC250 kbVFP600
Version           : WINDOWS:2.5,6.0
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.