Solved

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

Posted on 2004-09-22
27
4,821 Views
Last Modified: 2008-01-09
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
Comment
Question by:revstudio
  • 11
  • 8
  • 4
  • +1
27 Comments
 
LVL 1

Expert Comment

by:E-Squared
ID: 12128038
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12130622
What's the DBMS on the remote (linked) server ?
0
 

Author Comment

by:revstudio
ID: 12133436
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
 

Author Comment

by:revstudio
ID: 12134612
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
 
LVL 9

Expert Comment

by:miron
ID: 12139727
--- 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
 

Author Comment

by:revstudio
ID: 12139870
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
 
LVL 1

Expert Comment

by:E-Squared
ID: 12140083
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
 

Author Comment

by:revstudio
ID: 12142681
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
 

Author Comment

by:revstudio
ID: 12151149
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
 
LVL 1

Expert Comment

by:E-Squared
ID: 12151237
Maybe it's a mismatch in how the two servers handle the numeric data type. Have you experimented with integer data types?
0
 
LVL 9

Expert Comment

by:miron
ID: 12151264
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
 

Author Comment

by:revstudio
ID: 12159763
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 9

Expert Comment

by:miron
ID: 12161056
declare @i int
select @i = 0
set start_order_no=@i - ASCII( @i )

0
 
LVL 9

Expert Comment

by:miron
ID: 12161103
---

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

Author Comment

by:revstudio
ID: 12161107
nice try but that doesnt work

that equates to

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

Author Comment

by:revstudio
ID: 12161159
miron,
same result for both of your suggestions
0
 
LVL 9

Expert Comment

by:miron
ID: 12161249
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
 
LVL 9

Expert Comment

by:miron
ID: 12161320
and

3

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

Author Comment

by:revstudio
ID: 12161398
Scenario 1

start_order_no = 48

Scenario 2

start_order_no =3683373

Scenario 3

start_order_no = 48
0
 
LVL 1

Accepted Solution

by:
E-Squared earned 250 total points
ID: 12161563
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
 

Author Comment

by:revstudio
ID: 12161616
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
 
LVL 9

Assisted Solution

by:miron
miron earned 250 total points
ID: 12162014
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
 
LVL 9

Expert Comment

by:miron
ID: 12162347
and the result of assignment

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


-- cheers
0
 

Author Comment

by:revstudio
ID: 12443225
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now