Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    4

    Unanswered: UPDATE OPENQUERY (...) data is invalid

    After running the following OpenQuery...

    UPDATE OPENQUERY(LINKEDSERVER, 'SELECT START_ORDER_NO FROM "OECTLFIL" WHERE "FILE_KEY" = 1')
    SET START_ORDER_NO = 0

    ----The START_ORDER_NO field contains a 48

    SET START_ORDER_NO = 1~9

    ----The START_ORDER_NO field contains 49~57 respectively.

    SET START_ORDER_NO = 10
    ---The START_ORDER_NO field contains 12337
    SET START_ORDER_NO = 11
    ---The START_ORDER_NO field contains 12593
    SET START_ORDER_NO = 12
    ---The START_ORDER_NO field contains 12849

    incrementing by 256 as I increase the value passed...

    ASCII 48-57 are the characters 0-9. The string '10' consists of the two bytes with the values 49 (0x31) and 48 (0x30). It is being viewed in reverse byte order as the value 0x3031 which equals 12337 (48*256 + 49).

    The LinkedServer is Pervasive SQL 2000i using 'OLE DB Provider for ODBC'

    The START_ORDER_NO field is a Numeric(8,0)

    I'm thinking some kind of Unicode, or translation or code page issue, but I haven't had any luck yet.

    I'm not sure how difficult this is, I don't think I'm a neophyte but I'm feeling like one...

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    A few questions:
    So is the field that's being updated in the Pervasive database or the SQL Server database?
    Does the data base pass a Pervasive consistency check?
    If not, what errors are listed?
    What happens if you update the record directly without using the Linked Server?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Sep 2004
    Posts
    4
    The table being updated is a PervasiveSQL table.

    Yes, if I run a consistency check on the database most of the tables pass.

    If I update the table directly using the Query editor within PCC or using a Microsoft Access linked table approach the data is written correctly.

    FYI, I figured out how to do the updates via four-part naming, but I still get the same result when I run the update.

    UPDATE LINKEDSERVER.CATALOG..TABLE SET START_ORDER_NO = 1 WHERE FILE_KEY=1...still writes a 49.

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Does this table pass the consistency check? Update the record using the linked server, and then another record using PCC/Access. Open the data file in the FUnction Executor and see what's actually written in both cases for that field. For the FE, you'll need to know the offset of the field in the table.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •