Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4880
  • Last Modified:

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

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

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...
0
revstudio
Asked:
revstudio
  • 11
  • 8
  • 4
  • +1
2 Solutions
 
E-SquaredCommented:
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?

0
 
HilaireCommented:
What's the DBMS on the remote (linked) server ?
0
 
revstudioAuthor Commented:
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





0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
revstudioAuthor Commented:
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.
0
 
mironCommented:
--- 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
0
 
revstudioAuthor Commented:
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
0
 
E-SquaredCommented:
Have you tried doing an update with four-part naming?

UPDATE LINKEDSERVER.database.owner.OECTLFIL
   SET START_ORDER_NO=1
   WHERE FILE_KEY = 1
0
 
revstudioAuthor Commented:
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].
0
 
revstudioAuthor Commented:
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.
0
 
E-SquaredCommented:
Maybe it's a mismatch in how the two servers handle the numeric data type. Have you experimented with integer data types?
0
 
mironCommented:
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
0
 
revstudioAuthor Commented:
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
0
 
mironCommented:
declare @i int
select @i = 0
set start_order_no=@i - ASCII( @i )

0
 
mironCommented:
---

declare @i int
select @i = 0
set start_order_no=@i - ASCII( RTRIM( LTRIM( STR( @i ) ) ) )
0
 
revstudioAuthor Commented:
nice try but that doesnt work

that equates to

0 - Ascii(0) or 0 minus 48 so -48 which becomes 3683373
0
 
revstudioAuthor Commented:
miron,
same result for both of your suggestions
0
 
mironCommented:
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' ) )
0
 
mironCommented:
and

3

declare @i int
select @i = 0
set start_order_no= ( @i - @i )
0
 
revstudioAuthor Commented:
Scenario 1

start_order_no = 48

Scenario 2

start_order_no =3683373

Scenario 3

start_order_no = 48
0
 
E-SquaredCommented:
You can't just subtract the ascii value of the character zero because when the value is two bytes there's an addition of 256 times a number, as well.

In any case, even if you could make that sort of work, I wouldn't trust it.
0
 
revstudioAuthor Commented:
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)



0
 
mironCommented:
so,

you are suggesting that when we have variable @i of type integer assigned 0

and the result of assigmnet z <= @i

is ASCII( '0' )

the results of

( @i - @i ) != ( @i - ASCII( '0' ) )


-- cheers
0
 
mironCommented:
and the result of assignment

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


-- cheers
0
 
revstudioAuthor Commented:
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 ...
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 11
  • 8
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now