More Problems With Activation
May 9, 2006
I 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
View 7 Replies
ADVERTISEMENT
May 1, 2007
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 Related
Oct 11, 2006
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
View 2 Replies
View Related
Sep 11, 2006
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
View 11 Replies
View Related
Mar 7, 2007
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?
View 4 Replies
View Related
May 1, 2006
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
View 5 Replies
View Related
May 5, 2007
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
View 9 Replies
View Related
Nov 28, 2006
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
View 1 Replies
View Related
Apr 24, 2007
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.
View 1 Replies
View Related
Aug 15, 2007
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?
View 1 Replies
View Related
Apr 5, 2007
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.
View 4 Replies
View Related
Oct 23, 2006
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 Related
Jan 12, 2006
Hi
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
View 4 Replies
View Related
Aug 17, 2006
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.
View 6 Replies
View Related
Jun 1, 2006
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!
View 4 Replies
View Related
Oct 6, 2006
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.
View 1 Replies
View Related
Oct 31, 2006
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:
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
View 6 Replies
View Related
Feb 13, 2007
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
View 1 Replies
View Related
May 12, 2006
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
View 1 Replies
View Related
Apr 9, 2007
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.
View 1 Replies
View Related
Aug 29, 2006
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
View 1 Replies
View Related
May 25, 2006
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?
View 3 Replies
View Related
May 23, 2007
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?
View 3 Replies
View Related
Aug 2, 2006
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
View 6 Replies
View Related
Jan 25, 2008
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
View 3 Replies
View Related
Nov 1, 2006
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.
View 3 Replies
View Related
Nov 7, 2006
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
View 1 Replies
View Related
Jul 20, 2007
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?????
View 3 Replies
View Related
Nov 8, 2006
rajesh writes "Hi All
When i attach my production database i got an device activation Error
cannot
attach database."
View 2 Replies
View Related
Jul 23, 2005
When running the following:C:Program FilesMicrosoft SQL Server80ToolsBinn>rsactivate -c"C:Program FilesMicrosoft SQLServerMSSQLReportingServicesReportServerRSRepo rtServer.config" -tI get the following error:Failure starting the web service:System.Exception: The local NT Service is disabled 0x80090005 --->System.Exception: 0x80090005--- End of inner exception stack trace ---atMicrosoft.ReportingServices.RSActivate.RSActivate. RpcActivateService(Int32clientType)at Microsoft.ReportingServices.RSActivate.RSActivate. InstanceMain()at Microsoft.ReportingServices.BaseCmdLine.CommandLin eMain(String[]args, BaseCmdLine instance)Any insight into this. What am I doing wrong.
View 1 Replies
View Related
Jul 18, 2007
Hello,
I have a strange error that occurs if I run ALTER QUEUE with status = OFF on my receiving queue...
Purpose of the test was to check that when the queue get disabled all my event notification set up works fine ..
I found out that the Event Notification will not be thrown if I call ALTER QUEUE with status = OFF on a queue that has not messages inside but If I run anyway ALTER QUEUE with status = OFF I found out that the receive activation Stored Procedure is called and because the code is wrapped around a
BEGIN TRY
...
END TRY
BEGIN CATCH
RAISEERROR (.........) WITH LOG
END CATCH
the SP goes in the catch block and actually I see my Application event log full of entries that contains:
The conversation handle is missing. Specify a conversation handle.'
First at all:
why the activated stored procedure is called if I execute:ALTER QUEUE with status = OFF ?
Thanks
Marina B.
View 2 Replies
View Related
Sep 16, 2006
I am attempting to use Service Broker to distribute message processing across multiple machines. I have mulitple queues setup to all notify a single service with the QUEUE_ACTIVATION event. My external application then sits in a "WAITFOR RECEIVE" on that event queue and when it receives a message for a particular queue, will begin processing the messages in that queue if that particular server is configured to process messages of that type.
My problem is that once one server starts receiving messages, the others don't always start processing and I end up with one server attempting to process all the messages by itself.
What is the best way to notify multiple servers that there are messages in a particular queue which need processing? I really like the WAITFOR clause since it eliminates the need to poll the server intermittently, however I also don't want to have each server sit in a WAITFOR RECEVE on every queue to avoid polling, I could easily end up with around a thousand connections sitting in that state. I also don't know how many servers there will be processing messages, so I'd like to avoid having to setup an event queue for each server.
(Remus, if you read this, I was at the SQL Performance Lab in building 35 about a month ago and we spoke about using Service Broker in our application.)
View 4 Replies
View Related
Mar 7, 2006
Hi all
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?
Thanks
Ian
View 6 Replies
View Related
