Many Queues and one Activation Procedure for that queues.

Hi, All!

Could you please help me in my problem?

I develop Service Broker applcation, SB has 20 queues, also I have one Activation Procedure which carries out following actions:

CREATE PROCEDURE proc_ms_Activation

AS

BEGIN

WAITFOR (RECEIVE TOP(1) message_type_name, message_body, conversation_handle, message_id FROM Queue1)

-- process message, save statisctisc and etc

END

Problem in that: when I execute command WAITFOR (RECEIVE TOP (1) message_type_name, message_body, conversation_handle, message_id FROM Queue1) I should specify a name concrete Queue, for example "... FROM Queue1 ". I would like to use one procedure for processing all Queue.

How I can design my application that one Activation Procedure processed messages from all Queues?

Thank a lot for help.

You can figure out what queue you were activated for by looking in sys.dm_broker_activated_tasks and filter for the current session (spid = @.@.SPID). Then use database_id and queue_id to lookup the queue name is sys.service_queues and execute the WAITFOR (RECEIVE... ) as dynamic SQL.

BTW, If you run into EXECUTE AS context restrictions (like inability to see the records in servel level views) see this article: http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx Simples fix is to mark the database trustworthy.

HTH,
~ Remus

|||

Hi Svitlana!

What's the reason that you have one activated stored procedure for the whole 20 queues? Because when you get different message types on each of the 20 queues, then it makes (for me) no sense to use just one activated stored procedure that handles all message types. Why you don't use one activated stored procedure for each queue?

Thanks

Klaus Aschenbrenner
www.sqljunkies.com/weblog/klaus.aschenbrenner
www.csharp.at

|||

Thanks for the shown interest, excuse that long did not answer.

Remus, thanks a lot for help! you post is very useful.

Klaus, allow me to explain what's the reason that I have one activated stored procedure for the 20 queues.

In my application only one custom message type. Stored Procedures identical and also differ only Queue name. It would be convenient to have one stored procedure for all queues.

manual activation of RS

