Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

Execute SqlAnywhere 11 Command without Rollback on Windows using the UTILITY_DB

0

I use an application that connects to a SqlAnywhere 11 server/database by issuing the following command (taken from the ConsoleLogFile):

<,3,EXEC_IMM,START DATABASE 'database-file.DBF' AS
"database-name" USING DISTINCT SQLSTATE
Starting database "database-name" (database-file.DBF) at Tue Nov 04 2014 09:42
Database recovery in progress
    Last checkpoint at Mon Sep 29 2014 13:05
    Checkpoint log...
    Transaction log: database-file.DBF.TLG...
Error: Cannot open transaction log file -- The system cannot find the file specified.
Recovery complete
E,3,-106,Cannot open transaction log file -- database-file.DBF.TLG
>.,3
<,3,DISCONNECT
Database "UTILITY_DB" (UTILITY_DB) stopped at Tue Nov 04 2014 09:42
>,3

I'd like to emulate the way the application issues the START DATABASE command but can't figure out how to execute with EXEC_IMM (I'm assuming this is EXECUTE IMMEDIATE?) when logged into the UTILTIY_DB as DBA; I can only seem to execute with PREPARE (e.g., <,3,EXEC_IMM,START DATABASE...).

Most importantly I'd like to prevent a ROLLBACK after the recovery. I tried starting the server and database with the -f command, but it then complains about the database needing recovery.

So, how do I execute unprepared statements and/or prevent rollbacks on errors?

asked 04 Nov '14, 16:36

davidmurdoch's gravatar image

davidmurdoch
1●1●1●2
accept rate: 0%

edited 05 Nov '14, 15:06

What exact problem are you facing - what's the link between START DATABASE and a rollback? Are you trying to start a database and that should not have an impact on the current transaction? (I don't know if it does, at least START DATABASE should not do an auto-commit.)

Just as a general hint: There are several ways to prevent an impact of operations on the current transaction, e.g.:

  • put these operations within a savepoint than can be rollbacked without "disturbing" the surrounding transaction
  • use an event to execute these operations and start that event from the current transaction: as events run on their own connection, they use a separate transaction by design
  • put these operations within a separate transaction (though that necessarily means the current transaction must be committed beforehad unless you use a separate connection).

(05 Nov '14, 03:22) Volker Barth

I asked a similar question related to this issue on StackOverflow: http://stackoverflow.com/questions/26742883/execute-sqlanywhere-11-command-without-rollback-on-windows-using-the-utility-db. I'll update this question with more details in a moment

(05 Nov '14, 15:05) davidmurdoch
One Answer:
active answersoldest answersnewest answerspopular answers
1

If the goal is to prevent a ROLLBACK of uncommitted transactions at the last time the database went down, the short answer is that you can't.

The long answer is that even if you could, you'd have to deal with potential database issues like check constraints and referential integrity (specifically if wait_for_commit is used). However, you may be able to regain some of this data by applying translated SQL for those uncommitted transactions against the database manually.

permanent link

answered 05 Nov '14, 16:05

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k●16●41
accept rate: 22%

1

If you believe you have a big transaction that didn't complete you could always pull out the details from the transaction log using the dbtran utility. See dbtran -s -a to get at the SQL (and then change the rollback to be a commit and resubmit after the database recovers.

(05 Nov '14, 17:58) Nick Elson S...
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

learn more about Markdown

Question tags:

×39
×9

question asked: 04 Nov '14, 16:36

question was seen: 1,902 times

last updated: 05 Nov '14, 17:58

Related questions

SQL Remote Rollback

Store dblgde17.dll and dblgen17.dll in different directory (ADO.NET)

Invalid request on pooled connection

System.EntryPointNotFoundException: Unable to find an entry point named 'AsaConnection_CloseDataRead

SATransaction with using statement

Upgrading from EF4 to EF6

Compound/composite foreign keys not working?

Best way to monitor database availability

ADO.net Connection Failure From Web Server - Works From Development Machine

SA10 ADO.NET driver unicode conversion