Good day,
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)?
thank you in advance.
I set up a queue to activate a clr stored procedure upon receiving a message and nothing is happening. I have tried everything listed in this topic:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=200684&SiteID=1
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 am trying to raise an event using sp_trace_generateevent in my activation stored proc. (dbo.ActivationSP)
EXEC sp_trace_generateevent @event_class = 82, @userinfo = N'Test Event'
There is a Service broker service listening to this event.
The problem is the event is getting fired whenever the activation SP is executed (could see in profiler) but the secondtargetqueue doesnt receive any messaages.
But if manually do a "EXEC sp_trace_generateevent", the secondtargetqueue receives a messaage.
Both the queues and sevices are in the same database.
The following are the code snippets
-- Code for queue on which the activation is working
CREATE QUEUE TargetQueue WITH STATUS=ON, ACTIVATION (PROCEDURE_NAME = dbo.ActivationSP,MAX_QUEUE_READERS = 5,Execute AS 'dbo') ;
Create Service ReceiverService ON QUEUE TargetQueue (SampleContract)
-- Code for Queue listening to event
Create Queue SecondTargetQueue WITH status= ON
Create Service SecondReceiverService ON QUEUE SecondTargetQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
CREATE EVENT NOTIFICATION TestNotification
ON SERVER FOR UserConfigurable_0 TO SERVICE 'SecondReceiverService', 'current database'
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.
Gary
Hello:
I am working with an application unde mssql 6.5 ,sp4. We have a separate
database for reporting on a different box. Each night we load a backup into
the database from the production database.
I set up a stored procedure to kill all users connected to this reporting
database just before the load.
THe job looks like this under scheduled tasks as a cmdexec:
isql -U sa -P -d master -Q"exec killusersall_sp 7" -o
F:MSSQLatchjobs
evdb.log
This job died with the following message:
Process Exit Code 1. ...[-b On error batch abort] [-O use Old ISQL
behavior disables the following]<EOF> batch processingAuto console width
scalingWide messagesdefault errorlevel is -1 vs 1 [-? show syntax summary
(this screen)]
The killusersall is paramaterize sp that I pass the dbid of the database
for whom I want all users killed. I need to put the stored procedure in the
master because I egt the spid from sysprocesses.
I do not understand why the job died. Does it matter if I named the stored
procedure as sp_killusersall instead of killusersall_sp which I used.
Can any one advise me about how to get the job to run? Any assistance will
be greatly appreciated. Thanks you.
David Spaisman
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)?
View 5 Replies View RelatedI have two databases on the same instance.
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.
Thanks for any help on this matter.
Gary
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.
Wayne
Hi There
Everything works 100% until i activate my sp.
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'.
???
Thanx
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?
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
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.
Regards
I've been experimenting with Service Broker and was surprised at one aspect of the design: the interface to Activation stored procedures.
I would have expected the queue to be a parameted passed to the procedure rather than having to hard code the queue query into the SP.
In a system with lots of queues it seems plausible that the same activation procedure might want to be used with several queues.
Any comments?
David.
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?
Hi All,
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.
Thanks.
James D.
Is there any way to increase the activation time, i.e. the time between service broker creating a new instance of the activation SP.
View 3 Replies View RelatedHi
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.
Any help appreciated.
Duncan Godwin
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)?
Any advice provided would be helpful.
Thanks!
Is there any way to identify the context by which a stored proc has been activated. i.e.
I want to use the same sp to end conversations on receipt of the end mesage from the target.
However I don't know how to find out which queue activated the SP.
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedI 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:
Create PROCEDURE [dbo].[P_RTD_MessageBase_Receive]
@receiving_queue_name varchar(255),
@receiving_time_out int
AS
Declare @receiveQuery nvarchar(300)
Set @receiveQuery = 'WAITFOR(RECEIVE * FROM [dbo].['+ @receiving_queue_name +']),
TIMEOUT ' + cast (@receiving_time_out as varchar)
Execute sp_executeSql @receiveQuery
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.)
TIA
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?
Gary
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.
Any ideas on what I'm doing wrong?
Thanks,
Michael
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?
Hi,
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
Hi, there,
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 have been going over the samples from PDC 2005 and trying to run the External Activator program in section 2 'CLR Services'. After sending a message to the Inventory Queue, I see that 'dm_broker_queue_monitors' table has a row in the table, with the state column shown as 'NOTIFIED', but the ExternalActivator program never gets notified of the event and therefore never launches the 'InventoryServer' program.
Querying the [ExternalActivatorQueue] shows no rows.
Querying the [Inventory Queue] shows messages are waiting to be received.
Executing the 'activator' command in the ExternalActviator program shows
Notification service 'ExternalActivator' on SQL Server 'DBSERVER' and Database 'In
ventory' is connected to the database and working.
Any Ideas?
Thanks,
-Kuo
Hi All,
I'm having some issues getting Database Mail to work with SQL Agent Job's which is doing my fruit !
I'm configuring the server using the following script:
DECLARE
@DataFile varchar(1000),
@LogFile varchar(1000),
@BackupFile varchar(1000),
@cmd Varchar(8000),
@ServerName varchar(1000),
@AccountName Varchar(1000),
@EmailAddress Varchar(1000),
@MailSubject VARCHAR(500),
@FullPath varchar(400),
@Drop varchar(100)
--Configure SQL Server DBMail
SET
@AccountName = (SELECT REPLACE(@@ServerName, '','-') + ' Email Profile')
SET
@EmailAddress = (SELECT REPLACE(@@ServerName, '','-') + '@OurDomain.com')
SET
@ServerName = (SELECT REPLACE(@@ServerName, '','-'))
IF EXISTS
(SELECT Name FROM msdb.dbo.sysmail_profile WHERE NAME = @AccountName)
BEGIN
PRINT 'Database Mail Profile Already Exists - Skiping Step'
END
ELSE
BEGIN
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@description = 'Mail account for administrative e-mail.',
@email_address = @EmailAddress,
@replyto_address = 'SQL_Notify@OurDomain.com',
@display_name = @ServerName,
@mailserver_name = 'MailServer.net' ;
-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @AccountName,
@description = 'Profile used for administrative mail.' ;
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @AccountName,
@account_name = @AccountName,
@sequence_number =1 ;
-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = @AccountName,
@principal_name = 'Public',
@is_default = 1 ;
EXEC
msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
EXEC
master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
EXEC
master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', @AccountName
SET
@MailSubject = 'Database Mail Test Message from ' + @@SERVERNAME
PRINT
'Created DBMail Profile'
END
GO
DECLARE @MailSubject VARCHAR(500)
DECLARE @AccountName Varchar(1000)
SET
@AccountName = (SELECT REPLACE(@@ServerName, '','-') + ' Email Profile')
EXEC
msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
EXEC
master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
EXEC
master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'Default Email Profile'
SET
@MailSubject = 'Database Mail Test Message from ' + @@SERVERNAME
--Check if DB Operator alredy exists; Create if not.
IF EXISTS (Select Name FROM MSDB.dbo.SysOperators WHERE NAME = 'SQL Notify')
BEGIN
PRINT 'Database Operator Already Exists - Skipping Step'
END
ELSE
BEGIN
EXEC msdb.dbo.sp_add_operator @name=N'SQL Notify',@enabled=1, @pager_days=0, @email_address=N'SQL_Notify@OurDomain.com'
END
EXEC
msdb.dbo.sp_send_dbmail
@profile_name = @AccountName,
@recipients = 'SQL_Notify@OurDomain.com',
@subject = @MailSubject,
@body = 'This is a Database Mail Test Message'
GO
OK - I get the test message, however when I setup a SQL Agent job later in the same script to alert me on failure; I dont get any mails through.
When I check the SysMail Event Log; I see the followign error:
error 2007-07-20 15:58:07.623 Activation failure. NULL NULL NULL 2007-07-20 15:58:07.623 sa
The SQLAgent is running under the same account as SQLServer; neither are local admin's on the machine. I've restatred SQLAgent using SSCM a number of times yet nothing comes through.
Mail Profile is started; Service Broker is enabled in MSDB
SQL Agent is configured with the correct profile; an operator is set as a failsafe operator.
Does anyone have any suggestions?????
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies View Relatedrajesh writes "Hi All
When i attach my production database i got an device activation Error
cannot
attach database."