Recieving E-Mail by Service broker

I'm beginner...

I need recieve e-mail by Service broker.

What can I do it?

(example if possible).

Thanks!

Service Broker provides reliable messaging in and between databases and does not interface with other systems directly (such as e-mail). You could, however, write a Service Broker service that receives e-mail from IMAP, POP3 or any other e-mail protocol and send them to another Service Broker service. Use Books Online to get started on how to use the technology.

Rushi

|||

Thanks!

In some book I read about this.

Probably it isn't true.

|||i'm with you it's true but faile to make it if you found any solve tell me please

REceiving notification in windows application

Hi,

Is this possible to receive notifications from Notification Service in my windows application written in Framework 1.1? I am using SqlServer 2K.

I want to create a notification when number of rows in a given table reach a specific limit. I want to receive the notification in my application and prompt user through my application's user-interface instead of sending sms or emails to users.

Thanks,

You can do this by creating your own custom delivery protocol.

HTH...

|||Any sample ?|||

Why not just use a MessageBox ?

You could simply write a SqlCommand counting the number of rows already in your databasetable...

|||Its a little complicated and then I need to do this very frequently and speed is an important factor here as well.|||

I don't have one off hand that I can post. Shyam Pather's book describes how to build one though. It's definitely worth the purchase price.

HTH...

RECEIVING messages from a Windows service

I've done a bit of work with the External Activator but I think it may be a bit overkill for what I need to do (which is RECEIVE messages from a single queue and process them with managed code). I've tried creating a Service Broker Interface service that retrieves messages from this queue, but I notice that if I set the timeout to -1 to watch for messages indefinitely, the Service never completes the OnStart code.

I notice if I change the service's timeout to something greater than 0, the message is retrieved, but this defeats the purpose of using a Windows Service app, which I want to continuously monitor the queue. I noticed the External Activator spawns a thread to start monitoring an EventNotification queue, which I can bypass since I want to monitor the notification's target queue.

Rushi, can you point me in the right direction to create a Windows Service that constantly monitors a queue? Also, I'd like the ability to monitor multiple databases (the queue name would be the same) as well, so if that is not feasible from a Windows Service please let me know.

Also, am I sacrificing scalability by NOT using the External Activator and switching to a Windows Service (I believe the External Activator will spawn multiple instances of the processing executable)?

Thanks,

Chris

If you do not need dynamic launching of one or more instances of your service program then you are fine with your approach of writing a single threaded process (which could be a Windows Service) that receives messages from the queue serially. (Of course, you could get fancy and have multiple (but a fixed number of) threads in your process simultaneously pulling messages from the queue). But if you need the ability to control the number of instances of your service program (processes or threads) based on rate of incoming messages, you will need to either implement the external activator based on the QUEUE_ACTIVATION event notification or use the sample.

If you choose the non-activation approach (i.e. your process is always running and waiting for messages), then setting WaitforTimeout to -1 seems appropriate to me. The Service class does not have an OnStart method, so I'm not sure what you are referring to. It does have a Run() method; and if you set WaitforTimeout to -1, Run will never return. While it would be nice to have a Cancel() method that would gracefully tear down the Service class, but since the Service Broker Interface is only a sample, we did not implement that. It should be fine to terminate the process while Run() has not returned. (The transaction will automatically be rolled back).

Rushi

|||

Rushi,

Thanks for the reply. Sorry, I was using the term "service" to refer to the Service class defined in the ServiceBrokerInterface project and a Windows Service app. What I mean is, in the OnStart() method of a Windows Service app, I would need to call the Service class' Run() method, which will never return. This means that the Windows Service's OnStart() method never completes, and you end up getting an error message from the ServiceInstaller (I think). I realize this may be more of a development question, and if so, I can try posting in another forum. I was just hoping you'd seen an example of RECEIVEing messages from a queue with a timeout of -1 from a Windows Service app.

Thanks,

Chris

|||

You will need to create a Thread in the constructor of your Windows Service class which is started by the OnStart() method. The ThreadStart should point to the ServiceBrokerInterface Service.Run() method.

Hope that helps,
Rushi

|||

Rushi:

You say...."While it would be nice to have a Cancel() method that would gracefully tear down the Service class, but since the Service Broker Interface is only a sample"

What would be those graceful tear down steps ..If I want to implement one?. I can see the following...

If there are still pending messages read from the queue but not yet processed ...return such entries back to the queue by rolling back the transaction.

Implementation wise.... "cancel()" can set a flag and the Dispatch function does not dispatch messages if this flag is set ....this way messages that are already read from queue can be prevented from getting processed.

What else I am missing.?

RECEIVING messages from a Windows service

I've done a bit of work with the External Activator but I think it may be a bit overkill for what I need to do (which is RECEIVE messages from a single queue and process them with managed code). I've tried creating a Service Broker Interface service that retrieves messages from this queue, but I notice that if I set the timeout to -1 to watch for messages indefinitely, the Service never completes the OnStart code.

I notice if I change the service's timeout to something greater than 0, the message is retrieved, but this defeats the purpose of using a Windows Service app, which I want to continuously monitor the queue. I noticed the External Activator spawns a thread to start monitoring an EventNotification queue, which I can bypass since I want to monitor the notification's target queue.

