The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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?

asked 14 Feb '14, 12:48

harncw's gravatar image

harncw
106359
accept rate: 25%

edited 01 May '14, 09:02


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.

permanent link

answered 14 Feb '14, 13:28

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%

...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

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;
permanent link

answered 14 Feb '14, 14:03

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%

edited 17 Feb '14, 03:58

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

permanent link

answered 17 Feb '14, 10:05

harncw's gravatar image

harncw
106359
accept rate: 25%

edited 17 Feb '14, 10:12

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:

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.

(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
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:

×34

question asked: 14 Feb '14, 12:48

question was seen: 564 times

last updated: 01 May '14, 09:02