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


I start the server with dbspawn:

dbspawn dbsrv17 -n Comix_Masked "cims-masked.db" -gf -x tcpip(Port=2640;IPv6=NO) -c 25p -ch40p -ti 360

After the server starts, I open a connection with dbisql and execute:

alter dbspace archive rename 'cims_archive-masked.db'; commit;

The statement works fine. The new name for the dbspace-file can be seen in sysfile system table. If I script dbspawn and dbisql -nogui in a windows batch file:

%SQLANY17%\bin64\dbspawn dbsrv17 -n Comix_Masked "cims-masked.db" -gf -x tcpip(Port=2640;IPv6=NO) -c 25p -ch40p -ti 360 >> renamedb.html %SQLANY17%\bin64\dbisql -port 2640 -c "UID=DBA;PWD=xxxxxxxxx;Server=Comix_Masked" -nogui -onerror exit -q renamedb.sql

I get the error:

Could not execute statement. Not allowed while 'DBA' is using the database SQLCODE=-211, ODBC 3 State="40001"

What I tried so far: 1) I tested it with dbsrv17 or dbeng17. 2) After dbspawn I run a statement to wait 60 seconds 3) I use -gf (to deactivate the triggers) or not use -gf. 4) I tried dbisqlc. 5) Remove the -onerror exist clause.

But nothing helps. I get allways the error message from above. Any ideas how to script it without the error?



asked 02 Dec '22, 08:22

Robert%20Kratschmann's gravatar image

Robert Krats...
accept rate: 0%

Comment Text Removed

I suggest double checking that no other connections exist. In the SQL script, I would add queries from sysdbfile and sa_conn_info(). Here is sql that I used for testing:

parameters new_name; -- supplied arguments to the script

alter dbspace archive rename 'AlterDBS{new_name}.db'; select dbfile_name, file_name from sysdbfile; select Number, UserId from sa_conn_info() order by Number;

I can reproduce the error but only if I have a user with DBA authority connected.

(02 Dec '22, 09:46) Chris Keating

There should be no other connection: I have a production copy tested on my PC. The error message also appears when I use dbeng17 instead of dbsrv17. I ran the script as DBA user.

I've tried an almost empty database with just the dbspace and two dummy tables (one on each dbspace). In this situation, the script works.

The production database contains many triggers and events.

(02 Dec '22, 11:44) Robert Krats...
Replies hidden

So could an event use the dbspace in question – or generally be running while you try to alter the dbspace? (If so, Chris's suggestion will tell...)

(02 Dec '22, 11:56) Volker Barth

Quering sa_conn_info() should show whether other connections are in play. For example, is there an event running?

(02 Dec '22, 12:01) Chris Keating
Replies hidden

I'll test it. Is there a way to deactivate all events like triggers with -gf?

(02 Dec '22, 12:59) Robert Krats...

The database was designed by a person who left the firm. I don't know details of the database. The only thing I can do is exporting the source code into an sql file and see if the events are using tables from the dbspace...

(02 Dec '22, 13:55) Robert Krats...

There is not a command line option to disable events but it would not be difficult to craft a query to generate SQL to disable events and reactivate then once you are done. Have you confirmed that it is an event that is the cause? You can collect the output of sa_conn_info in the script with the ALTER to see what other connections exist. Connection Numbers 1000000000 or higher are connections including internal and event connections.

(02 Dec '22, 15:19) Chris Keating
Comment Text Removed

I found a workaround: 1) Start dbeng17 and deactivate all events via script. 2) Stop dbeng17. 3) Restart dbeng17 execute alter dbspace and activate all events. Only with this workaround it is possible to change the dbspace.

I also checked the output from sa_conn_info: It allways shows only one line. This line is the DBA connection which executes the alter dbspace statement.

(05 Dec '22, 07:57) Robert Krats...
More comments hidden
showing 4 of 8 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 02 Dec '22, 08:22

question was seen: 298 times

last updated: 05 Dec '22, 07:57