microsoft.public.sqlserver.notificationsvcs
[Top] [All Lists]

Re: SQL 2005 NS Problem. Need Help

Subject: Re: SQL 2005 NS Problem. Need Help
From: Joe Webb
Date: Fri, 05 May 2006 15:09:12 -0500
Newsgroups: microsoft.public.sqlserver.notificationsvcs
This looks to be more of a Service Broker question than a Notification
Services question. You'll likely have more luck getting a helpful
response in another newsgroup.

Cheers!

-- 
Joe Webb
SQL Server MVP
http://www.sqlns.com


~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)


On 26 Apr 2006 10:06:23 -0700, [email protected]
wrote:

>Hi everybody
>
>I have a problem with my SQL 2005 NS
>
>I have a script that insert XML values on a table when a database event
>happens (create, alter and drop tables, procedures and views). I have
>created a new empty DB called TEST, I ran the script against this DB
>and it works OK, it inserts the XML into the table.
>
>The problem starts when I try to use the same script against my main
>database. The script ran OK, but the table in where I have to find the
>new XML rows is always empty. The main and the test DBs are on the same
>server
>
>This is the script (Very simple script, Is a Sample script from a SQL
>2005 Book)
>
>-- Notification Services
>Use TEST
>drop table tblAudit
>go
>create table dbo.tblAudit
>       (eventXMLData XML null,
>       time datetime null
>       );
>go
>select * from tblAudit (nolock)
>go
>if object_id('dbo.sp_Audit') is not null
>begin
>       drop procedure dbo.sp_Audit;
>end
>go
>create procedure dbo.sp_Audit
>as
>begin
>       declare @eventDataXML varbinary(max);
>       receive top(1) @eventDataXML=message_body
>        from TEST.dbo.AuditQueue
>
>       if cast(@eventDataXML as XML) is not null
>       begin
>               insert into dbo.tblAudit (eventXMLData, time)
>                       values(cast(@eventDataXML as XML), getdate());
>       end
>end
>go
>if exists(select * from sys.services where name = 'AuditService')
>       drop service AuditService;
>go
>if object_id('dbo.AuditQueue') is not null and
>        exists(select * from sys.service_queues where name = 'AuditQueue')
>       drop queue dbo.AuditQueue;
>go
>create queue dbo.AuditQueue
>       with status = ON,
>       activation (
>               procedure_name = TEST.dbo.sp_audit,
>               MAX_QUEUE_READERS = 1,
>               execute as self )
>       on [default] ;
>go
>create service
>       AuditService on queue AuditQueue
>       
> ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
>go
>drop event notification ENDBAudit
>on server
>go
>create event notification ENDBAudit
>on server
>for create_database,
>drop_database,
>create_table,
>drop_table,
>create_procedure,
>drop_procedure,
>create_function,
>drop_function
>to service 'AuditService', 'current database';
>go
>create table temp01
>       (
>                Sequence_Number int identity not null
>               ,Port_id int not null
>       )
>go
>create  procedure dbo.sp_8
>as
>go
>drop procedure [dbo].[sp_8]
>go
>
>When I try to select from my output table
>
>select * from tblAudit (nolock)
>
>I have 3 rows (Everithing work OK)
>
>But whe I run it against my main, the output table has no rows
>
>Any Idea or Suggestion ?
>Thanks

<Prev in Thread] Current Thread [Next in Thread>
  • Re: SQL 2005 NS Problem. Need Help, Joe Webb <=