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?
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.
answered 14 Feb '14, 13:28
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;
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.