Service Broker and SQL Server Express

[Joseph I. Ceasar]

Is it possible to use Service Broker with SQL Server Express?

How?

What about in a network where the only database server is the Express
edition?

--
-------------------------------
Joseph I. Ceasar
CLS Computer Solutions

[Remus Rusanu [MSFT]]

Yes. If using Service Broker locally, whithin the instance, there are no
restrictions at all. If you want Service Broker to communicate between SQL
instances, then the message has to pass through at least one non-Express
instance. That is, SQL Express can send messages to non-Express edition and
viceversa, but in order to send messages from a SQL Express instance to
another SQL Express instance, the message has to be forwarded by one
non-Express instance (see Service Broker Message Forwarding at
http://msdn2.microsoft.com/en-us/library/ms166098.aspx )

BTW, note that Service Broker has nothing to do with the Notification
Services feature (since you posted in this NG)

--
This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx

"Joseph I. Ceasar" <j...@pipeline.com> wrote in message
news:ujEZdzV6...@TK2MSFTNGP10.phx.gbl...

[Joseph I. Ceasar]

This is where I get confused. I thought that NS was part of Service Broker.

What I am trying to achieve is to have the server notify a client app when a
particular table has been modified. Would be nice to know which record was
modified as well. So what should I be using?

My plan it to roll out the application using SQL Server Express as the
database server. It will be a limited-features application. Once the users
upgrade to the full app, the upgrade price will include a "real" version of
SQL Server.

"Remus Rusanu [MSFT]" <Remus.Rus...@microsoft.com.nowhere.moon> wrote
in message news:Ogsp$zW6FH...@TK2MSFTNGP14.phx.gbl...

[Remus Rusanu [MSFT]]

Query Notifications are implemented using Service Broker, indeed. But
Notification Services is a different feature altogether.
NS is for mass distributing real notifications (email, SMS, IM), and is an
external feature, also available in SQL 2000.
Service Broker is for writing distributed apps, is internal (inside SQL
Server engine itself) and is oonly available in SQL 2005.

For your problem, I can recommend two approaches. An easy way would be to
use the new Query Notifications and SqlDependency. These new features were
designed exactly to address your problem.
http://msdn2.microsoft.com/en-us/library/t9x04ed2.aspx

A second approach, if you must know the row changed, is to use directly
Service Broker and send a message from a trigger.
Here is a simple demo on how to do it:

use master
go

if exists(select * from sys.databases where name = 'demoAsyncTrigger')
begin
drop database [demoAsyncTrigger];
end
go

create database demoAsyncTrigger;
go

use demoAsyncTrigger;

create message type [DML_Notification] validation = well_formed_xml;
create contract [DML_Notification] ([DML_Notification] sent by initiator);
create queue [DML_Notification];
create service [DML_Notification] on queue [DML_Notification]
([DML_Notification]);
go

create queue [DML_Notification_Sender];
create service [DML_Notification_Sender] on queue [DML_Notification_Sender];
go

create table [DML_Notification_Subscriptions]
(TABLE_NAME sysname primary key,
DIALOG_HANDLE uniqueidentifier);
go

create procedure sp_send_dml_update (
@table_name sysname,
@operation nvarchar(10),
@key sql_variant)
as
begin
declare @dialog_handle uniqueidentifier;
begin transaction;
select @dialog_handle = DIALOG_HANDLE
from [DML_Notification_Subscriptions]
where TABLE_NAME = @table_name;
if (@dialog_handle is NULL)
begin
begin dialog conversation @dialog_handle
from service [DML_Notification_Sender]
to service 'DML_Notification'
on contract [DML_Notification]
with encryption = off;
insert into [DML_Notification_Subscriptions]
values (@table_name, @dialog_handle);
end
declare @message_body xml;
select @message_body = (
select @table_name as [@table_name],
@operation as [@operation],
@key as [@key]
for xml path ('DML_Notification'));
send on conversation @dialog_handle
message type [DML_Notification]
(@message_body);
commit;
end
go

create table foo (a int);
go

create trigger foo_dml_notification_insert
on foo
for insert
as
begin
declare @a int;
select @a = a from inserted;
exec sp_send_dml_update N'foo', N'insert', @a;
end
go

create trigger foo_dml_notification_update
on foo
for update
as
begin
declare @a int;
select @a = a from deleted;
exec sp_send_dml_update N'foo', N'update', @a;
end
go

insert into foo values (1);
insert into foo values (2);
insert into foo values (3);
go

update foo
set a = 10
where a= 1;
go

begin transaction
insert into foo values (6);
insert into foo values (7);
rollback;
go

receive cast(message_body as xml), * from [DML_Notification];

--
This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx

"Joseph I. Ceasar" <j...@pipeline.com> wrote in message

news:%23Yk643W...@TK2MSFTNGP12.phx.gbl...

[Joseph I. Ceasar]

This is good!

I assume that the restrictions that you mentioned earlier about Service
Broker and the Express edition apply as well to Query Notifications.

"

[Remus Rusanu [MSFT]]

Yeap. If you want the notification to be delivered to a remote SQL instance,
you must have a non-Express somewhere in the delivery path.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx

"Joseph I. Ceasar" <j...@pipeline.com> wrote in message

news:%23BinKQY...@TK2MSFTNGP09.phx.gbl...