Identify Queue That Activated Sp ?

Sep 10, 2006

Hi there

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?

Thanx

View 1 Replies


ADVERTISEMENT

Why Does The Condition Activated = Isnull(@activated, -1) Work?

Jul 16, 2005

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?

View 2 Replies View Related

Clarifications On Queue Service And Queue Readers

Jan 11, 2006

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.

Thank you very much for the time,

Lubomir

View 5 Replies View Related

Activated Proc Not Firing.

Mar 13, 2007

Hi There

I just had the following scenario.

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 ?

Thanx

View 4 Replies View Related

Stored Procedure Activated More Than Once

Nov 23, 2006

Hi guys!

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?

Thanks a lot! :)

View 4 Replies View Related

User List With First Activated Offer

Nov 27, 2013

I have 3 tables, please find the table structure and sample data below

Table 1 : tblUserInfo

UserID | FirstName | Email | JoinedDate

1 Testuser01 User01@User.com 10-10-2013
2 Testuser10 User10@User.com 11-10-2013
3 Testuser20 User10@User.com 11-10-2013

Table 2 : tblOffers

OfferID | OfferName | ExpiryDate

1 OfferSample1 15-10-2014
2 OfferSample2 15-9-2014
3 OfferSample3 10-07-2014

Table 3 :tblOfferActivated

ActivationID | UserID | OfferID | ActivationCode | ActivatedDate

1 2 3 ABC 11-11-2013
2 2 1 CEG 13-11-2013
3 3 1 JHG 18-11-2013
4 3 2 KIU 20-11-2013

Expected Output

I want to list out the users with the first activated offer details.The OfferName Should be based on the first activated date

UserID | FirstName | Email | JoinedDate | OfferID | OfferName |ActivatedDate | ActivationCode

1 Testuser01 User01@User.com 10-10-2013 Null Null Null Null
2 Testuser10 User10@User.com 11-10-2013 3 OfferSample2 11-11-2013 ABC
3 Testuser20 User10@User.com 11-10-2013 4 offerSample3 18-11-2013 JHG

View 6 Replies View Related

Dynamic Code Activated By Sp_executesql Can Anyone Help Me With That One?

Apr 2, 2008

Hi everyone
I try to run Dynamic sql wherby sp_executesql as follows:



Code Snippet
DECLARE @params NVARCHAR(4000)
DECLARE @portion INT
SET @portion=6
DECLARE @mydynamic NVARCHAR(4000)
SELECT @mydynamic = ' SELECT TOP @portion * FROM server.database.dbo.mytable'
SELECT @params = N'@portion INT '
EXEC sp_executesql @mydynamic,@params, @portion






I get the following error message:



Code Snippet
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@portion'.




Any idea what is wrong with that code?Thanks

View 6 Replies View Related

Activated Stored Proc Blocking?

Aug 24, 2006

Hi There

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.

Does anyone know ?

Thanx

View 3 Replies View Related

Cannot Debug Activated CLR Stored Procedure

Jan 11, 2007

Hi

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.

Can anyone help?

Thanks

View 3 Replies View Related

Set New_broker When The Activated Stored Procedure Changes.

Sep 4, 2007

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..

Thank you for any helps!
Marina B.

View 3 Replies View Related

Ending A Conversation In A CLR Activated Stored Procedure

Apr 26, 2006

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.

Thanks

View 5 Replies View Related

Activated Stores Procedure And Batch Processing

Aug 2, 2006

Hi There

2 Questions :

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 ?

Thanx

View 3 Replies View Related

Is There A Way To Know If Execution Of Activated Stored Proc Is Already Finished?

Oct 19, 2006

Hi everyone! Good day!

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.

Thanks so much!

View 4 Replies View Related

Transact SQL :: Get Activated Data For A Date Range?

Jun 29, 2015

I've data like below:

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.

View 4 Replies View Related

Event Notifications Don't Fire When Raised By Activated Procedure

Oct 23, 2006

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.

View 2 Replies View Related

Service Broker Activated Procedure Getting Deadlock While Running Mulitple Queue_reader Threads

Mar 7, 2008

Hi,

I am getting deadlock on activated procedure which I am using to receive message from the Service Broker Queue.

Deadlock details:

Two threads are tring to do delete on internal table queue_messages_122847900 ends up in a dead lock.

Activated procedure code

RECEIVE TOP(1) @xmlMessage = message_body,
@handle = conversation_handle,
@message_type = message_type_name
FROM TransactionQueue;

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.

Thanks

View 1 Replies View Related

Conversation Handle Processed By An Activated Stored Procedure Service Can Not Be Invoked By A CLR Service Instance?

Dec 1, 2006

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.

View 7 Replies View Related

How To Identify A Dll

Apr 17, 2008



Hi,

How would I know that a dll component I built is in .NET framework 1.0 or 1.1??

annej

View 4 Replies View Related

Identify Neg And Pos Values

May 7, 2003

How do l select a negative value from a column and set it to 'C' and if its a positive value set it to 'D' for debit

l've written it in layman's terms .Is it wise to use substring or they is a better method......

select
case
when amount is (negative) then 'C'
when amount is (positive) then 'D'
else
end

from table_Tran

View 2 Replies View Related

SHOWCONTIG - Need A Bit Of Help To Identify If

Oct 11, 2007

Greetings.

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.

View 9 Replies View Related

How To Identify An Array

Nov 20, 2006

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.

View 1 Replies View Related

How To Identify Hot Tables

Jul 20, 2005

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.

View 2 Replies View Related

How To Identify The Fixpack Level?

Jul 21, 2004

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?

tnks

View 3 Replies View Related

Identify Value In A Column Nullified

Jan 20, 2006

hi,

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????



Thanks,
srini

View 2 Replies View Related

How To Identify SP Used By Front End Application

Sep 24, 2012

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?

View 6 Replies View Related

Statement To Identify Certain Fields

Sep 19, 2013

I am extract data from one of our Sql servers with the code below

Code:
Select SectDesc, PrGDesc, 'Product Description'=SetDescF, ProdSet, ProdSect, SectChar, Prdct.ProdGrp, OrigPr

From Prdct

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...

View 6 Replies View Related

Identify Backup Files

Apr 4, 2008

How to identify the backup files, whether the backup files were taken using SQL 2000 or SQL 2005?


Advance thanks,
Ravi

View 1 Replies View Related

Identify Potential Duplicate

Apr 22, 2015

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

[code]...

View 7 Replies View Related

Identify What Tables Are Being Access In SQL

Jul 20, 2005

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.

View 4 Replies View Related

SQL To Identify The Primary Key In A Table?

Oct 24, 2007

Hi

I have been looking for a way to identify the primary key defined in a
table using a SQL Sentence...how can i do it?

Thanx for your support!!!

Diego Bayona

View 5 Replies View Related

How To Identify What Consumes Cpu In Sql Server

Jun 1, 2007

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.

Thank you, Gene.

View 3 Replies View Related

Need To Identify Column In Resultset

May 10, 2007

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?



Thanks for the information.

View 1 Replies View Related

How To Identify A Dialog As Encrypted?

Oct 3, 2006

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?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved