SQL Anywhere database - How do I unload/reload to change collation?

Author

How do I unload/reload to change collation?

Breck Carter

2005-04-18, 9:23 am

How do I unload/reload to change collation?

I have an ordinary ASA 9.0.2.3044 database running on
Windows. I have created a second database using the dbinit
-z UTF8 option.

What options should I use on the dbunload and/or the dbisql
reload steps to ensure the load goes smoothly?

Currently, I am getting this error:

-- Could not execute statement.
-- Cannot convert UNITED STATES to a date. The bad value
was supplied for
-- column 'DATEOFBIRTH' on line 2096 of the data file.
-- SQLCODE=3d-162, ODBC 3 State=3d"07006"
-- File: "reload.sql" on line 33082, column 1
Could not execute statement.
Cannot convert UNITED STATES to a date. The bad value was
supplied for
column 'DATEOFBIRTH' on line 2096 of the data file.
SQLCODE=3d-162, ODBC 3 State=3d"07006"
File: "reload.sql" on line 33082, column 1
LOAD TABLE "xxx"."yyy" ("ACCOUNTNUMBER",
" INDIVIDUALCOMPANYFLA

, "FIRSTNAME", "MIDDLENAME", "LASTNAME", "DATEOFBIRTH",
"ADDRESS", "COUNTRY",
TATE", "CITY", "ZIPCODE", "EMAILADDRESS", "STATINGDATE",
"OPSERVICE", "ORSERVI
", "EXCHANGESERVICE", "SOCIALSECURITYNUM", "DRIVERLICENNUM",
"PASSPORTNUM", "A
IVE", "LINEOFBUSSINESS", "EXCHANGEDAILYVOLUME",
"SALESMENCODE", "REGIONCODE",
ENDER", "OBSERVATION", "INACTIVEPARAMETER",
"PREFERREDLANGUAGE", "LASTTRANSACT
NDATE", "FAXBROAD", "VENDORCODE", "SOURCEREF",
"CREATEDDATIME", "UPDATETIME",
niquekey", "paspexpdate", "drivlicexpdate", "doingbusas",
"mail", "ss_tin_cgc"
"tradeservice" )
FROM 'd:\\xxx\\reload\\44
7.dat'
FORMAT 'ASCII' QUOTES ON
ORDER OFF ESCAPES ON
CHECK CONSTRAINTS OFF COMPUTES OFF
STRIP OFF DELIMITED BY ','
You can continue executing, stop executing, or quit ISQL
completely.
1. Stop
2. Continue
3. Exit

The bad line has an accented-A in 'DE SA' that appears to
cause the closing quote to be skipped, thus causing 'UNITED
STATES' to be treated as the 6th column rather than the 8th:

=3d=3d=3d=3d=3d
5974,'I','CELSO LIMA','','DE S=c1',1944-04-03 00:00,'1801
COLLINS AVE #941','UNITED STATES','FL','MIAMI
BEACH','33139','',19
95-12-20
00:00,'Y','N','N','5
92493385','','','Y',
'
',0,4,5,'M','B/D
BARNETT\x0d\x0a',0,'
PORTUGUES',1997-08-28
00:00,'Y','UNO1','UN
O',,2001-02-07
16:52:46. 812000,1265,,,,'N','
592493385',
=3d=3d=3d=3d=3d

Breck

Bruce Hay

2005-04-18, 9:23 am

The procedure was described in the January 2005 issue of the iAnywhere
Solutions Developer Community Technical Newsletter. See:
http://www.sybase.com/detail?id=1034325

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

<Breck Carter> wrote in message news:4263b73b.19b6.1681692777@sybase.com...
How do I unload/reload to change collation?

I have an ordinary ASA 9.0.2.3044 database running on
Windows. I have created a second database using the dbinit
-z UTF8 option.

What options should I use on the dbunload and/or the dbisql
reload steps to ensure the load goes smoothly?

Currently, I am getting this error:

-- Could not execute statement.
-- Cannot convert UNITED STATES to a date. The bad value
was supplied for
-- column 'DATEOFBIRTH' on line 2096 of the data file.
-- SQLCODE=-162, ODBC 3 State="07006"
-- File: "reload.sql" on line 33082, column 1
Could not execute statement.
Cannot convert UNITED STATES to a date. The bad value was
supplied for
column 'DATEOFBIRTH' on line 2096 of the data file.
SQLCODE=-162, ODBC 3 State="07006"
File: "reload.sql" on line 33082, column 1
LOAD TABLE "xxx"."yyy" ("ACCOUNTNUMBER",
" INDIVIDUALCOMPANYFLA

, "FIRSTNAME", "MIDDLENAME", "LASTNAME", "DATEOFBIRTH",
"ADDRESS", "COUNTRY",
TATE", "CITY", "ZIPCODE", "EMAILADDRESS", "STATINGDATE",
"OPSERVICE", "ORSERVI
", "EXCHANGESERVICE", "SOCIALSECURITYNUM", "DRIVERLICENNUM",
"PASSPORTNUM", "A
IVE", "LINEOFBUSSINESS", "EXCHANGEDAILYVOLUME",
"SALESMENCODE", "REGIONCODE",
ENDER", "OBSERVATION", "INACTIVEPARAMETER",
"PREFERREDLANGUAGE", "LASTTRANSACT
NDATE", "FAXBROAD", "VENDORCODE", "SOURCEREF",
"CREATEDDATIME", "UPDATETIME",
niquekey", "paspexpdate", "drivlicexpdate", "doingbusas",
"mail", "ss_tin_cgc"
"tradeservice" )
FROM 'd:\\xxx\\reload\\44
7.dat'
FORMAT 'ASCII' QUOTES ON
ORDER OFF ESCAPES ON
CHECK CONSTRAINTS OFF COMPUTES OFF
STRIP OFF DELIMITED BY ','
You can continue executing, stop executing, or quit ISQL
completely.
1. Stop
2. Continue
3. Exit

The bad line has an accented-A in 'DE SA' that appears to
cause the closing quote to be skipped, thus causing 'UNITED
STATES' to be treated as the 6th column rather than the 8th:

=====
5974,'I','CELSO LIMA','','DE SÁ',1944-04-03 00:00,'1801
COLLINS AVE #941','UNITED STATES','FL','MIAMI
BEACH','33139','',19
95-12-20
00:00,'Y','N','N','5
92493385','','','Y',
'
',0,4,5,'M','B/D
BARNETT\x0d\x0a',0,'
PORTUGUES',1997-08-28
00:00,'Y','UNO1','UN
O',,2001-02-07
16:52:46. 812000,1265,,,,'N','
592493385',
=====

Breck

Sponsored Links