ASA 9.0.2.3527

I wrote a script which opened a passthrough session, created a stored
procedure for DBA and then created an event, with schedule, to call
that stored procedure. The event syntax follows:

----------
create event checkFileSize
schedule checkFileSizeSchedule
between '22:05:00' and '22:15:00'
every 24 hours
handler
begin
call dba.sp_file_size();
end;
----------

I ran the script on the consolidated database and both the stored
procedure and the event replicated through to the two subscribed
databases.

The stored procedure sp_file_size writes rows to a log table and
includes the results of: select db_property('name')

However, I am finding that only the consolidated database is creating
log records. What I intended was for all three databases to call
dba.sp_file_size on a nightly basis, and to log their results.

Can anyone please show me the error of my ways?

Thanks in advance,

Chris.

Check for an error from the event in the server console on the remote. Also
try triggering the event manually on the remote using "trigger event
checkFileSize". There might be a problem with the event not having
permission to call the procedure. Check SYS.SYSEVENT on the remote to see
which user created the event; its owner might be a user with REMOTE DBA
authority. If that's the case, when the event runs it will not have DBA
authority and will need explicit permissions to call the procedure.

Whitepapers, TechDocs, bug fixes at http://www.ianywhere.com/developer

"youcantryreachingme" <youcantryreachingme (AT) gmail (DOT) com> wrote

Thanks Bruce,

permissions fixed it. You live, you learn

I did find something interesting in the process though. I ran:

-----
select * from sysevent
join sysuserperm on sysevent.creator = sysuserperm.user_name
-----

and got an error

Cannot convert SYS to a numeric(30,6)

but run:

-----
select * from sysevent
join sysuserperm
-----

and there was no problem.

Anyway, that's just an aside. Thanks again.

Chris.