Rushi, can you point me in the right direction to create a Windows Service that constantly monitors a queue? Also, I'd like the ability to monitor multiple databases (the queue name would be the same) as well, so if that is not feasible from a Windows Service please let me know.

Also, am I sacrificing scalability by NOT using the External Activator and switching to a Windows Service (I believe the External Activator will spawn multiple instances of the processing executable)?

Thanks,

Chris

If you do not need dynamic launching of one or more instances of your service program then you are fine with your approach of writing a single threaded process (which could be a Windows Service) that receives messages from the queue serially. (Of course, you could get fancy and have multiple (but a fixed number of) threads in your process simultaneously pulling messages from the queue). But if you need the ability to control the number of instances of your service program (processes or threads) based on rate of incoming messages, you will need to either implement the external activator based on the QUEUE_ACTIVATION event notification or use the sample.

If you choose the non-activation approach (i.e. your process is always running and waiting for messages), then setting WaitforTimeout to -1 seems appropriate to me. The Service class does not have an OnStart method, so I'm not sure what you are referring to. It does have a Run() method; and if you set WaitforTimeout to -1, Run will never return. While it would be nice to have a Cancel() method that would gracefully tear down the Service class, but since the Service Broker Interface is only a sample, we did not implement that. It should be fine to terminate the process while Run() has not returned. (The transaction will automatically be rolled back).

Rushi

|||

Rushi,

Thanks for the reply. Sorry, I was using the term "service" to refer to the Service class defined in the ServiceBrokerInterface project and a Windows Service app. What I mean is, in the OnStart() method of a Windows Service app, I would need to call the Service class' Run() method, which will never return. This means that the Windows Service's OnStart() method never completes, and you end up getting an error message from the ServiceInstaller (I think). I realize this may be more of a development question, and if so, I can try posting in another forum. I was just hoping you'd seen an example of RECEIVEing messages from a queue with a timeout of -1 from a Windows Service app.

Thanks,

Chris

|||

You will need to create a Thread in the constructor of your Windows Service class which is started by the OnStart() method. The ThreadStart should point to the ServiceBrokerInterface Service.Run() method.

Hope that helps,
Rushi

|||

Rushi:

You say...."While it would be nice to have a Cancel() method that would gracefully tear down the Service class, but since the Service Broker Interface is only a sample"

What would be those graceful tear down steps ..If I want to implement one?. I can see the following...

If there are still pending messages read from the queue but not yet processed ...return such entries back to the queue by rolling back the transaction.

Implementation wise.... "cancel()" can set a flag and the Dispatch function does not dispatch messages if this flag is set ....this way messages that are already read from queue can be prevented from getting processed.

What else I am missing.?

sql

Receiving a message with a specific contract

If the service is defined with multiple contracts is there a way to receive a message with a specific contract?

No. I'm curious to understand why do you want this, can you give a bit of detail?

Thanks,
~ Remus

|||

I am implementing a queue processing system. You can get some of the info here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446600&SiteID=1. The queue can contain messages of different contracts/types. When I receive a message I instantiate a different processing component based on the contract/type of the message. There are processing servers that process different kind of messages. The single processing server might only have a subset of processing components. This is why I need a way to retrieve messages based on the contract/type.

Alex

|||

You can try having one service as front end that simply forwards each dialog to specialized services on the back end, similar to the approach described here: https://blogs.msdn.com/remusrusanu/archive/2006/03/28/563571.aspx

Each supported contract would be forwarded to a different service.

HTH,
~ Remus

|||

I have a working implementation without Service Broker which handles message types and message priority (another one Service Broker does not handle directly). It does not make sence to switch to Service Broker which would also require to increse the complexity of the architecture.

Alex

Received message constantly processed.

Hello,

when is seemd that everything works some weird behaviours comes out.

I try to summarize the problem without to post the complete code.

Service Broker is set to have a dialog between two databases on the same SQL Server instance.

The Initiator queue has retention=on and there is an activation SP to handle errors and Target's end dialog message.

