How deos Schedule Subscription evaluated within Notificaion Service

Hi,

I have read most of the threads here and many online resources but still couldn't find my answers. I am trying to set up a schedule subscription with the following condition:

1. condition: Users borrow books and should return in 30 days.

2. They should be notified 3 days before the book return date. The return date is different for every user depending on their borrow date.

3. Now can I make use of schedule subscription to achieve this. Basically one schedule subscription, different schedule datetime depending on return date.

I couldn't figure out how to proceed. How does the scheduling work within in NS and how does it make use of the following fields from the schedule table: UtcTime, ScheduleType, ScheduleData, ScheduleText.

Shyam and others,

Thanks.

I would probably approach this by creating a subscriber for each person that can borrow a book (either en mass as the beginning or as they borrow books from you). Then configure a scheduled subscription for each book they borrow. The schedule can be set for whatever time you'd like to notify them (1:00am, etc)

Configure your Event Provider to retrieve books that are due in 3 days.

Then write the matching rule.

Make sense?

HTH...

Joe

How client program get the events?

I know SQL 2005 notification service can send email, file, HTTP protocol to inform the client program the events.

1) How to implement the HTTP solution? My client program is Java or MFC. A sample is really appreciate.

2) If TCP socket could be listened for the events too?

Thanks a lot.

I don't have or even know of an example where the end subscriber is a client application (although that is a commonly asked question in the forum/newsgroup).

You'll need to create a custom delivery channel to provide this functionality.

You may also want to check out Query Notifications, as that technology may also suit your needs.

HTH...

Joesql

How client program get the events?

I know SQL 2005 notification service can send email, file, HTTP protocol to inform the client program the events.

1) How to implement the HTTP solution? My client program is Java or MFC. A sample is really appreciate.

2) If TCP socket could be listened for the events too?

Thanks a lot.

I don't have or even know of an example where the end subscriber is a client application (although that is a commonly asked question in the forum/newsgroup).

You'll need to create a custom delivery channel to provide this functionality.

You may also want to check out Query Notifications, as that technology may also suit your needs.

HTH...

Joe

How can Internal Activation use more resources?

Hi,

I'm using service broker queue with internal activation to run a stored procedure.
The DB server is windows 2003 R2, 4 cpu, with SQL server 2005 SP2.
When I'm runing the stored procedure directly from the sql management studio it takes about 75% of the cpu and running for about a minute, but when the stored procedure is activated by the queue internal activation (as a background process) it uses only 25% of the machine cpu (my guess it uses only 1 cpu insted of all 4 cpu) and running for much longer time (sometimes even more than one hour).
How can I change this behavior? I want it to run as fast as possible.

The queue decleration is:

CREATE QUEUE [TaskQueue]
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = ProcessTasksProc,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF);

Thanks in advance,
Shai Brumer

If you have 4 CPU, why are you restricting the MAX_QUEUE_READERS to 1?

How did you measure this? Did you preload the queue with messages and run the procedure? Or are the messages continuosly incomming from some local or remote sender?

Note that activated procedures are looped for you by the activation mechanism, that is if the procedure exists and there are still message in the queue, it is invoked again. You will not see a separate Activation:End and Activationtart events for this, so it may look like is running for hours but in fact is contiuosly re-invoked because there are still message in the queue.

With only 1 activated proc, it is normal to consume only 25% of the 4 CPUs. The only way a procedure could use more than one CPUs (and push CPU utilization above 25%) would be to contain many queries that can be parallelized. Do you have anything in the procedure that could be heavilly paralelized and thus explain the 75% when launched from SSMS?

|||

My intention is to run the stored procedure asynchronously from another stored procedure, and for this I used service broker queues as async mechanism- when I want to run the procedure asynchronously I enter a message into the queue that is configured to active the procedure. The procedure is heavy and using all the cpu’s of the server (I can see parallelization in the execution plan and the server cpu is up to 75% when I’m running the procedure directly from the management studio).

I set up the MAX_QUEUE_READERS to 1 because I want a single procedure to run at any given time- but I want the procedure to take as much resource she need in order to complete the run as fast as it can.

Is this the normal behavior of internal activation? (Useing only 1 CPU).

Is there any way to change this?

|||Activation procedure should behave just as if the procedure was launched from SSMS. If you ahve plans that are heavily parallel, they should continue to be parallel and use 70% CPU. Can you compare the plans when run under activation vs. when run from SSMS?|||

How can I get the execution plan from the SSMS ?

|||

I ment:

How can I get the execution plan from the activation ?

|||Attach the Profiler and monitor the server and monitor the Performance/Showplan XML for Query Compile. The Profiler will show the plans just like SSMS does and each event/plan can be saved as an individual .sqlplan file (right click on the event and select 'Extract Data').|||

It seems the problem was the statistics of the table.

Thanks for the help.

Shai.

How can I use SQL reporting Services to get a dynamic dataset from another web service as my rep

I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking

I think this technical article may help you.

http://msdn2.microsoft.com/en-us/library/aa964129.aspx

Simone

|||Thank you very much, this is almost exactly what I was looking for, not 100% but closer then I have been for weeks now. I just need to try and figure out now if I can send a parameter to a web service now to get the result set I need. Again, thanks...