I have multiple queues with the same activated stored procedure (for various reasons we are trying this scenario).
My biggest obsticle is i cannot figure out a way to determine with the activated sp which queue caused it to activate.
Basically i need to make the sp dynamic, so that no matter which queue activated the sp the sp can determine the queue name and use that dynamically to do the receive command from the right queue.
I am sure it is possible since sys.dm_broker_activated_tasks shows how many sp's are activated by each queue, however the sp name is the same for all queues so that does not help me.
How do i determine within an activated sp which queue caused it to activate?
I have a stored procedure into which a bit type of parameter called '@activated' is being passed. This stored procedure gets all the users matching the condition : Activated = isnull(@activated, -1)
In users table, one of the columns is 'Activated' of bit type. The strange thing happening here is that if @activated is null, then the above condition is true for all rows in users table, even though we know that the column Activation is either 1 or 0 only and never -1.Can someone explain why this strange thing is happening?
Hello, This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:
A. When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back? 1. Message is left on the Queue. 2. is the worker procedure triggered again for the same message by the queue? 3. I am hoping the Queue keeps on triggering workers until it is empty.
My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.
B. For my scenario messages are independent and ordering does not matter. Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.
I checked sys.service_queues to confirm the my queue has an activated proc assigned to it and is activation_enabled.
I send messages to the service, however i see that the messages just sit in the queue. I run profiler with all SB and Error events there is nothing, i check the sql server log, there is also nothing.
So i have no idea why the activated proc is not firing, all i see in the profiler trace is that the messages are acknowledged but the activated proc does not execute.
For a while i was stumped. i then tried to execute the proc myself and i got a syntax error.
Basically what happened was that i altered a table that the proc used and now the proc cannot execute cos there are more columns that must be inserted so i altered the proc.
However the activated proc still did not fire.
I had to disable and re-enable activation for it to work.
What i am wondering is why was there no indication in profiler or the sql og as the the fact that something was wrong with the activated proc ? Surely the activated proc should have fired and given the same error i got either to the sql log or profiler ?
And why did i have to re-enable activation ? Does sql cache the proc when you activate it and not know that the proc has changed when you alter it ?
Is it possible for a stored procedure to be activated more than once even if a message is sent only once? After sending the message, I check the sys.dm_broker_activated_tasks and see that it activates more than once. Is there a way for me to prevent this from happening?
I was wondering, i have experienced alot of stored procedure blocking where you have a stored procedure that get executed very frequently and is complex, when the sp has to recompile it is locked and cannot be executed by other processes.
How will this work with an activated stored procedure, our activated stored procedure will probably not be too complex but it may exec complex ones base on the message type.
Now lets say that a message is recieved by the activated sp he in turn executes a complex sp to process the message this causes the complex sp to recompile, during this time another activated sp is spawned by the queue with the same message type he execs the same complex sp that is busy recompiling, i presume that the second activated sp will be blocked until the sp has completed recompiling.
Is this correct, because on a very busy queue with very complex sp's being called by the activated stored procedure, the benefit of having multiple queue readers is negated by the blocking caused by the recompiling sp's called by the activated sp.
Basically no matter how many queue readers kick in to handle the messages they all wait for the sp to recompile from the first message.
I am hoping multiple spawned activated stored procedures somehow over come this? Or work differently somehow.
I'm having a problem debugging (with VS2005,) a CLR Stored Procedure which is activated on a queue. I know it is working because it is consuming and processing messages when one appears in the queue.
I can debug it 'manually' by either right-clicking on the SP within VS2005 and selecting 'Step into Stored Procedure' or by attaching to the SQL Server Management Studio process, setting a breakpoint and executing the stored procedure from a Management Studio query window.
However, if I send a message to my queue within Management Studio, my breakpoints are NOT being hit within VS2005, but the message IS reaching my queue and it IS being processed by the SP.
Hi I have a couple of questions to understand better Service Broker..
Every time I alter the activation Stored Procedure on the receiving queue I also execute the: alter database set new_broker rollback
1) can somebody explain me in a detailed way why I have to do that? what happen inside SQL Server when there is a Stored Procedure activated on a queue?? Also link to white papers, resouces on so on are appreciated.
My Activation Stored Procedure calls other stored procedure. 2) If ONLY one of the nested stored procedures changes I have to execute the command: alter database set new_broker rollback ???
I think that the answer to the first question will answer the second one..
I've read Remus' article on Fire and Forget tactics when it comes to ending conversations, and I have to admit I am guilty of sending a message to a service and immediately ending the conversation. I've set up a stored procedure to end the conversation on the initiator queue, but I'm guessing it's never being fired because I am not ending the conversation in the target activated stored proc, which is a CLR stored proc.
Can you tell me how to end the conversation from a CLR stored proc?
Now that I think of it none of the code I use in my TSQL activated stored procs to handle different message types and error-checking is done in the CLR stored proc.
1. Almost in every SB example you will see this sql :
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP (1)
@MessageType = message_type_name,
@Message = message_body
FROM [Queue1]
WHERE conversation_handle = @ConversationHandle
), timeout 5000;
If this sql in an activated sp do you really have to have the waitfor ? Since the sp will only be fired if there is a message on the queue ?
2. It is reccomended that for high volume SB apps you do not do a top(1) receive but process batches. Exactly what is the best practice to do this. Receive a batch into a table variable and then what ? Process through it with a cursor ? That is not very efficient either, i would just like some insight into batch queue processing as everywhere i have seen uses top (1) from the queue ?
I'm not really sure if my question should fall on Service Broker or T-SQL, but I hope someone helps me with this... After activating the stored procedure assigned to the queue, is there any way for me to find out if the stored procedure is already finished executing?
I have successfully sent messages to my queue but I have no way to know if all the processing is already done.
Now, I've to get active data for a particular date range. Let me explain the active data definition as below:
StartDate : 01-Jul-2015 EndDate : 31-Dec-2015
It should return all the data which was active for that date range even if it was only for one day.If no data found for that date range, check the last record before start date and and if its active then it should be returned else not.
I though of creating a function and pass primary key with date range and return the final status but that doesn't seems like an optimized query.
I have an activated SP that is issuing PRINT statements.
These are raised as ErrorLog and EventLog events when using SQLProfiler
However if you attach an event notification for ERRORLOG and EVENTLOG, nothing appears in the queue. Any other ERRORLOG or EVENTLOG event caught by profiler is caught by the event notification as expected.
IF (@message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog') BEGIN END CONVERSATION @handle; RETURN 0 END ......................... ......................... After this I do process the message and some other processing
And then
END CONVERSATION @handle;
Note I do have single conversation group
Is their a problem in the way I am receiving and processing messages. Is it possible because of the delay between RECEIVE and END CONVERSATION same message is read by two different threads.
I have a initiator and a target service broker peer.
Both are controlled by a C# unit test. The initiator uses the Microsoft.Samples.SqlServer class. The target service uses stored procedure activation.
Sending a message from the initiator to the target, saves the content of the message, along with its conversation handle in the target's database specific table.
The unit test needs - at a later time - to instruct the target to send a message back on the same conversation handle to the initiator service.
For this the C# unit test creates a Conversation off of the saved conversation handle:
Service client = new Service("cleintservicename", conn, tran);
Conversation dialog = null;
dialog = new Conversation(client, convHandle); Sending the message on this dialog generates an error "Message body: <Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8495</Code><Description>The conversation has already been acknowledged by another instance of this service.</Description></Error>". Is the error due to the fact that a service - using the activated stored procedure already picked up the conversation, so that a new reference to the service can not be created through the Service class in CLR? If so, I might need then to skip the activated stored procedure in favor or a CLR service, alltogether? Any help - greatly appreciated.
I'm currently on a company with an ms sql server 2000. I'm looking into the indexes and tables to see if there are some bottlenecks there but the LogicalFramentation is very low in the index I have searched.
However, this table has a logicalFragmentation of 99,9215698242188 which I get when I do DBCC SHOWCONTIG ([TInsurance]) WITH TABLERESULTS. Is that a value to be trusted or not to be trusted since this does not check an index? If it is, how do I defrag a table? I know only how to defrag an index. (example: DBCC INDEXDEFRAG (MFSSEK,[TInsurance], PK_InsuranceID) )
Tipps, suggestions, help, all is very wellcome! :-)
DBCC SHOWCONTIG scanning 'TInsurance' table... Table: 'TInsurance' (2051694557); index ID: 0, database ID: 17 TABLE level scan performed. - Pages Scanned................................: 1275 - Extents Scanned..............................: 225 - Extent Switches..............................: 224 - Avg. Pages per Extent........................: 5.7 - Scan Density [Best Count:Actual Count].......: 71.11% [160:225] - Extent Scan Fragmentation ...................: 74.67% - Avg. Bytes Free per Page.....................: 520.0 - Avg. Page Density (full).....................: 93.58% DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I have seen several examples explaining the fact that a tablecontaining a field for each day of the week is for the most part anarray. An specific example is where data representing worked hours isstored in a table.CREATE TABLE [hoursWorked] ([id] [int] NOT NULL ,[location_id] [tinyint] NOT NULL,[sunday] [int] NULL ,[monday] [int] NULL ,[tuesday] [int] NULL ,[wednesday] [int] NULL ,[thursday] [int] NULL ,[friday] [int] NULL ,[saturday] [int] NULL)I had to work with a table with a similar structure about 7 years agoand I remember that writing code against the table was pretty close toHell on earth.I am now looking at a table that is similar in nature - but different.CREATE TABLE [blah] ([concat_1_id] [int] NOT NULL ,[concat_2_id] [int] NOT NULL ,[code_1] [varchar] (30) NOT NULL ,[code_2] [varchar] (20) NULL ,[code_3] [varchar] (20) NULL ,[some_flg] [char] (1) NOT NULL) ON [PRIMARY]The value for code_2 and code_3 will be dependently null and they willrepresent similar data in both records (i.e. the value "abc" can existin both fields) . For example if code_2 contains data then code_3 willprobably not contain data.I do not think that this is an array. But with so many rows wherecode_2 and code_3 will be NULL something just does not feel right.I will appreciate your input.
Does anyone know how to identify the hottest, most active tables in adatabase?We have hundreds of users hitting a PeopleSoft database with hundredsof tables. We are I/O bound on our SAN, and are thinking of puttingthe hottest tables on a solid state (RAM) drive for improvedperformance. Problem is: which are the hottest tables? Would like todo this based on hard data instead of developer/vendor guesses.Any suggestions are much appreciated.
hi, for sql server 2000, how can we find the fixpack(service pack) level installed on this sql server? is there any command, or any gui tool to identify the level?
I have a huge db with many services ,users and applications hitting the db. Suddenly one of our column is nullified , we are not able track who /how it is done,
Can any one tell be whatz the best way to identify this???? trace(what events to select ), trigger or what????
there is a Crystal Report run from the Front end Application. The DB used here is SQL Server 2008. I need to know the Stored Procedure used by the report that is been run from the front end. . How shall I do it?
INNER JOIN qryPRDGroupDets on Prdct.ProdGrp=qryPRDGroupDets.PGCode
where supersed ='' And OrigPr Not Like '9%' And OrigPr Not Like '%MDM%' And LISTPR1>'0' And STANCOST>'0'
Which works fine, but what I need to do is reference the "OrigPr" field and mark it as "valid" or "Invalid", the "OrigPr" the field contains alpha numeric data e.g. A000, A001, A002 - ZZ99 and so on, amongst all of the potential different types of codes we have codes that end in treble Zero (0) e.g. A000 which are valid, but if they end in double 00 e.g. AA00 then this is invalid, the problem I have is I can't just add
Code: 'Marker'= Case When Right(OrigPr, 2) = '00' Then 'Invalid' ELSE 'Valid' End
For it will mark the A000 as invalid, is there a way of getting around this...
There is one report to identify potential duplicate in a table and it is performing poor.I'm now tuning the existing SP and got struck in modifiying it. rewrite the query in a best way. I just pasted below an example of query which is now in a report.The report will be run every week currently the table has 10 million records, and every week there will 5k to 10k will be added up so with that 5k to 10 k we have to check all the 10 miilion rows that if it is duplciated the logic is (surname = surmane or forename = forename or DOB =DOB )
Create table #employee ( ID int, empid varchar(100), surname varchar(100), forename varchar(100), DOB datetime, empregistereddate datetime, Createdate datetime
The database I'm currently working with is very old and some of thetables, SP, and views are not being used. I'm looking for a way toidentify what items are no longer in uses, or what items arecurrently in use.
Hi all I use 64 bit 2005 server with 8cpu and 8G of memory. This server is accessed by large number of intensive or not so intensive programs. I had eliminated all inefficient queries by means of sql profiler. What I see now is 30 procs or so runining in 1 second. They are all pretty simple and as I said use indexes. cpu column for most show 0, reads show 10 - 50 - pretty good. But... my cpu utilization is 75% in avg. across of all 8 cpu's. I really can't find an answer for it. If procs run so efficient, where does cpu go? Disk queue length is 0.04 or less - seems very good. Task manager shows that all of it 75% attributed to sql server. So which resources besides sql queries use so much cpu? Do I have to look at some other areas and which ones where cpu could be used besides sql queries themselves.
Okay, I now have some dynamic SQL working. This is the SQL statement I have for a report in Reporting Services:
DECLARE @SQL nvarchar(4000)
SET @SQL=(SELECT AdHocSQL
FROM RptValueTypeMap
WHERE RptValueTypeMap.SectionCd in ('ITEM0010'))
EXECUTE (@SQL)
We have a table set up that actually holds different SQL statements based on the report items. This is reading the SQL statement from AdHocSQL for the Report item #0010 and it is returning the results. However, it does return the correct value, but under (No Column Name). I have tried to incorporate an "AS", but I get errors when I try this.
I am familiar, but new to SQL statements and I would like this to return a field so I can use this value in the report. What do I need to do?
I am trying to lookup a dialog from conversation_endpoints, however if a dialog was created with the encryption setting to ON and thereis no master Key in the database then the record put in the conversation_endpoints is the same as one without encryption.
How can I distinguish between the one requested with no ecryption and requested with encryption but setup with none due to the lack of a key?