Hi
I'm having problems manually activating the report server after installation
on Windows 2000 server sp4 , SQL Server 2000 sp3 and Reporting Services sp1.
I have followed other fixes I have seen but still get the same error "The
Report Server Web service has not generated a public key"
I have done the following:
rskeymgmt -d
rsactivate -c"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config" -t
but still get the same error. Can anyone help?
Thanks,
JezHi jez,
From your descriptions, I understood that you encounter error message "The
Report Server Web service has not generated a public key" during your
Reporting Services installation. Have I understood you? Correct me if I was
wrong.
Based on my experience, RSActivate is only used for setting up web farms.
As the error states, the problem is most likely an unrelated failure when
the web service starts. You can get a more detailed description of the
underlying problem by navigating to http://localhost/reportserver.
Please give full permissions to IUSR, IWAM, and ASPNET users on the
following key using regedt32 and reinstalled SQL Server Reporting Services
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application
Let me know whether it works or you need more detailed steps about how to
perform these steps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Cheng,
Yes you have understood this correctly.
In the past I have successfully install RS on XP but now I'm trying to
install it on Windows 2000 Server and I run into this error. I have tried
to follow your advice but have observed the following:
1) When I try and access the page reportserver I get "HTTP 500 - Internal
Server error". I then checked that the configuration pointed to version
1.1.4322 of the .NET Framework which it did.
Then I looked at the Event Viewer and the only messages in there relating to
Reporting Services where entries every 12 hours by the "Schedule and
Delivery" source recording "The ReportServer service has been activated"
2) regedit - On Windows 2000 Server I do not get a "permissions" option when
I right click the registry key as you would on XP. I have checked under the
"Local User and Groups" heading in the Computer Management console and noted
that these users belonging to the following groups:
IUSR - Guest
IWAM - Guest
ASPNET - Administrators; Guest; VS Developers; Web Applications
I then added IUSR and IWAM to the administrators group and reinstalled RS
but still get the same error.
Can you suggest what I should do next?
Thanks,
Jez
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:uzEVYw0CFHA.2768@.cpmsftngxa10.phx.gbl...
> Hi jez,
> From your descriptions, I understood that you encounter error message "The
> Report Server Web service has not generated a public key" during your
> Reporting Services installation. Have I understood you? Correct me if I
> was
> wrong.
> Based on my experience, RSActivate is only used for setting up web farms.
> As the error states, the problem is most likely an unrelated failure when
> the web service starts. You can get a more detailed description of the
> underlying problem by navigating to http://localhost/reportserver.
> Please give full permissions to IUSR, IWAM, and ASPNET users on the
> following key using regedt32 and reinstalled SQL Server Reporting Services
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application
> Let me know whether it works or you need more detailed steps about how to
> perform these steps.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>|||Hi jez,
Expand the Registery Key to
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application
Right click "Application" in the Left Panel,
Click "Permissions..."
Add IUSR, IWAM and ASPNET full permissions explicitly
Let me know whether it works.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Cheng,
To administer the server I'm using NetMeeting or Terminal Services and
logging on as the local Administrator but when I right click "Application" I
do not see a Permissions option. The only choices I have are:
Collapse
New
Find
Delete
Rename
Copy Key Name
Are you sure that this option is avaible under Windows 2000 Server?
If it is then it must be the properties of NetMeeting or Terminal Services
but I just don't understand that?
Thanks,
Jez
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:Iwt07ORDFHA.644@.cpmsftngxa10.phx.gbl...
> Hi jez,
> Expand the Registery Key to
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application
> Right click "Application" in the Left Panel,
> Click "Permissions..."
> Add IUSR, IWAM and ASPNET full permissions explicitly
> Let me know whether it works.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Jez,
Oops, I didn't realize that you are using Windows 2000
So, please follow the steps below to open Regstry Editor
- Start -> Run -> "regedt32"
- Go to
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application
- Click Menu "Security" -> "Permissions"
Let me know whether it works this time.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Cheng,
User error on my behalf as I was using regedit and not regedt32!!! Have set
the permissions and reinstalled RS but still get the same error on
installation that reporting services requires manual activation.
Any ideas where to look next?
Many thanks for your help,
Jez
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:j6wYYTaDFHA.3152@.cpmsftngxa10.phx.gbl...
> Hi Jez,
> Oops, I didn't realize that you are using Windows 2000
> So, please follow the steps below to open Regstry Editor
> - Start -> Run -> "regedt32"
> - Go to
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application
> - Click Menu "Security" -> "Permissions"
> Let me know whether it works this time.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Cheng,
In the end I reinstalled the .Net 1.1 framework and then reinstalled RS and
it now all works!
Thanks for you help,
Jez
"jez" <jnel@.nospam.nospam> wrote in message
news:eSM45JcDFHA.3928@.TK2MSFTNGP15.phx.gbl...
> Cheng,
> User error on my behalf as I was using regedit and not regedt32!!! Have
> set the permissions and reinstalled RS but still get the same error on
> installation that reporting services requires manual activation.
> Any ideas where to look next?
> Many thanks for your help,
> Jez
> "Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
> news:j6wYYTaDFHA.3152@.cpmsftngxa10.phx.gbl...
>> Hi Jez,
>> Oops, I didn't realize that you are using Windows 2000
>> So, please follow the steps below to open Regstry Editor
>> - Start -> Run -> "regedt32"
>> - Go to
>> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application
>> - Click Menu "Security" -> "Permissions"
>> Let me know whether it works this time.
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>|||Hi Jze,
Thanks for your kindly letting me know about this, it's great to hear that
it!
Based on my experience, setting the permission will do resolve this issue,
anyway, it's great to hear that it works now. If you have any questions or
concerns next time, don't hesitate to let me know. We are always here to be
of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.