hi there

I have a db which was created with ASA5 and later on carried on to ASA8 format. Now, it is even ASA9 format but still with the old codepage.

So, because for future SA versions, I finally did it: I converted it to a new code page following the steps as in http://www.ianywhere.com/developer/t...collation.html.

It worked as fas as the data is concerned

The problem with this is, that reloading choekd on almost every trigger and every stored procedure / function.

Only some made it to the new db. I currently have no idea why dbisql / reload stumbled over the code which IS running in the productive environment.

Anybody any idea?


PS: The unload/reload was done with ASA 9.0.2.3249. The reload prompted me to stop/exit/continue on those errors, I continued because I wanted to see whether the data was then correct.


Thanks,

Martin

In article <MPG.1ebcaf6d57da076b989852 (AT) forums (DOT) sybase.com>, tinu (AT) mindpower (DOT) com says...

More on this:

This is what the reload outputs:

---
Could not execute statement.
Syntax error near '(end of line)' on line 12
SQLCODE=-131, ODBC 3 State="42000"
File: "reload.sql" on line 8485, column 1
CREATE EVENT "Daily Backup" ENABLE AT ALL
HANDLER
begin
declare destination varchar(200);
declare db_filename varchar(40);
declare validate_result long varchar;
declare slash_pos integer;
set destination=db_property('file');
set slash_pos=locate(destination,'\\',-1);
set db_filename="right"(destination,length(destination )-slash_pos);
set destination=replace("left"(destination,slash_pos)+ 'backup','\\','\\');
backup database directory destination transaction log rename wait before sta
rt wait after end with comment 'Daily Backup'
You can continue executing, stop executing, or quit ISQL completely.
1. Stop
2. Continue
3. Exit
Select an option: 2
(Continuing after error)
Could not execute statement.
Variable 'validate_result' not found
SQLCODE=-260, ODBC 3 State="42000"
File: "reload.sql" on line 8497, column 3
select messages into validate_result
from sa_validate(check_type = 'full')
You can continue executing, stop executing, or quit ISQL completely.
1. Stop
2. Continue
3. Exit
Select an option: 2
(Continuing after error)
Could not execute statement.
Syntax error near 'end' on line 6
SQLCODE=-131, ODBC 3 State="42000"
File: "reload.sql" on line 8499, column 3
if validate_result <> 'No error detected' then
set validate_result=string('Verify after backup: Could not validate ',db_f
ilename,'. Errors: ',validate_result);
message validate_result type warning to console;
call Mail_Alert('klicket (AT) klicket (DOT) net','support (AT) klicket (DOT) net','smtp.klicket.
net','Validate after Backup failed',validate_result)
end if
end
You can continue executing, stop executing, or quit ISQL completely.
1. Stop
2. Continue
3. Exit
Select an option: 2
(Continuing after error)
Could not execute statement.
Syntax error near 'BEGIN
declare destination varchar(200);
declare db_filename varchar(40);
declare validate' on line 2
SQLCODE=-131, ODBC 3 State="42000"
File: "reload.sql" on line 8506, column 1
COMMENT TO PRESERVE FORMAT ON EVENT "Daily Backup" IS
{BEGIN
declare destination varchar(200);
declare db_filename varchar(40);
declare validate_result long varchar;
declare slash_pos integer;
set destination = db_property ('file');
set slash_pos = locate (destination, '\', -1 );
set db_filename = right (destination, length(destination) - slash_pos);
set destination = replace (left (destination, slash_pos) + 'backup', '\', '\
\');
backup database directory destination
transaction log rename
wait before start
wait after end
with comment 'Daily Backup'
You can continue executing, stop executing, or quit ISQL completely.
1. Stop
2. Continue
3. Exit
Select an option: 2
---

The script relaod.sql contains those lines beginning with 8485

---
CREATE EVENT "Daily Backup" ENABLE AT ALL
HANDLER
begin
declare destination varchar(200);
declare db_filename varchar(40);
declare validate_result long varchar;
declare slash_pos integer;
set destination=db_property('file');
set slash_pos=locate(destination,'\\',-1);
set db_filename="right"(destination,length(destination )-slash_pos);
set destination=replace("left"(destination,slash_pos)+ 'backup','\\','\\');
backup database directory destination transaction log rename wait before start wait after end with comment 'Daily Backup';
select messages into validate_result
from sa_validate(check_type = 'full');
if validate_result <> 'No error detected' then
set validate_result=string('Verify after backup: Could not validate ',db_filename,'. Errors: ',validate_result);
message validate_result type warning to console;
call Mail_Alert('klicket (AT) klicket (DOT) net','support (AT) klicket (DOT) net','smtp.klicket.net','Validate after Backup failed',validate_result)
end if
end
go
COMMENT TO PRESERVE FORMAT ON EVENT "Daily Backup" IS
{BEGIN
declare destination varchar(200);
declare db_filename varchar(40);
declare validate_result long varchar;
declare slash_pos integer;

set destination = db_property ('file');

set slash_pos = locate (destination, '\', -1 );

set db_filename = right (destination, length(destination) - slash_pos);
set destination = replace (left (destination, slash_pos) + 'backup', '\', '\\');

backup database directory destination
transaction log rename
wait before start
wait after end
with comment 'Daily Backup';

select messages into validate_result
from sa_validate (check_type = 'full');

if validate_result <> 'No error detected' then
set validate_result = string ('Verify after backup: Could not validate ', db_filename, '. Errors: ', validate_result);
message validate_result type warning to console;
call Mail_Alert ('klicket (AT) klicket (DOT) net', 'support (AT) klicket (DOT) net', 'smtp.klicket.net', 'Validate after Backup failed', validate_result);
end if

END
}
go
---

It seems to me that this is a bug ... the event runs fine every day on ASA 9.0.2.3269 ... the backup IS made.

However, why does unload/reload generate a buggy relaod.sql? Note that the unload/reload is done with the ASA 9.0.2.3249 64 Bit developer edition.


Any help / comments appreciated ... I will find a work aournd I hope for those .. but I am curious who / waht causes the problem.

Thanks

Martin

The "with comment" clause is not valid on the "image backup" form of
the BACKUP statement, only the "archive backup" format... at least,
that's what the Help says. Anyway, "with comment" doesn't buy you much
IMO.

Breck

On 29 Apr 2006 22:09:38 -0700, Martin Baur <tinu (AT) mindpower (DOT) com> wrote:

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

In article <98f952ddovp0sm9vrb40lvjohsse8jfra8 (AT) 4ax (DOT) com>, NOSPAM__bcarter (AT) risingroad (DOT) com says...
Well then, how does it come? I have NOT set any comment .. the comment is because formatting is preserved, so then this is a bug in the unload?

Martin

I'm talking about the "BACKUP ... WITH COMMENT" clause, not the
"COMMENT TO PRESERVE FORMAT".

Here is your backup statement from the event...

backup database directory destination transaction log rename wait
before start wait after end with comment 'Daily Backup';

Breck

On 30 Apr 2006 14:49:14 -0700, Martin Baur <tinu (AT) mindpower (DOT) com> wrote:

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

In article <bgsb52llsp004qee224ot6oid4395ruo18 (AT) 4ax (DOT) com>, NOSPAM__bcarter (AT) risingroad (DOT) com says...
Understood and thanks.

still, I did not do anyhting there, so is it a bug or is it a bug?

Martin

I am guessing that the syntax checking got tightened up at some point;
previously, I'll bet the clause was just ignored.

Bug? I think not... I make a syntax error, I get an error message

Breck

On 1 May 2006 18:06:34 -0700, Martin Baur <tinu (AT) mindpower (DOT) com> wrote:

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

The problem seems to be that DBISQL is confused by the WAIT AFTER END
clause. The syntax for the BACKUP statement and the rest of the event is
valid.

As a workaround for now, comment out the WAIT AFTER END clause, create the
event, and then modify it using Sybase Central.

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

"Martin Baur" <tinu (AT) mindpower (DOT) com> wrote