If you try and run a second instance of dbremote.exe while there is already another instance running, the following error is returned on the second instance of dbremote.exe trying to connect "Cannot register 'sybase.asa.dbremote.both' since another exclusive instance is running" (http://dcx.sap.com/index.html#sqla170/en/html/80e71c5d6ce21014a5f0f04dd53b7182.html). What I am trying to determine is whether there is a SQL query or stored system procedure that can be run on the database to determine whether this is the case, without actually starting dbremote.exe to have it fail with this error. I am guessing there must be a property, or similar, set somewhere in a system table that should be queryable, as the database must know that it's running to return the error to dbremote.exe? Alternatively is there a stored system procedure that can check for a running Windows process on the server?

We are running SQLA 17.0.4.2100.

asked 03 Dec, 10:57

robertwigley's gravatar image

robertwigley
26691220
accept rate: 80%

edited 03 Dec, 11:07

Do you run dbremote in continuous or batch mode?

(03 Dec, 11:19) Volker Barth
Replies hidden
Comment Text Removed

Batch mode.

(03 Dec, 11:24) robertwigley
1

I'm not aware of particular properties but dbremote is a database client, so you can check for the according database connections.

(03 Dec, 11:28) Volker Barth

There is no SQL to determine if this error will be returned. Information about which exclusive applications are connected to a given database are stored in memory for the database engine and only accessible via internal calls in the code.

You could implement this yourself using the ConnectionName parameter in the connection string you use when dbremote connects to the database, and then using the sa_conn_info() stored procedure to look for a connection with the name that you used.

Reg

permanent link

answered 03 Dec, 11:32

Reg%20Domaratzki's gravatar image

Reg Domaratzki
5.8k33785
accept rate: 38%

edited 03 Dec, 11:43

I don't know how I didn't think of this before! Thanks everyone. The following SQL seems to do the trick:

select connection_property('AppInfo', sci.number) 
from sa_conn_info() sci
where connection_property('AppInfo', sci.number) like '%dbremote.exe%'
(03 Dec, 11:51) robertwigley
Replies hidden
1

I recognize that I'm being a little picky here, but your query is more restrictive than the code that spawns the "exclusive instance" error. You should also include the database number in the query, since the restriction is for dbremote is per database, not per server.

(03 Dec, 12:06) Reg Domaratzki
Comment Text Removed

Thanks for that. I have just looked at the CON parameter more closely. You are correct, it is simpler to just add e.g. 'con=Replication_UniqueID' to the connection parameters and then check for this with:

select name from sa_conn_info() where name like 'Replication_%';
(03 Dec, 13:56) robertwigley

As Volker mentions you can run dbremote.exe under a specific account name and then create a procedure to see if the account is connected. Alternatively you can use the dbremote hooks to store a record in a work table to track when it is running.

Finally at our sites we run dbremote via an internal database event and use EVENT_PARAMETER( 'NumActive' ) to see if it's already active.

CREATE EVENT DBA.EventDatabaseReplication
SCHEDULE EventDatabaseReplicationSchedule BETWEEN '02:00' AND '23:00' EVERY 1 HOURS
DISABLE AT REMOTE
HANDLER
BEGIN
  DECLARE @EventName        CHAR(128);
  DECLARE @ActiveInstances  INTEGER;
  DECLARE @ExecutionNumber  INTEGER;

  SET @EventName        = EVENT_PARAMETER( 'EventName' );
  SET @ActiveInstances  = EVENT_PARAMETER( 'NumActive' );
  SET @ExecutionNumber  = EVENT_PARAMETER( 'Executions' );

  CALL DBA.DatabaseReplicationEvent( @EventName, @ActiveInstances, @ExecutionNumber );
END
GO
permanent link

answered 03 Dec, 11:43

J%20Diaz's gravatar image

J Diaz
875263145
accept rate: 13%

edited 03 Dec, 12:07

Reg%20Domaratzki's gravatar image

Reg Domaratzki
5.8k33785

More good suggestions.

(03 Dec, 12:07) Reg Domaratzki
Replies hidden

Yes, thanks to everyone for this. All extremely helpful information.

(03 Dec, 13:53) robertwigley

Here's another attempt as you are using v17: You could also use a dbremote hook procedure like sp_hook_dbremote_begin to set the status of a database-scoped variable and then query that from your code before you start another dbremote instance...or lock a mutex within the hook and unlock it afterwards...

That would be independent of particular connection or user names...

permanent link

answered 03 Dec, 14:02

Volker%20Barth's gravatar image

Volker Barth
32.6k328476696
accept rate: 32%

edited 04 Dec, 03:52

Thanks. I am actually surprised at how many different ways there are to do this! I was struggling to find just one!

(03 Dec, 15:20) robertwigley
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:

×114
×84
×51

question asked: 03 Dec, 10:57

question was seen: 277 times

last updated: 04 Dec, 03:52