|
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. ...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...
(14 Feb '14, 14:06)
Volker Barth
In my testing TRIGGER EVENT does NOT appear to ignore the AT clause. My "consolidated" database had granted consolidate to another.
(17 Feb '14, 10:07)
harncw
|
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; My database returned 2 rows! :) thanks for your help.
(17 Feb '14, 10:10)
harncw
|
|
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 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.
(17 Feb '14, 10:30)
Reimer Pods
Replies hidden
Thanks for your concern. I am fully aware that my public website folder shows everything, I configured the active server page that does it.
(17 Feb '14, 10:44)
harncw
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:
(17 Feb '14, 12:32)
Volker Barth
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.
(17 Feb '14, 13:00)
harncw
|
