UPDATE OPENQUERY (LINKEDSERVER, 'SELECT...) SET FIELD = #

The values in your set statements are being treated as strings, converted directly to binary with no translation:

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

Of course, how to fix it...

Have you tried removing and readding the linked server?
Have you tried using a convert in your set statement? SET START_ORDER_NO = Convert(Numeric(8,0),0)
Are the two versions of SQL Server the same?
Have you applied all the service packs available?
What collation settings are on the column/table/database/server that you are updating, and what is the default collation setting for the server you're running the query on?

What's the DBMS on the remote (linked) server ?

Have you tried removing and readding the linked server?
---Yes numerous times

Have you tried using a convert in your set statement? SET START_ORDER_NO = Convert(Numeric(8,0),0)
---Yes that exact Convert statement, plus some CAST statements

Are the two versions of SQL Server the same?
---The linked server is Pervasive SQL 2000i, connecting using OLE DB Provider for ODBC and a System DSN

Have you applied all the service packs available?
---Yes Service Pack 3 on SQLServer and Service Pack 4 on Pervasive SQL

What collation settings are on the column/table/database/server that you are updating, and what is the default collation setting for the server you're running the query on?
---The default collation setting on SQLServer is SQL_Latin1_General_CP1_CI_AS
---PervasiveSQL doesn't seem to have a straight forward 'collation setting', but I have found this informationon their website

---International Sort Rules
---To specify an ACS that sorts string values using an ISO-defined, language-specific collating sequence, you must specify an ISR table name, as follows:  

437 MS-DOS Latin-US uses ISR table name PVSW_ENUS00437_0
850 MS-DOS Latin-1 uses ISR table name PVSW_ENUS00850_0

However, I haven't been able to to figure out what table it is using, or how to change it. There is a reference under table properties that shows that an ACS (Alternate Collating Sequence) is not being used.

the DBMS is Pervasive SQL 2000i

Don't know if this helps, but if I created a linked table within Access 2000 using the same ODBC DSN, any updates I make are correct with none of this odd behavior.

--- The START_ORDER_NO field contains 49~57 respectively

1 how exactly did you observe the fields contanes values above. Is it possible that the values stored correctly but displaied incorrectly.
2 'OLE DB Provider for ODBC' is it supported configuration, did you try to search the FAQ / start a ticket with the vendor of SQL Server
3 did you try to look for updated ODBC driver for Pervasive.

-- cheers

I observed the values by using the Pervasive Control Center, I confirmed the values doing a Select * from OpenQuery(LINKEDSERVER, 'Select * from "OECTLFIL") as well

It is a supported (the only supported way to create a Pervasive SQL 2000i Linked Server within MSSQL 2000, and Pervasive SQL 2000i is no longer supported by the Vendor. Nice of them huh.

I have the latest ODBC Driver for Pervasive

Have you tried doing an update with four-part naming?

UPDATE LINKEDSERVER.database.owner.OECTLFIL
   SET START_ORDER_NO=1
   WHERE FILE_KEY = 1

Yes, I tried that first because the Pervasive documentation waffles back and forth on whether or not it supports four-part naming.

I did it again just so I could get you the error message.

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].

I figured out how to make the four-part naming to workm thereby elminating the need for the OpenQuery approach but unfortunately with the same result.

It has something to do with the way the SQLServer is mapping and/or transforming the data through the Linked Server approach.

Maybe it's a mismatch in how the two servers handle the numeric data type. Have you experimented with integer data types?

did you try this

declare @i int
select   @i = 0

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

-- cheers

I don't have the option of changed the data type. Pervasive SQL is part of a proprietary ERP system. I can simply work with what I have.

declare @i int
select @i = 0
set start_order_no=@i achieves the same end result...start_order_no = 48

declare @i int
select @i = 0
set start_order_no=@i - ASCII( @i )

---

declare @i int
select @i = 0
set start_order_no=@i - ASCII( RTRIM( LTRIM( STR( @i ) ) ) )

nice try but that doesnt work

that equates to

0 - Ascii(0) or 0 minus 48 so -48 which becomes 3683373

miron,
same result for both of your suggestions

well, lets see

what is the value you see in the pervasive after

1

declare @i int
select @i = 0
set start_order_no=@i

2

declare @i int
select @i = 0
set start_order_no= ( @i - ASCII( '0' ) )

and

3

declare @i int
select @i = 0
set start_order_no= ( @i - @i )

Scenario 1

start_order_no = 48

Scenario 2

start_order_no =3683373

Scenario 3

start_order_no = 48

Me either, and I understand that. Just being accomodating. Since their trying to help.

I need to figure out a way to determine how SQL Server is mapping the table.

I have run..

EXEC sp_tables_ex
   @table_server='LINKEDSERVER'

and

EXEC sp_columns_ex
   @table_server='LINKEDSERVER',
   @table_name='OECTLFIL'

thinking they would provide some insight.

sp_tables returned...
PSQLOBRIANERP1      NULL      OECTLFIL      TABLE      NULL
not much help

and
sp_columns returned an empty grid (column names only)

and the result of assignment

( z <= '0' ) == ( z <= @i )


-- cheers

The question should have been closed with no answer. The points should have been awarded to no one. No one gave any insight that solved the problem. The 'linked server' approach had to be abandoned. Sorry I didn't respond earlier I was in Kenya ...