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


Joe Webb
SQL Server MVP

Get up to speed quickly with SQLNS 

I support PASS, the Professional Association for SQL Server.

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

>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
>This is the script (Very simple script, Is a Sample script from a SQL
>2005 Book)
>-- Notification Services
>drop table tblAudit
>create table dbo.tblAudit
>       (eventXMLData XML null,
>       time datetime null
>       );
>select * from tblAudit (nolock)
>if object_id('dbo.sp_Audit') is not null
>       drop procedure dbo.sp_Audit;
>create procedure dbo.sp_Audit
>       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
>if exists(select * from where name = 'AuditService')
>       drop service AuditService;
>if object_id('dbo.AuditQueue') is not null and
>        exists(select * from sys.service_queues where name = 'AuditQueue')
>       drop queue dbo.AuditQueue;
>create queue dbo.AuditQueue
>       with status = ON,
>       activation (
>               procedure_name = TEST.dbo.sp_audit,
>               MAX_QUEUE_READERS = 1,
>               execute as self )
>       on [default] ;
>create service
>       AuditService on queue AuditQueue
> ([]);
>drop event notification ENDBAudit
>on server
>create event notification ENDBAudit
>on server
>for create_database,
>to service 'AuditService', 'current database';
>create table temp01
>       (
>                Sequence_Number int identity not null
>               ,Port_id int not null
>       )
>create  procedure dbo.sp_8
>drop procedure [dbo].[sp_8]
>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 ?

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