I'm trying to migrate an SA9 database to version 11 (maybe I'll change to 12). I did unload at 9 using external options. When I run reload.sql I get "right truncation of string data" in several tables. Ok, that's fine and it is a expected behavior. But I need to solve that setting bigger varchar type for my columns.

My question is: what's the easiest way to know what columns is generation this error?

I'm planning to write some app, that select column size of syscolumns table and compares it with select max(length(column)) but it will be my last shoot.

Thanl you

asked 30 Aug '10, 13:03

Zote's gravatar image

Zote
1.7k364050
accept rate: 43%

edited 20 Sep '11, 17:36

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674

1

So you are saying the data was valid in v9 and is too long in v11? (Otherwise I would not know why a an error should occur.) Wouldn't that imply that the schema is different in both databases, and you just have to compare that?

(30 Aug '10, 13:13) Volker Barth
1

Another quick shot: Have you tried to set option string_rtruncation to off (the v9 default)?

(30 Aug '10, 13:15) Volker Barth

I'm saying that my data/database is valid in v9 and isn't when I try to reload it in v11.

I'm planning to not set string_rtrunction to off since I want to correct my schema. Set this option, in my mind, is a work-around.

(30 Aug '10, 13:36) Zote

@Zote: I think of string_rtruncation as a workaround, too. However, it might help you to find out about the offending values. In my experience, it's wayyy easier to find out which values are longer than expected after they have been (provisorically) imported compared to diagnosing reload errors...

(30 Aug '10, 13:52) Volker Barth
Comment Text Removed

@Zote: I should add that setting the option to off here is only useful when doing data exports from the v9 and the provisorically loaded v11 db and compare which data has changed by the silent truncation. Otherwise, you just won't notice which values have been truncated, and that won't help much:)

(30 Aug '10, 13:56) Volker Barth

@Zote: Another attempt may be useful when only a few tables are affected: Import into a temporary table with same schema, but (much) wider columns and then find out about the offending lines. That's somewhat manual work, I admit:(

(30 Aug '10, 13:59) Volker Barth
2

If the schemas have not changed and the data has not changed then there should be no error. Since you are getting an error I am thinking that perhaps there is an uncorrect character set conversion happening during one of your steps and hence some strings are appearing as longer. Can you confirm that all steps have used the correct character set? BTW: Have not tried doing an upgrade to v11 directly from the v9 database using the v11 s/w?

(30 Aug '10, 17:48) Mark Culp

@Mark, didn't try using direct upgrade. v9 db is using iso_1 as character set encoding and iso1latin1 as collation sequence. v11 is utf-8 and utf8bin. But in my mind, it should not afftect it since I think length should be "number of characters" and not "number of bytes". I'll try again changing charset of v11.

(30 Aug '10, 20:10) Zote
2

I have nothing to add, I just wanted to say that I enjoyed the irony of the "right-truncation-of-strin" tag.

(11 Nov '10, 17:02) Graeme Perrow
More comments hidden
showing 4 of 9 show all flat view

I have change v11 database character set encoding to iso_1 and collation sequence iso1latin1 (same settings that v9 db) and it solves my problem.

It is an expected behavior? (read my reply to Mark in question comments)

permanent link

answered 30 Aug '10, 20:25

Zote's gravatar image

Zote
1.7k364050
accept rate: 43%

3

Yes, this is expected behavior. char(n) and varchar(n) declares character strings which are up to n bytes long. SA 10 introduced CHAR length semantics so you can declare CHAR(10) if you want 10 characters (instead of 10 bytes). Read more about this at http://dcx.sybase.com/index.html#1200en/dbreference/char-character-wsqltype1.html

(30 Aug '10, 22:02) Mark Culp
1

@Mark: I guess it should be "CHAR (10 CHAR)" when using character-length semantics.

(31 Aug '10, 07:24) Volker Barth
1

@Volker: Correct.... my previous comment should have been: "... you can declare CHAR( 10 CHAR ) if you want 10 characters ...".

(31 Aug '10, 13:25) Mark Culp

example: 1> select cast(Number as varchar(10)), 2> cast(PropNum as varchar(10)), 3> cast(PropName as varchar(25)), 4> -- cast(PropDescription as varchar(30)), 5> cast(Value as varchar(25)) 6> from sa_conn_properties() 7> where PropName like '%ncation%' 8> go Number PropNum PropName Value


5 492 string_rtruncation On (1 row affected)

permanent link

answered 26 Aug '13, 23:33

Jun%20Mac's gravatar image

Jun Mac
1
accept rate: 0%

You can try - SET OPTION PUBLIC.ANSI_SUBSTRING = 'Off'; In this case the behavior of the SUBSTRING function is the same as in previous releases of SQL Anywhere

permanent link

answered 28 Aug '13, 03:19

HBrener's gravatar image

HBrener
426212333
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×137
×101
×45
×8

question asked: 30 Aug '10, 13:03

question was seen: 3,896 times

last updated: 28 Aug '13, 03:19