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);
I am looking for an example of a SP that shows the best practices for internal activation? In BOL this topic describes the typical patter for reading messages from a queue. What is the typical pattern for reading messages from a queue using an internally activated SP? Do we still need to loop (considering the message arrival actually fired the sp)?
I'm a Chinese fan of Microsoft SQL Server 2005. Through studying for MS SQL Server 2005, now I have a question about €˜internal activation of service broker€™.
At the moment I have done a test. The description of test is as following:
Firstly I set the status of the activation to be off (ALTER QUEUE [dbo].[TargetQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = OFF , PROCEDURE_NAME = [dbo].[Usp_HelloWorld] , MAX_QUEUE_READERS = 5 , EXECUTE AS N'dbo' )). And filled the queue with100000 Messages. Then that running another application caused the usage of the CPU achieve 100%. Secondly reset the status of the activation to be on and monitored the €˜sys.dm_broker_activated_tasks€™. The instance of the stored procedure got the maximum number 5 very quickly. The server was much slower at current.
The SQL Server 2005 Books Online says: €™an activated stored procedure typically processes one or more messages and returns a response to the service that originated the message or messages. When messages arrive faster than the stored procedure processes messages, Service Broker starts another instance of the stored procedure, up to the maximum number defined by the queue.€? But it doesn€™t mention the performance counter of the server, for example: If the usage of the CPU is very high, the queue readers should be as few as possible, even don't process the message of queue until the system is idle. Because a client choice service broker means he don€™t care immediately process message, contrarily he care the throughput of the system and don€™t bother the natural application.
So my question is whether the strategy of internal activation of service broker doesn€™t care the performance counter of the server, just care the speed of process message. And the priority of queue monitor in SQL Server€˜s internal processes. Unfortunately I can€™t find any information about these from books online and Internet.
I am using internal activation on a queue to process the messages, should an error be encountered I call stored procedure A in the same database to log the error. Part of the processing in stored procedure A is a call to stored procedure B in another database (on the same server), however I have not been able to get this call to B to work. Currently I get the error "The server principal XXXXXX is not able to access the database YYYYYYY under the current security context".
I have tried various combinations (too many to remember) of database owners, roles and permissions as well as EXECUTE AS on both A and B and the Queue but none seem to work. Can anyone give me simple example of a setup which would allow this cross database call to work?
New to service broker, and my books haven't come in yet. I'm trying to figure out how Internal Activation works.
The following code seems to "work", in terms of the inline code, but it doesn't seem to be going through the activation PROCs. Is there something else I need to set or do?
TIA, Bob
USE master;
GO
-- Ensure Service Broker functionality is enabled.
ALTER DATABASE ICEBOX SET ENABLE_BROKER;
GO
USE ICEBOX;
GO
-- Drop all sample objects if present.
IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsReceiveService') DROP SERVICE BobsReceiveService;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsReceiveQueue') DROP QUEUE BobsReceiveQueue;
IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsSendService') DROP SERVICE BobsSendService;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsSendQueue') DROP QUEUE BobsSendQueue;
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = N'BobsContract') DROP CONTRACT BobsContract;
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsSendMessageType') DROP MESSAGE TYPE BobsSendMessageType;
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsReceiveMessageType') DROP MESSAGE TYPE BobsReceiveMessageType;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_NAME = 'USP_BobsReceiveQueueMonitor') DROP PROCEDURE dbo.USP_BobsReceiveQueueMonitor;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_NAME = 'USP_BobsSendQueueMonitor') DROP PROCEDURE dbo.USP_BobsSendQueueMonitor;
GO
CREATE PROCEDURE dbo.USP_BobsSendQueueMonitor
AS
DECLARE @QueueTable TABLE
(status tinyint --Status of the message. For messages returned by the RECEIVE command, the status is always 1. Messages in the queue may contain one of the following values: 0=Received message1=Ready2=Not yet complete3=Retained sent message
,queuing_order bigint --Message order number within the queue.
,conversation_group_id uniqueidentifier --Identifier for the conversation group that this message belongs to.
,conversation_handle uniqueidentifier --Handle for the conversation that this message is part of.
,message_sequence_number bigint --Sequence number of the message within the conversation.
,service_name nvarchar(512) --Name of the service that the conversation is to.
,service_id int --SQL Server object identifier of the service that the conversation is to.
,service_contract_name nvarchar(256) --Name of the contract that the conversation follows.
,service_contract_id int --SQL Server object identifier of the contract that the conversation follows.
,message_type_name nvarchar(256) --Name of the message type that describes the message.
,message_type_id int --SQL Server object identifier of the message type that describes the message.
,validation nchar(2) --Validation used for the message. E=Empty N=None X=XML
,message_body varbinary(MAX) --Content of the message.
,message_id uniqueidentifier --Unique identifier for the message.
);
-- FROM BoL
-- The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id.
-- The search condition may not contain any of the other columns in the queue.
-- The conversation_handle or conversation_group_id may not be an expression.
DECLARE @SendDialog_Handle uniqueidentifier;
DECLARE @SendMsg varbinary(MAX);
DECLARE @SendMsgName nvarchar(256);
BEGIN
PRINT 'SEND' + CONVERT(VARCHAR,GETDATE(),109);
SELECT TOP (1)
@SendDialog_Handle = conversation_handle
,@SendMsg = message_body
,@SendMsgName = message_type_name
FROM
BobsSendQueue;
SELECT 'SEND',@SendDialog_Handle AS conversation_handle,CAST(@SendMsg AS VARCHAR(MAX)) AS message_body,@SendMsgName AS message_type_name;
END;
GO
CREATE PROCEDURE dbo.USP_BobsReceiveQueueMonitor
AS
DECLARE @QueueTable TABLE
(status tinyint --Status of the message. For messages returned by the RECEIVE command, the status is always 1. Messages in the queue may contain one of the following values: 0=Received message1=Ready2=Not yet complete3=Retained sent message
,queuing_order bigint --Message order number within the queue.
,conversation_group_id uniqueidentifier --Identifier for the conversation group that this message belongs to.
,conversation_handle uniqueidentifier --Handle for the conversation that this message is part of.
,message_sequence_number bigint --Sequence number of the message within the conversation.
,service_name nvarchar(512) --Name of the service that the conversation is to.
,service_id int --SQL Server object identifier of the service that the conversation is to.
,service_contract_name nvarchar(256) --Name of the contract that the conversation follows.
,service_contract_id int --SQL Server object identifier of the contract that the conversation follows.
,message_type_name nvarchar(256) --Name of the message type that describes the message.
,message_type_id int --SQL Server object identifier of the message type that describes the message.
,validation nchar(2) --Validation used for the message. E=Empty N=None X=XML
,message_body varbinary(MAX) --Content of the message.
,message_id uniqueidentifier --Unique identifier for the message.
);
-- FROM BoL
-- The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id.
-- The search condition may not contain any of the other columns in the queue.
-- The conversation_handle or conversation_group_id may not be an expression.
DECLARE @ReceiveDialog_Handle uniqueidentifier;
DECLARE @ReceiveMsg varbinary(MAX);
DECLARE @ReceiveMsgName nvarchar(256);
BEGIN
PRINT 'RECEIVE' + CONVERT(VARCHAR,GETDATE(),109);
SELECT TOP (1)
@ReceiveDialog_Handle = conversation_handle
,@ReceiveMsg = message_body
,@ReceiveMsgName = message_type_name
FROM
BobsReceiveQueue;
SELECT 'RECEIVE',@ReceiveDialog_Handle AS conversation_handle,CAST(@ReceiveMsg AS VARCHAR(MAX)) AS message_body,@ReceiveMsgName AS message_type_name;
END;
GO
CREATE MESSAGE TYPE BobsSendMessageType VALIDATION = NONE;
CREATE MESSAGE TYPE BobsReceiveMessageType VALIDATION = NONE;
CREATE CONTRACT BobsContract
(BobsSendMessageType SENT BY INITIATOR
,BobsReceiveMessageType SENT BY TARGET
);
CREATE QUEUE BobsSendQueue
WITH ACTIVATION
(STATUS = ON
,PROCEDURE_NAME = dbo.USP_BobsSendQueueMonitor
,MAX_QUEUE_READERS = 1
,EXECUTE AS OWNER
)
;
CREATE SERVICE BobsSendService ON QUEUE BobsSendQueue;
CREATE QUEUE BobsReceiveQueue
WITH ACTIVATION
(STATUS = ON
,PROCEDURE_NAME = dbo.USP_BobsReceiveQueueMonitor
,MAX_QUEUE_READERS = 1
,EXECUTE AS OWNER
)
;
CREATE SERVICE BobsReceiveService ON QUEUE BobsReceiveQueue (BobsContract);
GO
DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;
DECLARE @SendMsg NVARCHAR(100);
SET @SendMsg = N'Hello Bob';
BEGIN TRANSACTION;
BEGIN DIALOG CONVERSATION @BobsDialog_Handle FROM SERVICE BobsSendService TO SERVICE N'BobsReceiveService' ON CONTRACT BobsContract WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @BobsDialog_Handle MESSAGE TYPE BobsSendMessageType (@SendMsg);
COMMIT TRANSACTION;
SELECT 'Conversation started on Handle ' + CAST(@BobsDialog_Handle AS VARCHAR(36));
GO
DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;
DECLARE @ReceiveMsg NVARCHAR(100);
DECLARE @ResponseMsg NVARCHAR(100);
DECLARE @ReceiveMsgName SYSNAME;
SET @ResponseMsg = N'Back at ya';
BEGIN TRANSACTION;
RECEIVE TOP(1)
@BobsDialog_Handle = conversation_handle
,@ReceiveMsg = message_body
,@ReceiveMsgName = message_type_name
FROM
BobsReceiveQueue;
COMMIT TRANSACTION;
SELECT @ReceiveMsgName AS ReceiveMsgName,@ReceiveMsg AS ReceiveMsg;
SEND ON CONVERSATION @BobsDialog_Handle MESSAGE TYPE BobsReceiveMessageType (@ResponseMsg);
END CONVERSATION @BobsDialog_Handle;
GO
DECLARE @ResponseMsg NVARCHAR(100);
DECLARE @ResponseMsgName SYSNAME;
DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;
BEGIN TRANSACTION;
RECEIVE TOP(1)
@BobsDialog_Handle = conversation_handle
,@ResponseMsg = message_body
,@ResponseMsgName = message_type_name
FROM BobsSendQueue;
END CONVERSATION @BobsDialog_Handle;
COMMIT TRANSACTION;
SELECT @ResponseMsgName AS ResponseMsgName, @ResponseMsg AS ResponseMsg;
GO
IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsReceiveService') DROP SERVICE BobsReceiveService;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsReceiveQueue') DROP QUEUE BobsReceiveQueue;
IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsSendService') DROP SERVICE BobsSendService;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsSendQueue') DROP QUEUE BobsSendQueue;
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = N'BobsContract') DROP CONTRACT BobsContract;
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsSendMessageType') DROP MESSAGE TYPE BobsSendMessageType;
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsReceiveMessageType') DROP MESSAGE TYPE BobsReceiveMessageType;
I have MS SQL Express installed on my local machine and I have problems runnig some unit tests. Precisely, an exception is thrown as follows:
System.Data.SqlClient.SqlException : The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
Nevertheless, everythin is ok on the server, where MS SQL Developer Edition is installed.
"Could not impersonate the client during assembly file operation."
The CLR function is invoked from Service Broker internal activation stored procedure.
"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.
SqlContext.WindowsIdentity.Name is "NT AUTHORITYSYSTEM" as the Data Engine runs with the LocalSystem account.
How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?
Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".
I use Try ... catch blok in my activation stored procedure. When SQL Server raise error (e.g. Primary key violation) in Try blok, XACT_STATE in Catch blok has value 1 = commitable transaction and I can use rollback transaction to savepoint. But when I use Raiserror() in Try blok, XACT_STATE in Catch blok has value -1 = uncommitable transaction and I can't use rollback transaction to savepoint. When I drop automatic activation for given queue and I run this stored procedure with Raiserror(), XACT_STATE has value 1 = commitable transaction.
What a problem may cause this different behavior ?
I am trying to find a solution to my woes creating CLR activation stored procedure that references Enterprise library data access dlls. Does anyone know best practices to deploy .NET 2.0 assemblies as SQLCLR. I am using VS2005 SqlClrProject to deploy my .NET 2.0 assemblies to Sql Server and it fails with error such as "Assembly was not found in the sql catalog". If I manually try to load a referenced assembly within SqlServer by browsing to the target location, like for example, System.Management.dll it fails with similar error, trying to resolve its dependencies. Even if I manage to walk through the dependency tree and resolve it to the final dependent dll, I am only able to load it in "UNSAFE" mode. Any recommendations, suggestions and feedback are welcome.
I've read already every thread in this forum regarding this topic with no results.
SQLServer and the corresponding services are at actual update level. The reporting services worked for 4 months without this problem. Suddenly, most of the time all reports return an internal error (it is like 70-30 for internal error) not depending which render format is used.
The system logs (eventlog, iis) show no errors. The ExecutionLog of RS show rsInternalError.
Looking in the log files in RS log directory, there is following stacktrace:
I was testing around with a sample service broker app using activation, and came across an interesting question. The little app sends a series of four messages to a queue, either on the same conversation or on seperate ones. Each message invokes one stored procedure in my activation procedure. All the procedure does is enter a record into a test table and then wait for an allotted amount of time. In my example, the first message called a proc that waited 20 sec, the 2nd one that waited 10 seconds, the third 5 seconds, and the 4th 1 second. I am using internal activation on the queue. It seemed that in both scenarios (sending on 4 separate conversations and on one conversation) the procedures executed "almost" sequentially. "Almost" meaning that the first procedure was done before the last one started executing. It makes sense to me that this would happen where I sent them on the same conversation, but not really when I sent them on 4 seperate ones. Is it because when I call a procedure from my activation procedure it locks the queue so that another message cannot be processed (I'm processing a message at a time)? How could I make it so that the 4th procedure (the one that only waits 1 second) returns before the 1st procedure (the one that waits 20 seconds)?
One is Basket_ODS and the other is Intelligence_ODS. I am using service broker activation on a queue to move data from the Basket_ODS table to the Intelligence_ODS database. Previously I was able to move from table to table in Basket_ODS, however now that I am moving it to another database on the same instance it is no longer working.
If I set my active connection in SQL Management Studio to this user(BrokerUser) and execute the "move" procedure it works. When activated by Service Broker however, it does not. Here is the error message:
2006-05-09 14:47:52.940 spid86s The activated proc [ODS].[ProcessOrderQueue] running on queue Basket_ODS.ODS.Order Process Queue output the following: 'The server principal "BrokerUser" is not able to access the database "Intelligence_ODS" under the current security context.'
I'm sure I missed something becasue it works fine in the same database. BrokerUser has datareader and datawriter in both databases.
We are trying to use xp_cmdshell commands in service broker. If I kick off the process without activation, the process succeeds. If I enable activation, the tasks with xp_cmdshell fail.
The xp_cmdshell task is either a echo command so that users know that we are processing a database, or a file copy.
I used the TechNet article by Roger Wolter to create the queues and stored procedures.
We will be working across domains that have one-way trusts.
I specify to execute as 'dbo' in the alter queue statement. I also define the activated sp to execute as 'dbo'.
But i keep getting permission errors from my activated sp. i have tried to excute as 'sa' , i have even tried to write a job that excutes to stored procedure but it also get weird errors. Bottom line if i exceute the sp in management studio logged in as sa it works , but thorugh activation or a job nothing works, as 'dbo' or 'sa'.
I need to change the Activation Key for a copy of SQL Server 2000 that I already have installed and up and running. I have the new key but can't figure out how to change from the old, no longer valid, key.
How can I change keys w/o dumping the DB, uninstalling, and reinstalling all of my instances?
I have seen the posting on determining how activation has failed and looking through the system logs is very helpful in determining why activation is not occuring, however, short of looking through the SQL Server logs is there another way to get the same information? Access to the SQL Server logs is fairly restricted. Does anyone know another way that this can be done? I have used the execute as technique described in the article on "Troubleshooting Activation Stored Procedures" and found it helpful in some cases.
So is there something I'm supposed to configure to allow activation to fire after a cluster failover. We have had three where I have noticed that activation does not automatically fire back up. i have to alter the queue to get it going. None of the values on sys.service_queues imply that is disabled, but messages just keep stacking up into the queue and are not being dealt with. Once I alter the queue to turn activation on; everything takes off and starts working normaly.
Obviously, this is less than desireable for an environment that requires High availability.
Newbie question, but is there a way for me to set up a thread in my c# code so as it sleeps until it gets woken up by an event fired by an activation SP?
As in queue sits idle, thread sleeps. Queue receives a message, activation SP gets fired, activation SP raises an event which gets caught by event handler in code, which wakes up thread to do processing?
Currently I have a PC that has MSDE installed on it and is attached to database (MyData.MDF and a log file MYLog.LDF) located on its hard drive at c:data. When I detach from the database, place a copy of the two files noted above on my network drive @ u:data and try to attach I get the following error€™s:
SQL[1] exec error = -1: Changed database context to 'master'. €œDevice activation error. The physical file name u:dataMyData.MDF may be incorrect.€?
I have done some testing a have found that I can attach to a copy of my database if I move it anywhere on the c: drive, and or even to a 1Gb USB key attached to the system(e:). So far it seems to only be an issue if I move it to a mapped network drive. If anyone could please provide me with any info it would be greatly appreciated.
I'm having problems with activation. I have a CLR stored procedure that runs fine when run directly (and consumes messages from the queue). When I try to enable activation nothing happens. I've tried different execute as options and looked in the sql server logs and I don't see anything indicating why.
I've checked sys.service_queues and all options look correct including activation options. The sys.dm_broker_activated_tasks view is empty.
Without any errors I'm having difficulty tracking down the problem. I've seen references to service broker shutting down the procedure if it doesn't consume any messages but I haven't seen where this is indicated.
I have send a msg successful,but the proc failed. I altered the proc to correct the problem in the proc. The msg are still in the queue. Is there a manual way to excute the proc again to process the msg in the q? or do I wait for service broker to do it after a retry time (self recover)?
I have a set of service broker services setup that rely on external activation to process messages. I'm using the GotDotNet ExternalActivator, and it launches console applications that do the actual retrieval from the queues. The console applications are written to run continuously to avoid the cost of starting up .NET based console apps over and over again.
I am observing very odd timing behavior. With the receive queues empty and the external activator configured to run a minimum and maximum of 5 instances, I observe in SQL Profiler that most of the receive operations finish in about the same amount of time as my WAITFOR command in my receive stored procedure. However, there is usually one receive command that consistently takes upwards of 30 seconds and often causes sql timeout exceptions to be thrown. I know that I could code around this, but I wasn't really expecting this behavior.
Does anyone have any thoughts on why it might be occurring? I would have expected to routinely see my receive operations taking 15 seconds, give or take, especially when the queue is empty. Also, I have observed this behaviour on both SQL 2k5 Express and Dev Editions, so I don't think it's a version thing.
The stored procedure I am using to do the receive is:
BOL only seems to say that you can do it w/o really showing how, and the ExternalActivator sample at gotdotnet.com contains so much functionality I'm not sure what's required just for the external activation. Are there any docs or samples out there that focus on how to do it w/o obscuring the matter with a bunch of other functionality? (I prefer docs to project samples, b/c the samples tend to have hacks like hardcoded paths and connection strings so that they rarely work correctly right out of the box.)
I will have a variety of different types of work that will come into my Service Broker queue and I'll likely have a stored procedure or two for each of the different types of work (ie. move order header, move items, move payment, etc.) What is required to be done in each of these steps may vary by the subsidiary and type of order coming in. My plan is to use exclusively stored procedures but to execute them dynamically using sp_executesql. I think I should use sp_executesql because that way I can have a config file (in xml) that I can store what stored procedures need to be called for which unit of work/order type/subsidiary. If I do this I should be able to easily configure each type of work to be done in a config file and let Service Broker handle the execution dynamically. As long as I keep the parameters the same for each of the stored procedures (I'm thinking maybe 4 or 5 parameters) and passing them to each of the stored procedures, this approach will allow me to dynamically configure Service Broker to do what it is supposed to do. I can pull what needs to be done out of the message that comes in with an XQuery expression on the config file. I know that I will have to configure my user (activation user) to be able to run sp_executesql and the security may be complex (especially since I'm using certificates). I can not use trusted databases. Are there any other considerations I should think about?
OK, so assume I am recycling dialogs in my client code, and assume I am doing something similar to get a dialog handle in my TSQL. What should the activated stored procedure that is processing my queue look like if I am expecting thousands of messages per second? Assume also that there is a small bit of logic need to process each individual message? I am building for a high-throughput scenario and would like to get as much as possible out of each second-tier service broker server as possible before the aggregated data is moved up the chain to a master. The first tier is Express on a web server and exists primarily only as a forwarding mechanism.
I have an application that is set up using Service Broker to pass messages between services asynchronously. I am using event-based external activation and have successfully set up my event notification in SQL Server so if a message appears on any of my Service Broker queues, I'm getting the activation event from SQL Server sent to my activation service.
The problem that I am seeing is that every time I am posting a message onto a Service Broker queue, I am losing the event notification entry in the sys.event_notifications view and I'm not receiving my activation event notifications. When I execute the CREATE EVENT NOTIFICATION T-SQL statement to recreate the event notification, I'm getting the event notification immediately (since there are messages on the queues being monitored). The event notification appears to be registered until the next message is posted on the queue.
I have two databases Basket_ODS and Intelligence_ODS.
I created a user in the Basket_ODS and Intelligence_ODS databases as follows:
USE Basket_ods
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*******'
CREATE USER BasketServiceUser WITHOUT LOGIN
ALTER AUTHORIZATION ON SERVICE::[Order Send] TO BasketServiceUser
GRANT CONTROL ON SERVICE::[Order Send]
TO BasketServiceUser
CREATE CERTIFICATE BasketServiceCertPriv
AUTHORIZATION BasketServiceUser
WITH SUBJECT = 'ForBasketService'
BACKUP CERTIFICATE BasketServiceCertPriv
TO FILE = 'BasketServiceCertPub'
In the other database...
I created the following:
USE Intelligence_ODS
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '************
USE Intelligence_ODS
GO
CREATE USER BasketServiceUser WITHOUT LOGIN
CREATE CERTIFICATE BasketServiceCertPub
AUTHORIZATION BasketServiceUser
FROM FILE = 'BasketServiceCertPub'
My Queue is in BASKET_ODS and is set up as:
ALTER QUEUE ODS.[Order Process Queue] WITH
ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = ODS.ProcessOrderQueue,
MAX_QUEUE_READERS = 4,
EXECUTE AS 'BasketServiceUser'
)
I have performed the following grants in Basket_ODS
grant execute on ODS.ProcessOrderQueue to BasketServiceUser
ProcessOrderQueue calls [ODS].[MoveOrderTotals_Core] in the Intelligence_ODS database.
grant execute on [ODS].[MoveOrderTotals_Core] to BasketServiceUser
ProcessOrderQueue proc is set as follows:
ALTER procedure [ODS].[ProcessOrderQueue]
WITH EXECUTE AS 'BasketServiceUser'
[ODS].[MoveOrderTotals_Core] is set up as follows:
when I run ProcessOrderQueue I get an error message:
ALTER procedure [ODS].[MoveOrderTotals_Core](@Orderid uniqueidentifier)
with execute as 'BasketServiceUser'
I just don't understand when I run ProcessOrderQueue I get the following error message (when database trust is turned off)
The server principal "sa" is not able to access the database "Intelligence_ODS" under the current security context.
Can you help me figure out what I'm doing wrong. I've spent so much time on this security stuff. Is there another way to do this that is more straight forward without using database trust?
I am kinda curious how the rest of you are doing your error handling inside your activation stored procedures...best practices says you should not rollback the part of your transaction that receives the record off of the queue...but using a try...catch block will only allow you to rollback the entire transaction. I tried using savepoints and starting the try...catch after the savepoint and the proc still gives me the error: "
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction." when the error scenario is encountered.
I tried using @@Error and checking for errors at the statement level which would allow me to do a partial rollback, but the type of errors I receive (i.e. invalid data types etc) are aborting the entire batch instead of passing the error and continuing.
Can other people on this forum using service broker give me an idea of how you are getting around this issue?
except for saying "Recieve Top(0)" which didn't make any sense to me. I have set the database to trustworthy (all of this is taking place within a single database on a local server). There are messages on the queue, and I have the queue activation set to max_queue_readers=2, procedure_name=StoredProcedure1, execute as owner. I tried execute as self and that didn't work either. I signed the assembly that contains StoredProcedure1 and the assembly that it references. The only thing that appears in the sql error log is this (I trimmed off the timestamp):
AppDomain 15 (TriggerTest.dbo[runtime].14) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations. AppDomain 15 (TriggerTest.dbo[runtime].14) unloaded. AppDomain 18 (TriggerTest.dbo[runtime].17) created.
If I call the stored procedure manually it works just fine
I have implemented the code from the 'Recycling Conversations' post that Remus Resanu has posted. For some reason my sender activation procedure never gets called. I thought it was working at one point but now can not get it to work. Messages are being sent correctly from sender to receiver but the c_audit_send_queue_activation procedure never gets called.
According to my code that calls 'begin conversation timer(@dlg) timeout=30;' I would think that after 30 seconds that my activated procedure would get called with a DialogTimer message but it does not. Nor does the activation procedure on the sender get called when I manually end conversations on the receiver side.
Thanks in advance for any help.
Here is my send procedure:
Code Snippet create procedure [dbo].[c_audit_p_send_message] @msg nvarchar(max) as begin if @msg is not null begin begin try set nocount on; declare @dlg uniqueidentifier declare @counter int; declare @error int; declare @errid bigint, @dbname nvarchar(128) set @counter = 1; begin transaction; while(1=1) begin select @dlg = dialog_id from dbo.audit_dialog with(holdlock) where audit_dialog_id_X = @@spid if @dlg is null begin begin dialog conversation @dlg from service [tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender] to service '//TyMetrix360Audit/DataWriter','7A9690F7-11A5-4ABB-ACBA-EECC1A58ACB7' on contract [//TyMetrix360Audit/Contract] with encryption = off; begin conversation timer(@dlg) timeout=30; insert into dbo.audit_dialog(audit_dialog_id_X, dialog_id) values(@@spid, @dlg) end; send on conversation @dlg message type [//TyMetrix360Audit/Message] (@msg) set @error = @@ERROR; if @error = 0 begin break; end set @counter = @counter + 1; if @counter > 10 begin raiserror(N'Failed to SEND on a converstation for more than 10 times.',16,1) with log; insert into audit_error (error_procedure, error_line, error_number, error_message, error_severity, error_state, audited_data) select error_procedure(), error_line(), error_number(), error_message(), error_severity(), error_state(), @msg break; end delete from dbo.audit_dialog where dialog_id = @dlg; set @dlg = null; end commit transaction; end try begin catch insert into audit_error (error_procedure, error_line, error_number, error_message, error_severity, error_state, audited_data) select error_procedure(), error_line(), error_number(), error_message(), error_severity(), error_state(), @msg select @errid = scope_identity(), @dbname = db_name() raiserror (N'Error while sending Service Broker message to TyMetrix360Audit. Error info can be found in ''%s.dbo.AUDIT_ERROR'' table with id: %I64d', 16, 1, @dbname, @errid) with log; end catch end end GO
here is the activatation procedure:
Code Snippet create procedure [dbo].[c_audit_p_send_queue_activation] as begin declare @dlg uniqueidentifier declare @msgtype sysname declare @msg varbinary(max) begin transaction; receive top(1) @dlg = conversation_handle, @msgtype = message_type_name, @msg = message_body from dbo.TyMetrix360AuditQueue if @dlg is not null begin delete from audit_dialog where dialog_id = @dlg if @msgtype = N'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer' begin send on conversation @dlg message type [//TyMetrix360Audit/Message/EndConversation] (''); end else if @msgtype= N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' begin end conversation @dlg end else if @msgtype = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' begin end conversation @dlg declare @error int; declare @description nvarchar(4000); with xmlnamespaces ('http://schemas.microsoft.com/SQL/ServiceBroker/Error' as ssb) select @error = cast(@msg as xml).value('(//ssb:Error/ssb:Code)[1]', 'int'), @description = cast(@msg as xml).value('(//ssb:Error/ssb:Description)[1]', 'nvarchar(4000)') raiserror(N'Received error Code:%i Description:''%s''', 16, 1, @error, @description) with log; insert into audit_error (error_procedure, error_line, error_number, error_message, error_severity, error_state, audited_data) select error_procedure(), error_line(), error_number(), error_message(), error_severity(), error_state(), @msg end end commit transaction; end
and here is the code that creates the service broker on the sender:
Code Snippet if exists (select * from sys.routes where name = 'TyMetrix360Route') drop route TyMetrix360Route if exists (select * from sys.services where name = N'tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender') drop service [tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender] if exists (select * from sys.service_queues where name = N'TyMetrix360AuditQueue') drop queue TyMetrix360AuditQueue if exists (select * from sys.service_contracts where name = N'//TyMetrix360Audit/Contract') drop contract [//TyMetrix360Audit/Contract] if exists (select * from sys.service_message_types where name = N'//TyMetrix360Audit/Message') drop message type [//TyMetrix360Audit/Message] if exists (select * from sys.service_message_types where name = N'//TyMetrix360Audit/Message/Blob') drop message type [//TyMetrix360Audit/Message/Blob] if exists (select * from sys.service_message_types where name = N'//TyMetrix360Audit/Message/EndConversation') drop message type [//TyMetrix360Audit/Message/EndConversation] GO create route TyMetrix360Route authorization dbo with service_name='//TyMetrix360Audit/DataWriter', broker_instance='7A9690F7-11A5-4ABB-ACBA-EECC1A58ACB7', address='TCP://SFT3DEVSQL01:4022' create message type [//TyMetrix360Audit/Message] validation=none; create message type [//TyMetrix360Audit/Message/Blob] validation=none; create message type [//TyMetrix360Audit/Message/EndConversation] validation=none; create contract [//TyMetrix360Audit/Contract]([//TyMetrix360Audit/Message] sent by initiator, [//TyMetrix360Audit/Message/Blob] sent by initiator, [//TyMetrix360Audit/Message/EndConversation] sent by initiator); create queue dbo.TyMetrix360AuditQueue alter queue dbo.TyMetrix360AuditQueue with activation(status=on,max_queue_readers=1,procedure_name=[c_audit_p_send_queue_activation],execute as owner); create service [tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender] authorization dbo on queue dbo.TyMetrix360AuditQueue grant send on service::[tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender] to public GO