The Target queue has retention=off, MAX_READER =1 and there is an activation SP to receive the message (WAIT FOR (RECEIVE (1) ...), TIMEOUT 30000 and do something with this message (sample insert into a DB).

The conversation has a Timeout Dialog to end the dialog after a while.

The problem that the message is constantly processed. The Process doens't stop is I end the dialof after the processing either.

n.b.the Receive is within a Transation that I commit at the end.

some other informations that in the meanwhile I found out :

This was my complete WAIT FOR(RECEIVE :

WAITFOR ( RECEIVE top(1) -- just handle one message at a time
@.message_type=message_type_id, --the type of message received
@.messagetypename=message_type_name,
@.message_body=message_body, -- the message contents
@.dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM [TargetQueue]
), timeout 1000;

if (@.@.ROWCOUNT = 0)
BEGIN
COMMIT;
BREAK;
END

IF I delet TIMEOUT 1000, everything works as expected ...
Inside if (@.@.ROWCOUNT = 0)BEGIN..END I wrote also an Insert into a table to see wheter the end of the queue was reached but this insert never occurs (neither with not without timeout)
I'm happy that it works what if this is the solution, it make no sense to me!

Any ideas?
Thank you!

M.B.

Thank you very much

M.B.

This sounds like there are messages constantly comming and causing the WAITOFR(RECEIVE...) to get another message while is waiting. Is this the case?|||

Hi,

it shouldn't be!!

I suppose that the only way to understand what is going on is to use the SQL Profiler.

Isn't?

Thank you

|||

Hi,

I tried to use the SQL server Profiler to understand what is going on when I send/receive the message.

First at all on both queues(initiator and target) I have RETENTION = OFF so the check if @.@.ROWCOUNT = 0 will be never true because the message stays in the queue till the END of the CONVERSATION and in my case the conversation will end when there is an error or when the conversation timeout expires; otherwise I send/receive using the same conversation handle.

If I write:

WAITFOR (

RECEIVE top(1) ...

), TIMEOUT 5000;

In the SQL Server Profiler I seen that the SP that is called after the receive statement and that processes the message is constantly executed.

If I write:

WAITFOR (

RECEIVE top(1) ...

)

In the SQL Server Profiler I seen that the SP that is called after the receive statement and that processes the message is executed only ONCE.

I'm quite confuse ... I thought that is better to use a timeout but if I use I have that strange behaviour!!

Any helps/advices?
Thankx

Marina B.

|||

@.@.ROWCOUNT is set by RECEIVE to the number of rows (messages) actually returned, so it doesn't matter if RETENTION is ON or OFF.

From your description of the problem is clear that your procedured does not correctly detect when RECEIVE returned no rows, so it continues to spin on empty RECEIVEs that timeout. Can you post the actual code of the procedure?

|||

Hi Remus,

thank you very much for your answer...

Maybe it is not necessarely but this is the SP wuch start the conversation (activated by a trigger on a insert )

PROCEDURE [dbo].[sp_sendInserted]

@.MessageXML nvarchar(max)

AS

BEGIN

DECLARE @.dialog_handle uniqueidentifier;

DECLARE @.dialog_id uniqueidentifier;

DECLARE @.msg XML;

DECLARE @.Error INT;

SET NOCOUNT OFF;

BEGIN TRY

BEGIN TRANSACTION;

set @.msg = Convert(xml,@.MessageXML);

WHILE (1=1)

BEGIN

set @.dialog_handle = (select conversation_handle from sys.conversation_endpoints where far_service='ReceivedService');

if @.dialog_handle is null

--Begin new dialog

BEGIN

BEGIN DIALOG CONVERSATION @.dialog_handle

FROM SERVICE [SendService]

TO SERVICE 'ReceivedService'

ON CONTRACT [MainContract]

WITH ENCRYPTION = OFF;

BEGIN CONVERSATION TIMER (@.dialog_handle) TIMEOUT = 600;

END;

SEND ON CONVERSATION @.dialog_handle

MESSAGE TYPE MyMessage (@.msg); END

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF XACT_STATE() = -1

BEGIN

ROLLBACK TRANSACTION;

END

Insert into [dbo].[tblErrorXMLMessages] values(@.MessageXML,@.dialog_handle,@.dialog_id,null,null,ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_PROCEDURE(),getdate());

END CATCH

END

And this is the SP activated on the ReceiveQueue:

PROCEDURE [dbo].[OnReceivedMessage]

AS

DECLARE @.message_type INT;

DECLARE @.messagetypename NVARCHAR(1000);

DECLARE @.XMLmessage_body XML;

DECLARE @.dialog UNIQUEIDENTIFIER;

DECLARE @.dialog_id UNIQUEIDENTIFIER;

DECLARE @.ErrorSave INT;

DECLARE @.ErrorDesc NVARCHAR(100);

SET NOCOUNT ON;

WHILE (1 = 1)

BEGIN

BEGIN TRY

BEGIN TRANSACTION;

WAITFOR (

RECEIVE top(1) -- just handle one message at a time

@.message_type=message_type_id, --the type of message received

@.messagetypename=message_type_name,

@.XMLmessage_body=message_body, -- the message contents

@.dialog = conversation_handle -- the identifier of the dialog this message was received on

FROM [ReceivedQueue]

)--, timeout 5000; -- if the queue is empty for three second

--Before to close the conversation I get the Conversation ID to be stored in the tblErrorXMLMessages

set @.dialog_id = (select conversation_id from sys.Conversation_endpoints where conversation_handle = @.dialog);

-- If we didn't get anything, bail out

if (@.@.ROWCOUNT = 0)

BEGIN

INSERT INTO [TestReceiver].[dbo].[tblReceived] VALUES('Received SP','<ReceivedQueue>NO more messages in the queue</ReceivedQueue>');

COMMIT;

BREAK;

END

If (@.messagetypeName = N'MyMessage')

BEGIN

DECLARE @.ret integer;

EXECUTE @.ret = [TestReceiver].[dbo].[sp_ShredXMLMessageToRelationalData] @.XMLmessage_body; --SP that PROCESSES THE MESSAGE RECEIVED ON THE QUEUE

if (@.ret <> 0)

INSERT INTO [TestSender].[dbo].[tblErrorXMLMessages] VALUES

(cast(@.XMLmessage_body as nvarchar(max)),@.dialog,@.dialog_id,null,null,0,null,null,getdate());

END

ELSE IF (@.messagetypeName = N'EndOfStream')

BEGIN

END CONVERSATION @.dialog;

END

-- Check for the Error Dialog message.

ELSE IF(@.messagetypeName=N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')

BEGIN

DECLARE @.WrongXMLMessage NVARCHAR(max);

set @.WrongXMLMessage = (Select message_body FROM dbo.TradeReceivedQueue WITH (NOLOCK) where Message_type_name = 'MyMessage' and status = 3 and conversation_handle=@.dialog);

DECLARE @.Error int;

DECLARE @.ErrorDescription nvarchar(4000);

WITH XMLNAMESPACES

('http://schemas.microsoft.com/SQL/ServiceBroker/Error' as ssb)

SELECT @.Error = cast(@.XMLMessage_Body as XML).value('(//ssb:Error/ssb:Code)[1]','INT'),

@.ErrorDescription = cast(@.XMLMessage_Body as XML).value('(//ssb:Error/ssbescription)[1]','nvarchar(4000)')

IF @.WrongXMLMessage is null

BEGIN

SET @.WrongXMLMessage = N'<Error>Error retrieving the wrong XML message from the sender queue</Error>';

END

Insert into [TestSender].[dbo].[tblErrorXMLMessages] values(@.WrongXMLMessage,@.dialog,@.dialog_id,'MArina','ReceivedQueue',@.Error,@.ErrorDescription,ERROR_PROCEDURE(),getdate());

--After the End Conversation all messages from the queue are deleted

END CONVERSATION @.dialog;

END

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

insert into [testReceiver].[dbo].[tblReceived] values('Error','<Marina>Error in SP Traget QUEUE</Marina>');

END CATCH

END

Let me know whether you need more info regarding contract, queues etc..etc..

Thank you very much!

Marina B.

|||

marina B. wrote:

WAITFOR (

RECEIVE top(1) -- just handle one message at a time

@.message_type=message_type_id, --the type of message received

@.messagetypename=message_type_name,

@.XMLmessage_body=message_body, -- the message contents

@.dialog = conversation_handle -- the identifier of the dialog this message was received on

FROM [ReceivedQueue]

)--, timeout 5000; -- if the queue is empty for three second

--Before to close the conversation I get the Conversation ID to be stored in the tblErrorXMLMessages

set @.dialog_id = (select conversation_id from sys.Conversation_endpoints where conversation_handle = @.dialog);

-- If we didn't get anything, bail out

if (@.@.ROWCOUNT = 0)

BEGIN

INSERT INTO [TestReceiver].[dbo].[tblReceived] VALUES('Received SP','<ReceivedQueue>NO more messages in the queue</ReceivedQueue>');

COMMIT;

BREAK;

END

SQL Server 2005 Books Online wrote:

@.@.ROWCOUNT (Transact-SQL)

Returns the number of rows affected by the last statement

RECEIVE is not the last statement before you check @.@.ROWCOUNT.

|||

As always,

my stupid mistake.

I thought that I was making a mess with the transaction!!

I moved the @.@.Rowcount check after the RECEIVE statement and it worked..

I should read more CAREFULLY the BOL !!

Thankx!!

Marina B.

Receive notification on several clients at the same time

Hello.
I'd like to reveive notifications on the client application when table
data is changed.
How could I use Service Broker, queues and services? In my tests I
have test queue (NotifyTestQueue), test service (NotifyTestService).
If only one client application instance is running - all works fine.
Application runs sql query
WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
in the separate thread and thread execution is continued after message
in the queue is available.
But if I run two ore more client application instance only one of them
receives notification :(
Help me please - how could I set up service broker or queue or service
to enalbe notification for several client about the same event in the
queue?
Should I give up and use individual table with records as events for
clients?
Thank you.I think the problem is when your client polls the Q and does a RECEIVE, the
message is lost, thus if another client is also looking for the same
message, it will not be there.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140520111.673015.124620@.z14g2000cwz.googlegroups.com...
> Hello.
> I'd like to reveive notifications on the client application when table
> data is changed.
> How could I use Service Broker, queues and services? In my tests I
> have test queue (NotifyTestQueue), test service (NotifyTestService).
> If only one client application instance is running - all works fine.
> Application runs sql query
> WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
> in the separate thread and thread execution is continued after message
> in the queue is available.
> But if I run two ore more client application instance only one of them
> receives notification :(
> Help me please - how could I set up service broker or queue or service
> to enalbe notification for several client about the same event in the
> queue?
> Should I give up and use individual table with records as events for
> clients?
> Thank you.
>|||Consider using Notification Services.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140520111.673015.124620@.z14g2000cwz.googlegroups.com...
Hello.
I'd like to reveive notifications on the client application when table
data is changed.
How could I use Service Broker, queues and services? In my tests I
have test queue (NotifyTestQueue), test service (NotifyTestService).
If only one client application instance is running - all works fine.
Application runs sql query
WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
in the separate thread and thread execution is continued after message
in the queue is available.
But if I run two ore more client application instance only one of them
receives notification :(
Help me please - how could I set up service broker or queue or service
to enalbe notification for several client about the same event in the
queue?
Should I give up and use individual table with records as events for
clients?
Thank you.|||The easiest thing is to use QueryNotification which is one of the new
features within SQL Server 2005.
HTH, jens Suessmeyer.|||Hello Jens.
I've use query notification but it works only for last client. Here is
the code:

code:

private static void NotificationTest()
{
using (SqlConnection connection = new
SqlConnection(_connectionStringLocal))
{
connection.Open();
SqlCommand cmd = new SqlCommand(
"SELECT GenderID, IsActive, DescrLocal FROM dbo.cls_Genders",
connection);
SqlNotificationRequest notifyRequest = new SqlNotificationRequest();
notifyRequest.UserData = "Any User Data";
notifyRequest.Options = "service=NotifyTestService";
notifyRequest.Timeout = 600;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
Console.WriteLine(rdr[0] + "\t" + rdr[2] + "\t" + rdr[1]);
}
Console.WriteLine("notifyRequest was sent");
Console.ReadKey();
WaitForChanges();
}
private static void WaitForChanges()
{
using (SqlConnection connection = new
SqlConnection(_connectionStringLocal))
{
connection.Open();
SqlCommand cmd = new SqlCommand(
"WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)",
connection);
cmd.CommandTimeout = 0;
object o = cmd.ExecuteScalar();
Console.WriteLine(o);
}
}

So after key is pressed and event is fired on the server only one
client gots event.|||I should explain what is happened with the code above.
So when one client executes NotificationTest method all works fine.
Client gets notification from the SQL server.
But when two or more client were started only one (last started)
receives notification - all others don't. As SriSamp says one client
application polls the queue and other clients miss event. How could I
leave message in the queue when RECEIVE statement is executed? I cann't
find anything that may help me with it :(
Thank you.|||A message can only be dequeued once. There's no way around this.
What you want to do is to multiply that one message into N messages, for the
N clients. Instead of clients waiting on the NotifyTestQueue, each client
waits on a dedicated queue, NotifyClient1...NoifyClientN. Create activated
stored procedure on the NotifyTestQueue that when it receives a message it
forwards this message to all clients (by SENDing the message N times). This
is basically a pub-sub system implemented on top of dialogs. A practical
example on how to do this is at
http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
Note that you cannot do this if you have too many clients (hundreds). If
each client has a WAIFOR(RECEIVE) blocked waiting for a notification, each
of these statements will block one thread on the server, and you'll hit the
upper configured limit max_worker_threads
http://msdn.microsoft.com/library/d... />
g_09wu.asp
If you need a large number of clients the solution must involve partitioning
out the N client services/queues on a number of different SQL Instances,
since SSB can deliver the notification remotely.
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
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140536881.331233.150770@.f14g2000cwb.googlegroups.com...
>I should explain what is happened with the code above.
> So when one client executes NotificationTest method all works fine.
> Client gets notification from the SQL server.
> But when two or more client were started only one (last started)
> receives notification - all others don't. As SriSamp says one client
> application polls the queue and other clients miss event. How could I
> leave message in the queue when RECEIVE statement is executed? I cann't
> find anything that may help me with it :(
> Thank you.
>|||Hello Remus.
Thank you for fast reply.
Can I use RETENTION = ON parameter on queue?

> Instead of clients waiting on the NotifyTestQueue, each client
waits on a dedicated queue, NotifyClient1...NoifyClientN.
Its not suitable for me - I don't know how many clients will be
connected to the database at future.

> Note that you cannot do this if you have too many clients (hundreds). If
each client has a WAIFOR(RECEIVE) blocked waiting for a notification,
each
of these statements will block one thread on the serve
I'd like to perform RECEIVE without WAITFOR from time to time in the
separate thread on the client.
It seems to me that there is still only one method to notify connected
clients that changes in data occured. Fill some special notify table
and polls this table from time to time at the client. Is it true?
Thank you.|||See inline
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
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140594793.299405.111750@.g44g2000cwa.googlegroups.com...
> Hello Remus.
> Thank you for fast reply.
> Can I use RETENTION = ON parameter on queue?
Remus: No, retention does not allow for a message to be receive more than
one time. Retention keeps a copy of the message in the queue if compensation
is needed, but this copy is not receivable.

> waits on a dedicated queue, NotifyClient1...NoifyClientN.
> Its not suitable for me - I don't know how many clients will be
> connected to the database at future.
Remus: That's manageable, clients can create a queue and then
subscribe/unsubscribe. Separate queues allow for clients to fan out and be
partitioned on several machines for scalability.

>
> each client has a WAIFOR(RECEIVE) blocked waiting for a notification,
> each
> of these statements will block one thread on the serve
> I'd like to perform RECEIVE without WAITFOR from time to time in the
> separate thread on the client.
Remus: Any reason for this? Pooling is in general worse than WAITFOR. Note
that
WAITFOR (RECEIVE) does not pool, it uses a notification mechanism.

> It seems to me that there is still only one method to notify connected
> clients that changes in data occured. Fill some special notify table
> and polls this table from time to time at the client. Is it true?
Remus: No, this is not true. Pooling for changes or pooling for change
notifications is limited in scalability. There is the potential to overload
the server with just answering the queries that are pooling for changes. The
way to address this is to fan out change notifications for each subscribed
client.
You can actually decide which approach is better, based on the specifics of
you case. Some back of the envelope cacluclation will give you a rough
answer:
First, lets figure out the pooling case. Consider you have N clients, each
pooling at an interval of t seconds and the pooling query batch consumes r
seconds on the server. On any interval of time T you are gonna consume a
total pooling runtime on the server R = r x t/N x T . The R/T ratio gives
how much of the total server runtime is consumed serving the pooling
queries. So basically the r x t/N number is giving you this ratio.
In the case of WAITFOR (RECEIVE), you're gonna have N clients blocked on the
server waiting for notifications. Considering each individual client is
going to be notified (per second) with a probability p and the server
runtime consumed for seting up the WAITFOR, delivering the notification and
returning the result is w, then the total runtime consumed on the server
during the interval T will be w x p x T x N. Again, dividing this by T gives
you the ratio of server runtime consumed in servicing these notifications.
So basically the ratio is w x p x N.
So in the end you're left comparing the r x t/N versus w x p x N.
If you consider the pool query runtime r as being based on a SELECT over one
clustered index and the WAITFOR w time as a RECEIVE from a queue, a rough
approximation of w vs. r should be w = 3 x r (maybe 4 x r), because the
WAITFOR will execute a similar scan as the SELECT, but twice (once before
suspending itself, once when is notified) and it also executes a write
operation (the dequeue)
So is all about comparing t/N vs. 3 x p x N.
- smaller t means clients cannot tolerate dirty cache, they need to pool
frequently, larger t means clients are OK with presenting potentially
outdated data. A large t favors the pooling model.
- larger p means the data is updated often and the cache will be invalidated
often for each client, smaller p means the cache invalidation is unfrequent.
A small p favors the WAITFOR.
Is also important if there is a corelation between p and N. If
each client is interested in a separate partition of the possible data
changes, then increasing N will likely decrease p, favouring the WAITFOR (or
at least hurting it less than the pooling). If all clients are interested in
same changes p does not decrease with an increase of N, this case hurst both
models.
Also, given a sufficient large N both models will crumble as the ratio
approaches 1, meaning all of the server resources are required just to
service the notifications. This case must reside to a fan-out mechanism the
WAITFOR model is better suited for.

> Thank you.
>|||Hello.
I cann't ever express how you post helps me. Thank you very much.
I should read more about Notification services and how it could be
connected with Service borker.
Good luck.

Receive alerts on SQLServer service up/down status

I am researching about this capability within SQL 7.0. I would like to use 1 SQL server to monitor all other SQL servers on "SQL Server service up/down" condition. I don't seem to be able to do it because when SQLServer service is down, the SQLAgent service stopped as well.
And, I think SQLAgent service is needed to communicate the status.
Does anyone know if this capability exist in SQL 7 or not? If not, I think I will look into other tools. Any information is appreciated. Thanks in advance!Debbie,
You could create a periodic SQL agent job on the monitoring server that polls
the others by attempting to run a simple stored proc or similar.
The on failure step can be set to do the alarm.
I'm not sure what you mean by the service up/down problem. Are you talking
about the monitoring sql server ?
The SQL Agent has SQL Server as a dependancy, so it wont run without it.
But they are real separate services, so you could "monitor" them by service
control manager commands.
Regards
AJ
"Debbie" <anonymous@.discussions.microsoft.com> wrote in message
news:BC78F429-88C2-4961-8010-969059F95D91@.microsoft.com...
> I am researching about this capability within SQL 7.0. I would like to use 1 SQL server to monitor all
other SQL servers on "SQL Server service up/down" condition. I don't seem to be able to do it because when
SQLServer service is down, the SQLAgent service stopped as well.
> And, I think SQLAgent service is needed to communicate the status.
> Does anyone know if this capability exist in SQL 7 or not? If not, I think I will look into other tools.
Any information is appreciated. Thanks in advance!|||You'd be far better off using a script written in a language other than
T-SQL for this type of monitoring purposes. The main reason is that many
error messages are sent to the client and the client needs to be able to
trap and check these error messages and respond properly. A real scripting
language is also better in handling whatever notification logic you want to
implement and in sending alerts
You can write a simple script to periodically try to connect to each of the
SQL instances you want to monitor. If the connection attempt fails, the
script can then decide whether to send an alert. You may want send an alert
only when there are several consecutive failed connection attempts in order
to minimize nuisance alerts.
If you are not into scripting, use a third-party package. But these
commerical tools often come with a lot of excessive weight, offering far
more than you need and costing a bundle.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Debbie" <anonymous@.discussions.microsoft.com> wrote in message
news:BC78F429-88C2-4961-8010-969059F95D91@.microsoft.com...
> I am researching about this capability within SQL 7.0. I would like to
use 1 SQL server to monitor all other SQL servers on "SQL Server service
up/down" condition. I don't seem to be able to do it because when SQLServer
service is down, the SQLAgent service stopped as well.
> And, I think SQLAgent service is needed to communicate the status.
> Does anyone know if this capability exist in SQL 7 or not? If not, I
think I will look into other tools. Any information is appreciated. Thanks
in advance!

Rebuild master db of SQL 2000 fails with sharing violation

Hi,
Can't see why there's a sharing violation.
MSSQLSERVER service is stopped, as is SQLSERVERAGENT
I've not got any open windows viewing this dir.
The data is on hard disk and not marked read only.
What else could it be?
Thanks
Martin
Hi,
Can you rebuild the master database based on the steps mentioned in the
below article.
http://www.dbarecovery.com/restoremasterdb.html
Thanks
Hari
MCDBA
"Martin" <x@.y.z> wrote in message
news:#SddJUZaEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can't see why there's a sharing violation.
> MSSQLSERVER service is stopped, as is SQLSERVERAGENT
> I've not got any open windows viewing this dir.
> The data is on hard disk and not marked read only.
> What else could it be?
> Thanks
> Martin
>
|||Hello-
I'm experiencing a similar problem. "Cannot copy master. There has been a sharing violation. The source or destination file may be in use. Rebuild master failed with error 266: The copy functions cannot be used" ... when attempting to Rebuild with the
Rebuild Master Utility. The data files have been copied from CD-ROM and the Read-Only attribute has been removed. I still get the error. The work around posted as MS Knowledge Base Article 238678 RebuildM Cannot Find Original Master Database Files for
MSDE Installation does not work either. It suggests running msdex86 ... which isn't recognized. Any help would be much appreciated!
"Hari Prasad" wrote:

> Hi,
> Can you rebuild the master database based on the steps mentioned in the
> below article.
> http://www.dbarecovery.com/restoremasterdb.html
> --
> Thanks
> Hari
> MCDBA
> "Martin" <x@.y.z> wrote in message
> news:#SddJUZaEHA.384@.TK2MSFTNGP10.phx.gbl...
>
>
|||Moved the data files to a different directory and now the Rebuild works. Hmmmm...
"bulld0g77" wrote:

> Hello-
> I'm experiencing a similar problem. "Cannot copy master. There has been a sharing violation. The source or destination file may be in use. Rebuild master failed with error 266: The copy functions cannot be used" ... when attempting to Rebuild with t
he Rebuild Master Utility. The data files have been copied from CD-ROM and the Read-Only attribute has been removed. I still get the error. The work around posted as MS Knowledge Base Article 238678 RebuildM Cannot Find Original Master Database Files f
or MSDE Installation does not work either. It suggests running msdex86 ... which isn't recognized. Any help would be much appreciated![vbcol=seagreen]
> "Hari Prasad" wrote:

Rebuild master db of SQL 2000 fails with sharing violation

Hi,
Can't see why there's a sharing violation.
MSSQLSERVER service is stopped, as is SQLSERVERAGENT
I've not got any open windows viewing this dir.
The data is on hard disk and not marked read only.
What else could it be?
Thanks
MartinHi,
Can you rebuild the master database based on the steps mentioned in the
below article.
http://www.dbarecovery.com/restoremasterdb.html
Thanks
Hari
MCDBA
"Martin" <x@.y.z> wrote in message
news:#SddJUZaEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can't see why there's a sharing violation.
> MSSQLSERVER service is stopped, as is SQLSERVERAGENT
> I've not got any open windows viewing this dir.
> The data is on hard disk and not marked read only.
> What else could it be?
> Thanks
> Martin
>|||Hello-
I'm experiencing a similar problem. "Cannot copy master. There has been a
sharing violation. The source or destination file may be in use. Rebuild m
aster failed with error 266: The copy functions cannot be used" ... when att
empting to Rebuild with the
Rebuild Master Utility. The data files have been copied from CD-ROM and the
Read-Only attribute has been removed. I still get the error. The work aro
und posted as MS Knowledge Base Article 238678 RebuildM Cannot Find Original
Master Database Files for
MSDE Installation does not work either. It suggests running msdex86 ... whi
ch isn't recognized. Any help would be much appreciated!
"Hari Prasad" wrote:

> Hi,
> Can you rebuild the master database based on the steps mentioned in the
> below article.
> http://www.dbarecovery.com/restoremasterdb.html
> --
> Thanks
> Hari
> MCDBA
> "Martin" <x@.y.z> wrote in message
> news:#SddJUZaEHA.384@.TK2MSFTNGP10.phx.gbl...
>
>|||Moved the data files to a different directory and now the Rebuild works. H
mmmm...
"bulld0g77" wrote:

> Hello-
> I'm experiencing a similar problem. "Cannot copy master. There has been a sharin
g violation. The source or destination file may be in use. Rebuild master failed w
ith error 266: The copy functions cannot be used" ... when attempting to Rebuild wit
h t
he Rebuild Master Utility. The data files have been copied from CD-ROM and
the Read-Only attribute has been removed. I still get the error. The work
around posted as MS Knowledge Base Article 238678 RebuildM Cannot Find Origi
nal Master Database Files f
or MSDE Installation does not work either. It suggests running msdex86 ... which isn't reco
gnized. Any help would be much appreciated![vbcol=seagreen]
> "Hari Prasad" wrote:
>

Rebuild master db of SQL 2000 fails with sharing violation

Hi,
Can't see why there's a sharing violation.
MSSQLSERVER service is stopped, as is SQLSERVERAGENT
I've not got any open windows viewing this dir.
The data is on hard disk and not marked read only.
What else could it be?
Thanks
MartinHi,
Can you rebuild the master database based on the steps mentioned in the
below article.
http://www.dbarecovery.com/restoremasterdb.html
--
Thanks
Hari
MCDBA
"Martin" <x@.y.z> wrote in message
news:#SddJUZaEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can't see why there's a sharing violation.
> MSSQLSERVER service is stopped, as is SQLSERVERAGENT
> I've not got any open windows viewing this dir.
> The data is on hard disk and not marked read only.
> What else could it be?
> Thanks
> Martin
>|||Hello-
I'm experiencing a similar problem. "Cannot copy master. There has been a sharing violation. The source or destination file may be in use. Rebuild master failed with error 266: The copy functions cannot be used" ... when attempting to Rebuild with the Rebuild Master Utility. The data files have been copied from CD-ROM and the Read-Only attribute has been removed. I still get the error. The work around posted as MS Knowledge Base Article 238678 RebuildM Cannot Find Original Master Database Files for MSDE Installation does not work either. It suggests running msdex86 ... which isn't recognized. Any help would be much appreciated!
"Hari Prasad" wrote:
> Hi,
> Can you rebuild the master database based on the steps mentioned in the
> below article.
> http://www.dbarecovery.com/restoremasterdb.html
> --
> Thanks
> Hari
> MCDBA
> "Martin" <x@.y.z> wrote in message
> news:#SddJUZaEHA.384@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Can't see why there's a sharing violation.
> >
> > MSSQLSERVER service is stopped, as is SQLSERVERAGENT
> >
> > I've not got any open windows viewing this dir.
> > The data is on hard disk and not marked read only.
> >
> > What else could it be?
> >
> > Thanks
> > Martin
> >
> >
>
>|||Moved the data files to a different directory and now the Rebuild works. Hmmmm...
"bulld0g77" wrote:
> Hello-
> I'm experiencing a similar problem. "Cannot copy master. There has been a sharing violation. The source or destination file may be in use. Rebuild master failed with error 266: The copy functions cannot be used" ... when attempting to Rebuild with the Rebuild Master Utility. The data files have been copied from CD-ROM and the Read-Only attribute has been removed. I still get the error. The work around posted as MS Knowledge Base Article 238678 RebuildM Cannot Find Original Master Database Files for MSDE Installation does not work either. It suggests running msdex86 ... which isn't recognized. Any help would be much appreciated!
> "Hari Prasad" wrote:
> > Hi,
> >
> > Can you rebuild the master database based on the steps mentioned in the
> > below article.
> >
> > http://www.dbarecovery.com/restoremasterdb.html
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> >
> > "Martin" <x@.y.z> wrote in message
> > news:#SddJUZaEHA.384@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > Can't see why there's a sharing violation.
> > >
> > > MSSQLSERVER service is stopped, as is SQLSERVERAGENT
> > >
> > > I've not got any open windows viewing this dir.
> > > The data is on hard disk and not marked read only.
> > >
> > > What else could it be?
> > >
> > > Thanks
> > > Martin
> > >
> > >
> >
> >
> >

reboot problem (not always)

Hi,
I have a server where, when I reboot it, sometimes the sql server 2005
service stop in starting mode and the performance log and alert service in
stopping mode.
this lock the computer from any remote access. (including Terminal server)
I have to do a hard reboot.
and if this doesn't works, I have to reboot again until all the services are
up and running correctly.
any idea of what can cause this lock?
why SQL Server don't starts correctly? and why the performance log service
don't stops?
thanks.
Jerome.JeJe,
Check the Application Event Log for any errors...may lead you to the cause.
HTH
Jerry
"Jeje" <willgart@.hotmail.com> wrote in message
news:OPGi4lafGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a server where, when I reboot it, sometimes the sql server 2005
> service stop in starting mode and the performance log and alert service in
> stopping mode.
> this lock the computer from any remote access. (including Terminal server)
> I have to do a hard reboot.
> and if this doesn't works, I have to reboot again until all the services
> are up and running correctly.
> any idea of what can cause this lock?
> why SQL Server don't starts correctly? and why the performance log service
> don't stops?
> thanks.
> Jerome.
>

reboot problem (not always)

Hi,
I have a server where, when I reboot it, sometimes the sql server 2005
service stop in starting mode and the performance log and alert service in
stopping mode.
this lock the computer from any remote access. (including Terminal server)
I have to do a hard reboot.
and if this doesn't works, I have to reboot again until all the services are
up and running correctly.
any idea of what can cause this lock?
why SQL Server don't starts correctly? and why the performance log service
don't stops?
thanks.
Jerome.JeJe,
Check the Application Event Log for any errors...may lead you to the cause.
HTH
Jerry
"Jeje" <willgart@.hotmail.com> wrote in message
news:OPGi4lafGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a server where, when I reboot it, sometimes the sql server 2005
> service stop in starting mode and the performance log and alert service in
> stopping mode.
> this lock the computer from any remote access. (including Terminal server)
> I have to do a hard reboot.
> and if this doesn't works, I have to reboot again until all the services
> are up and running correctly.
> any idea of what can cause this lock?
> why SQL Server don't starts correctly? and why the performance log service
> don't stops?
> thanks.
> Jerome.
>