Create Event AT Consolidated... My consolidated database thinks it's a remote?

[Former Member]

I'm trying to create an event that should only fire at the consolidated database. For some reason my Consolidated database thinks it is a remote database for the purposes of the "Create Event AT Clause".

CREATE EVENT "DBA"."TestCons" AT CONSOLIDATED
HANDLER
BEGIN
  declare timestamp1 timestamp;
  set timestamp1 = now();
  message 'TestCons Event Fired' || timestamp1 to console;
END;

CREATE EVENT "DBA"."TestRemote" AT REMOTE
HANDLER BEGIN 
  declare timestamp1 timestamp;
  set timestamp1 = now();
  message 'TestRemote Event Fired' || timestamp1 to console;
END;

TRIGGER EVENT "TestRemote";
TRIGGER EVENT "TestCons";

Any thoughts? How does the database determine if it is consolidated or remote... I though it was possible to have a database that was both?

[VolkerBarth]

How does the database determine if it is consolidated or remote... I though it was poddible to have a database that was both?

Yes, in a SQL Remote multi-tier setup a database can be both a remote for its "upper" consolidated and a consolidated for its "lower" remotes.

I guess the following query on SYSREMOTEUSER will tell the role(s) - looking at the type of relationship the remote users are specified with (so a database having only non-consolidated remote users must be a consolidated database itself):

select if consolidate = 'N' then 'is_consolidated' else 'is_remote' end if
   as database_remote_role 
from sysremoteuser
group by consolidate
order by 1;

[Former Member]

My database returned 2 rows! 🙂 thanks for your help.

[Breck_Carter]

TRIGGER EVENT ignores TYPE and EVENT... presumably it ignores AT as well.

In your case, TRIGGER EVENT is the only way the events will fire since there are no other clauses.

[VolkerBarth]

...or the AT clause is only intended for DBXTRACT, to tell whether the CREATE EVENT statement will be added to the reload.sql script for a freshly extracted remote or not...

[Former Member]

In my testing TRIGGER EVENT does NOT appear to ignore the AT clause.

My "consolidated" database had granted consolidate to another.

[Former Member]

I unloaded my "consolidated" database and found:

GRANT CONSOLIDATE TO "CONS" TYPE "FILE" ADDRESS 'CONS';

Therefore my database that was acting as the consolidated was actually set up also as a remote.

I called :

REVOKE CONSOLIDATE FROM "CONS";

Strangely we didn't have a Publisher either

GRANT PUBLISH TO "CONS";

I think this originally got messed with about 6 years ago and had been running as pictured since then. Since our events were not running as we wanted we coded it to check the machine name as work around.

http://globalsoftware-inc.com/harncw/CRM%20Cons%20is%20a%20Remote.jpg

[reimer_pods]

Please be carefull when posting links to subdirs: the website might be hacked, if it's not properly secured. Just using the path from your link may might give access to more details then you intended to grant.

[Former Member]

Thanks for your concern. I am fully aware that my public website folder shows everything, I configured the active server page that does it.

[VolkerBarth]

Note: A recently enough version of SQL Remote 12.0.1 (EBF 3764 and newer) should have warned you when your database has no publisher, cf. this CR note:

If SQL Remote had failed to find a publisher for the database it was connected to, it would have continued to run and could have generated a large number of duplicate messages for a single remote user. This has been corrected so that SQL Remote will now shut down when it determines there is no publisher.

[Former Member]

Although I didn't find anything in our dbremote logs. We do commonly use the "current publisher" special value in our application.

I found that "select current publisher" would return null if there was no publisher and no consolidated user.

GRANT CONSOLIDATE TO "CONS" TYPE "FILE" ADDRESS 'CONS';

OR

GRANT PUBLISH TO "CONS";

We dodged that issue I guess by having a "Grant Consolidate"

In summary it seems someone was last messing with this about 5 years ago in that production system, they are long gone now. I vaguely recall their being an issue from back then but never really got a good understanding of what was going on until now.