|
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. |
|
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 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 '18, 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 '18, 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 '18, 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 More good suggestions.
(03 Dec '18, 12:07)
Reg Domaratzki
Replies hidden
Yes, thanks to everyone for this. All extremely helpful information.
(03 Dec '18, 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... Thanks. I am actually surprised at how many different ways there are to do this! I was struggling to find just one!
(03 Dec '18, 15:20)
robertwigley
|
Do you run dbremote in continuous or batch mode?
Batch mode.
I'm not aware of particular properties but dbremote is a database client, so you can check for the according database connections.