Error Log Peppered With --&&> 'The Conversation Handle Is Missing. Specify A Conversation Handle.'
Dec 3, 2007
Hi
I'm using service broker and keep getting errors in the log even though everythig is working as expected
SQL Server 2005
Two databases
Two end points - 1 in each database
Two stored procedures:
SP1 is activated when a message enters the sending queue. it insert a new row in a table
SP2 is activated when a response is sent from the receiving queue. it cleans up the sending queue.
I have a table with an update trigger
In that trigger, if the updted row meets a certain condition a dialogue is created and a message is sent to the sending queue.
I know that SP1 and SP2 are behaving properly because i get the expected result.
Sp1 is inserteding the expected data in the table
SP2 is cleaning up the sending queue.
In the Sql Server log however i'm getting errors on both of the stored procs.
error #1
The activated proc <SP 1 Name> running on queue Applications.dbo.ffreceiverQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
error #2
The activated proc <SP 2 Name> running on queue ADAPT_APP.dbo.ffsenderQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
I would appreceiate anybody's help into why i'm getting this. have i set up the stored procs in correctly?
i can provide code of the stored procs if that helps.
thanks.
View 10 Replies
ADVERTISEMENT
Jan 18, 2008
We have implemented our service broker architecture using conversation handle reuse per MS/Remus's recommendations. We have all of the sudden started receiving the conversation handle not found errors in the sql log every hour or so (which makes perfect sense considering the dialog timer is set for 1 hour). My question is...is this expected behavior when you have employed conversation recycling? Should you expect to see these messages pop up every hour, but the logic in the queuing proc says to retry after deleting from your conversation handle table so the messages is enqueued as expected?
Second question...i think i know why we were not receiving these errors before and wanted to confirm this theory as well. In the queuing proc I was not initializing the variable @Counter to 0 so when it came down to the retry logic it could not add 1 to null so was never entering that part of the code...I am guessing with this set up it would actually output the error to the application calling the queueing proc and NOT into the SQL error logs...is this a correct assumption?
I have attached an example of one of the queuing procs below:
Code Block
DECLARE @conversationHandle UNIQUEIDENTIFIER,
@err int,
@counter int,
@DialogTimeOut int,
@Message nvarchar(max),
@SendType int,
@ConversationID uniqueidentifier
select @Counter = 0 -- THIS PART VERY IMPORTANT LOL :)
select @DialogTimeOut = Value
from dbo.tConfiguration with (nolock)
where keyvalue = 'ConversationEndpoints' and subvalue = 'DeleteAfterSec'
WHILE (1=1)
BEGIN
-- Lookup the current SPIDs handle
SELECT @conversationHandle = [handle] FROM tConversationSPID with (nolock)
WHERE spid = @@SPID and messagetype = 'TestQueueMsg';
IF @conversationHandle IS NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE [InitiatorQueue_SER]
TO SERVICE 'ReceiveTestQueue_SER'
ON CONTRACT [TestQueueMsg_CON]
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER ( @conversationHandle )
TIMEOUT = @DialogTimeOut
-- insert the conversation in the association table
INSERT INTO tConversationSPID
([spid], MessageType,[handle])
VALUES
(@@SPID, 'TestQueueMsg', @conversationHandle);
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
ELSE IF @conversationHandle IS NOT NULL
BEGIN
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
SELECT @err = @@ERROR;
-- if succeeded, exit the loop now
IF (@err = 0)
BREAK;
SELECT @counter = @counter + 1;
IF @counter > 10
BEGIN
-- Refer to http://msdn2.microsoft.com/en-us/library/ms164086.aspx for severity levels
EXEC spLogMessageQueue 20002, 8, 'Failed to SEND on a conversation for more than 10 times. Error %i.'
BREAK;
END
-- We tried on the said conversation, but failed
-- remove the record from the association table, then
-- let the loop try again
DELETE FROM tConversationSPID
WHERE [spid] = @@SPID;
SELECT @conversationHandle = NULL;
END;
View 2 Replies
View Related
Aug 30, 2007
Hi
I have a replicated table that has a trigger attached to the it. The trigger fires off a service broker message for inserts. Originally for every insert, I would begin a conversation, send, and end the conversation when target send an end conversation. Since replication process is only using a single spid, I would like to reuse 1 conversation. the following is what I have for the send procedure in the initiator. I check the conversation_endpoints for any open conversation, if it's null, I start a new conversation and send else just send with the existing conversation. Is there anything wrong with this code? What could cause the conversation on the initiator to be null if I never end the conversation on the initiator side? thanks
DECLARE @dialog_handle uniqueidentifier
select @dialog_handle = conversation_handle from sys.conversation_endpoints where state = 'CO'
IF @dialog_handle is NULL
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [initiator]
TO SERVICE 'target'
ON CONTRACT [portcontract];
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [Port] (@msg)
View 1 Replies
View Related
Nov 29, 2005
within the context of the sys.conversation_endpoints system view, there is:
View 3 Replies
View Related
Feb 8, 2008
Hi,
I have implemented Remus Resanu's implementation from the Recycling Conversations article and I am experiencing locking issue when I try to insert new conversation handles to the SessionConversations table. I have copied the code in the article exactly including the activation procedure. Any ideas why I may be locking. I am thinking it is related to the HOLDLOCK hint on the table.
The sepcific line where I see locking is directly from the article:
INSERT INTO [SessionConversations] (SPID, FromService, ToService, OnContract, Handle) VALUES (...etc)
Thanks
View 6 Replies
View Related
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
Apr 19, 2006
Hi:
My service broker was working perfectly fine earlier. As I was testing...I recreated the whole service broker once again.
Now I am able to get the message at the server end from intiator. When trying to send message from my server to the intiator it gives this error in sql profiler.
broker:message undeliverable: This message could not be delivered because the Conversation ID cannot be associated with an active conversation. The message origin is: 'Transport'.
broker:message undeliverable This message could not be delivered because the 'receive sequenced message' action cannot be performed in the 'ERROR' state.
How do I proceed now ?
Thanks,
Pramod
View 14 Replies
View Related
Sep 18, 2006
Q1. Model Prediction -- Suppose we already have a trained Microsoft Linear Regression Mining Model, say, target y regressed on two variables:
x1 and x2, where y, x1, x2 are of datatype Float. We try to perform Model Prediction with an Input Table in which some records consist of NULL x2 values. How are the resulting predicted y values calculated?
My guess:
The resulting linear regression formula is in the form:
y = constant + coeff1 * (x1 - avg_x1) + coeff2 * (x2 - avg_x2)
where avg_x1 is the average of x1 in the training set, and avg_x2 is the average of x2 in the training set (Correct?).
I guess that for some variable being NULL in the Input Table, Microsoft Linear Regression just treat it as the average of that variable in the training set.
So for x2 being NULL, the whole term coeff2 * (x2 - avg_x2) just disappear, as it is zero if we substitute x2 with its average value.
Is this correct?
Q2. Model Training -- Using the above example that y regressed on x1 and x2, if we have a train set that, say, consist of 100 records in which
y: no NULL value
x1: no NULL value
x2: 70 records out of 100 records are NULL
Can someone help explain the mathematical procedure or algorithm that produce coeff1 and coeff2?
In particular, how is the information in the "partial records" used in the regression to contribute to coeff1 and the constant, etc ?
View 1 Replies
View Related
Sep 18, 2006
Q1. Model Prediction -- Suppose we already have a trained Microsoft Linear Regression Mining Model, say, target y regressed on two variables:
x1 and x2, where y, x1, x2 are of datatype Float. We try to perform Model Prediction with an Input Table in which some records consist of NULL x2 values. How are the resulting predicted y values calculated?
My guess:
The resulting linear regression formula is in the form:
y = constant + coeff1 * (x1 - avg_x1) + coeff2 * (x2 - avg_x2)
where avg_x1 is the average of x1 in the training set, and avg_x2 is the average of x2 in the training set (Correct?).
I guess that for some variable being NULL in the Input Table, Microsoft Linear Regression just treat it as the average of that variable in the training set.
So for x2 being NULL, the whole term coeff2 * (x2 - avg_x2) just disappear, as it is zero if we substitute x2 with its average value.
Is this correct?
Q2. Model Training -- Using the above example that y regressed on x1 and x2, if we have a train set that, say, consist of 100 records in which
y: no NULL value
x1: no NULL value
x2: 70 records out of 100 records are NULL
Can soemone help explain the mathematical procedure or algorithm that produce coeff1 and coeff2?
In particular, how is the information in the "partial records" used in the regression to contribute to coeff1 and the constant, etc ?
View 3 Replies
View Related
Jan 15, 2008
Hello,
How can I find the cause on the error that marked one conversation with status 'ER' in sys.conversation_endpoints?
Thanks in advance.
View 1 Replies
View Related
Nov 3, 2015
I am developing one SSIS package where I bring some details from DWH and insert ii to a table in SQL Server.
But i am getting the error like - The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_I4" and "DT_GUID".
View 2 Replies
View Related
Oct 12, 2007
Hi,
I already had a thread for same problem but didn't recieved any response in last 4-5 months so I created a new thread for this
I am using service broker in between two database servers. following is the way i am sending and receiving messages
Send
BEGIN TRAN
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE @SendService
TO SERVICE @ReceiveService
ON CONTRACT @Contract
WITH LIFETIME = @lifetime;
SEND ON CONVERSATION @handle
MESSAGE TYPE @xmlMessageType(@xmlMessage);
COMMIT
Receive
BEGIN TRAN;
RECEIVE TOP(1) @xmlMessage = message_body,
@handle = conversation_handle,
@message_type = message_type_name
FROM TransactionQueue;
----------------------------------------------------------------------------------------------------
-- Check to make sure a message was returned to process. In theory this should never happen.
----------------------------------------------------------------------------------------------------
IF @@rowcount = 1
BEGIN
IF @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @handle;
COMMIT
RETURN 0
END
IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
RAISERROR(N'Received error %s from service [Target]', 10, 1)
END CONVERSATION @handle;
COMMIT
RETURN 0
END
SET @sql = 'EXEC '+@message_processor_name+' @xml'
BEGIN TRAN
EXEC sp_executesql @sql, N'@xml XML', @xml=@xmlMessage
COMMIT TRAN
END CONVERSATION @handle;
END
COMMIT
I see Messages are delivered to the target every thing working fine other than following errors which i am seeing in profiler.
1) "This message could not be delivered because the conversation endpoint has already been closed." I see this error on initiator end. Is it like ending conversation on initiator end when i get "EndDialog" send an acknowledgement, which cannot be recieved by target as it has already ended conversation.
2) "An error occurred while receiving data: '64(The specified network name is no longer available.)'." I don't have much idea about the reason for this error. But in profiler i see value for GUID is different for this error and the real message.
Let me know if you need any other information
View 2 Replies
View Related
Apr 1, 2007
Is it possible to catch and error and then keep the process going in a stored procedure?
So if an update encounters a primary key violation on a row, is it possible to skip that row and keep the process going?
View 4 Replies
View Related
Oct 21, 2007
Hi! I have some try .. catch block trying to insert some data into database. During its action duplicate key row insert error could raise, for example. The question is how could I know distinguish it from other sql errors? Object ex (Catch ex As Exception) has only message property '{"Cannot insert duplicate key row in object 'dbo.Group_Courses' with unique index 'IX_Group_Courses'.The statement has been terminated."}' and type System.Data.SqlClient.SqlException. Knowing the type of error is not enough, because there are different SqlExceptions. Even the message is not unique for this error, because now i deal with 'dbo.Group_Courses' and then it could be other table. Is there something that unique identifies each error? For example error code. If it exists, where could I get it?
Thanks in advance!
View 2 Replies
View Related
Jan 13, 2006
Hi,
I would like to handle a sql error in t-sql and return a certain value in case error occurs. For example if I would like to add a record I want to return a certain identity value or maybe a status of transaction (0 for incomplete, 1 for succesfull trans).
If error occurs in sql I cannot return any values back to asp.net because of What I am doing at the moment is catching an error in asp.net and then displaying an error message. Is there a way to return only a return value to asp.net and somehow handle the error in t-sql?
Thanks
View 1 Replies
View Related
May 6, 2008
Hi,
How to capture the Error Msg 8114 in sql server 2000.
Below is the sample code i have used.
CREATE TABLE Test
(
Column1 int IDENTITY,
Column2 int NOT NULL
)
USE tempdb
go
ALTER PROCEDURE CHK_INSERT
@Column2 int
AS
DECLARE @ExecQuery nVARCHAR(4000)
set @ExecQuery='INSERT test VALUES ('+cast(@Column2 as varchar)+')'
EXEC (@ExecQuery)
IF @@ERROR <>0
BEGIN
PRINT 'Error Occured'
END
EXEC CHK_INSERT Test
Regs,
priya
View 9 Replies
View Related
Mar 13, 2006
I have the next sp:
CREATE PROCEDURE SP_SALDO
(
@strDatos char(50)
)
AS
declare @ID char(20)
declare @Montodecimal(9,2)
declare @SaldoActualdecimal(9,2)
DECLARE @TipMov tinyint
declare @Fechachar(11)
declare @Horachar(10)
declare @Tiendatinyint
declare @NoCajatinyint
declare @SaldoNuevo decimal(9,2)
declare @NoAutoint
DECLARE @NomEmp char(40)
DECLARE @Trans_Num numeric
set nocount on
--SET @strDatos='T23C02001000000000000000000020025047'
SET @ID= SUBSTRING(@strDatos , 10, 20 )
set @Monto= cast(SUBSTRING(@strDatos , 30, 7 ) as decimal(9,2))/100
set @TipMov= cast(SUBSTRING(@strDatos , 7, 3 ) as int)
set @Tienda= cast(SUBSTRING(@strDatos , 2, 2 ) as int)
set @NoCaja= cast(SUBSTRING(@strDatos , 5, 2 ) as int)
set @Fecha = convert(char(11), getdate(), 101)
set @Hora= CONVERT(VARCHAR,getdate(),108)
set @NoAuto=ABS(CHECKSUM(NewID())) % 100000 + 1
--Se guarda el monto Actual y el nombre en variables
SElect @SaldoActual = SALDO, @NomEmp = NOM_EMP, @Trans_Num=last_trans_num +1
FROM TBSALDOS
WHERE ID=@ID
--select @SaldoActual,@ID,@Monto,@TipMov,@Tienda, @NoCaja, @Fecha, @Hora,@NoAuto
if (@Monto<=@SaldoActual)
begin
set @SaldoNuevo=@SaldoActual-@Monto
insert TBMOVIMIENTOS
values(@trans_num,@ID,@TipMov,GETDATE(),@hora,@Tienda,@NoCaja,@SaldoActual,@Monto,@NoAuto)
Update TBSALDOS
SET SALDO=@SaldoNuevo, SALDO_ULT_MOV=@Monto,
DT_ULT_MOV=GETDATE(), HR_ULT_MOV=@Hora,
NO_TIE_ULT_MOV=@Tienda, NO_CAJA_ULT_MOV=@NoCaja,
last_trans_num=@Trans_Num
WHERE ID=@ID
SELECT 'OK'+ SUBSTRING(@strDatos , 1, 6 )+@id
+RIGHT(REPLICATE('0',7) + CAST(cast(@SaldoNuevo*100 as int) as varchar(10)) ,7)
+ RIGHT(REPLICATE(' ',40) +@NomEmp ,40)
+ + RIGHT(REPLICATE('0',6) + CAST(cast(@NoAuto as int) as varchar(10)) ,6) as resp
end
else
begin
SELECT 'NO'+ SUBSTRING(@strDatos , 1, 6 )+@id
+RIGHT(REPLICATE('0',7) + CAST(cast(@SaldoActual*100 as int) as varchar(10)) ,7)
+ RIGHT(REPLICATE(' ',40) +@NomEmp ,40)
+ '000000' as resp
end
GO
How can I add a handle error?
What I need to do is if you can not do the insert and the update returns :
SELECT 'NO'+ SUBSTRING(@strDatos , 1, 6 )+@id
+RIGHT(REPLICATE('0',7) + CAST(cast(@SaldoActual*100 as int) as varchar(10)) ,7)
+ RIGHT(REPLICATE(' ',40) +@NomEmp ,40)
+ '000000' as resp
end
View 6 Replies
View Related
Nov 7, 2007
Hi, i get this error while i manually execute dts. But when i execute dts on my .aspx page, i can't handle this error on "... catch(Exception ex) {... }" part. catch (Exception ex) {
return ex.Message ; //DtsPackage.OnError += new PackageEvents_OnErrorEventHandler(DtsPackage_OnError);
} Here is dts message in a text file. Step 'DTSStep_DTSDataPumpTask_3' failedStep Error Source: Microsoft OLE DB Provider for SQL ServerStep Error Description:Transaction (Process ID 124) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.Step Error code: 80004005Step Error Help File:Step Error Help Context ID:0 Any idea?
View 2 Replies
View Related
Nov 7, 2007
Hi, i get this error while i manually execute dts. But when i execute dts on my .aspx page, i can't handle this error on "... catch(Exception ex) {... }" part.
catch (Exception ex)
{
return ex.Message ;
//DtsPackage.OnError += new PackageEvents_OnErrorEventHandler(DtsPackage_OnError);
} Here is dts message in a text file.
Step 'DTSStep_DTSDataPumpTask_3' failed
Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Transaction (Process ID 124) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
Any idea?
View 1 Replies
View Related
Nov 28, 2007
Hi, I have the follow function:
CREATE FUNCTION [dbo].[ToTime]
(
@intHora int, --A valid hour
@intMin int -- A valid minute
)
RETURNS smalldatetime
AS
BEGIN
declare @strTime smalldatetime
declare @errorvar int
select @strTime=cast(convert(varchar,cast((cast(@intHora as varchar) +':'+ cast(@intMin as varchar)) as smalldatetime),108) as varchar)
return @strTime;
END
the function works perfect but when the parameter for the hour is a negative number (for example -1), or a number > 23
and the parameter for the minute is an negative number (-1) or a number > 59, the function produce an error.
I need handle this error converting the wrong value in 0, but i don't want to do this using "if statement". for example
if @intHora < 0 or @intHora >23
begin
set @intHora = 0
end
if @intMin <0 or @intMin>59
begin
set @intMin = 0
end
please, If someone know some sql function (try - catch doesn't work) to handle this kind of error or some good way to do it, please help me.
View 4 Replies
View Related
Sep 25, 2006
I have a system that will post a message to a queue, but does not need to wait for a response - just needs to make sure the message arrived properly in the queue, not that is was processed at the receiving end. A second service will poll the queue to retrieve outstanding messages and will then move the message to an outside system. The movement of the message to the outside system will be wrapped in a transaction and if the process is successful, then the transaction will be commited otherwise it will be rolled back.
1) is it appropriate for the service that posts the message to send an END CONVERSATION ? This way the sending service will not be waiting for a response.
2) in the data movement phase, is it appropriate to issue and END CONVERSATION when commiting and not issue when ROLLBACK occurs. Or should ROLLBACK occur with a following END CONVERSATION with error message?
View 7 Replies
View Related
Jul 23, 2005
Below is the script. The problem is when I simulated the Oracle linkdrop, my SQL2K never have to a chance to head to the GOTO section as itdies with this error msg and exit. Any idea on a workround? Thanks.Server: Msg 7399, Level 16, State 1, Procedure USP_Link_Check, Line 8OLE DB provider 'MSDAORA' reported an error.[OLE/DB provider returned message: ORA-12154: TNS:could not resolveservice name]OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializereturned 0x80004005: ].----------------------------------------------ALTER PROCEDURE [USP_Link_Check] ASDECLARE @myERROR int -- Local @@ERROR, @myRowCount int -- Local @@ROWCOUNT--- Verify network connectionsselect *from openquery(OraLink,'select count(*) from Oracle.table')IF @myERROR != 0 GOTO HANDLE_ERRORHANDLE_ERROR:Print ' Error in Oracle Link'RETURN @myERROR---------------------------------------------
View 1 Replies
View Related
Jan 22, 2008
Hello,
How would you do a log in a massive rows loading, I'm having problems because every row error(because of casting, format, lookup) in a transformation task is redirected to a text file as a log, this is ok when only exist one error by row, but in the case when I have two errors in the same row detected by diferents transformation tasks only the first one is reported to the text file, I have to wait to the second information load, after I correct the first error, to find the second one, I need to validate as many errors exists by row in the same load...
which component or which strategy can I use in a SSIS Packge to achieve this?
thanks
View 1 Replies
View Related
Jul 30, 2006
I am attempting to learn Service Broker from Bob Beauchemin's book "A Developer's Guide to SQL Server" - Chapter 11. I'm finding it to be very good but I'm confused over the concept of closing a conversation. Could someone answer the following questions for me?
When a conversation is ended, can the conversation handle that was created when the conversation was created still be used? (I assume not)
Beauchemin says, on page 511, that when a conversation ends, "Any messages still in the queue from the other end of the conversation are deleted with no warning." Does this mean that if I send a message that expects a reply, but I end the conversation, the message is still sent, it is still received by the other endpoint, the other endpoint processes it, but I'll never receive the reply?
Beauchemin says that if no lifetime is specified, the conversation is active for the number of seconds which can be represented by the maximum size of an integer. Does this mean that if I don't specify a lifetime, a conversation is active for many, many years?
Thanks very much.
Amos
View 1 Replies
View Related
Sep 6, 2007
I want to reuse conversations to minimize overhead during bursts of activity. Remus' article on reusing conversations (http://blogs.msdn.com/remusrusanu/archive/2007/05/02/recycling-conversations.aspx) is great. (I know you are reading this Remus, thanks.)
I was wondering if there is a simpler way of ending a cached conversation - Quiesce the conversation (Stop using it), then after some period of time, end it.
I create a conversation, cache it in RLY_Conversations, and use it for 50 seconds. After 1 minute, the dialog timer servicing proc ends the conversation. There will be no messages sent around the time the End Conversation takes place, thus no race conditions.
Do you see any problems with this method?
Select @DialogHandle = [conversation_handle]
From RLY_Conversations
Where TableName = @TableName and IsActive = 1 And
CreatedTmstp > dateadd(ss, -50, getdate())
if @DialogHandle is null
Begin
-- initialize a conversation and record it in our reuse table
BEGIN DIALOG CONVERSATION @DialogHandle
FROM SERVICE FirstHostRelayService
TO SERVICE 'SecondHostRelayService'
ON CONTRACT RelayContractSentByAny
WITH ENCRYPTION=OFF ;
-- cache the dialog handle to minimize dialog creation overhead.
Insert into RLY_Conversations (
TableName, conversation_handle, conversation_id, is_initiator, service_contract_id,
conversation_group_id, service_id, lifetime, state, state_desc, IsActive, CreatedBy, CreatedTmstp
)
Select @TableName, conversation_handle, conversation_id, is_initiator, service_contract_id,
conversation_group_id, service_id, lifetime, state, state_desc, 1, 'Setup', getdate()
From sys.conversation_endpoints
Where conversation_handle = @DialogHandle;
-- initiate housekeeping process
BEGIN CONVERSATION TIMER ( @DialogHandle )
TIMEOUT = 60;
End
View 1 Replies
View Related
Feb 16, 2006
when you move a conversation to a conversation group, that conversation_group has to have been created previously, ie, you cant specify a non-existing conversation_group, right?
I ask because I am trying to develop an application where I use optimally one conversation related to many given conversation_groups, so that when I receive, I lock only a small determined subset of messages. What I could have used was a way to send messages on a conversation, specifying a conversation_group_id.
Any suggestions?
View 4 Replies
View Related
Oct 16, 2006
Hi There
Message ordering is of utmost importance in our application.
As i found in testing the only way to ensure message ordering is if they are in the same conversation.If you send multiple messages in different conversations there is no garantee which will be processed first.
Therefore i will be creating conversations that last "forever", that is using a single conversation.
I plan on doing a BEGIN DIALOG CONVERSATION when an inititator site is setup and writing the conversation handle guid to a table.
I will them simply SEND ON SONVERSATION using the guid, i will never issue a end conversation from target or initiator.
Is this theory solid, ie: is there a better way or best practice to do this?
I know that conversatons persist with sql server restarts, however what happens if an initiator site db is restored ?
I was thinking of adding logic to first check if a conversation endpoint exists with the specified guid if not , then start another conversation. But is this the best way?
Thanx
View 2 Replies
View Related
Jun 28, 2006
I am thinking of updating my SQL monitoring application to use Service Broker.
Right now I loop through my list of servers performing various checks on each server. Things like 'check last database backup', 'check for new databases', 'check for server restart'. I loop through, one server at a time, doing one check at a time. The more servers I have the longer it is taking.
So, I want to multi-thread the servers, but single-thread the checks on each individual server. This way I can check say, 5 servers at a time, but on each server I will only do one check at a time. This way I won't flood an individual server with multiple checks.
Is this possible? It looks like Conversation groups might be the way to go but I'm not sure.
View 2 Replies
View Related
Dec 20, 2005
I'm having some troubles with conversation groups. I need to send two messages on the same conversation group so I have the following in my SP....
BEGIN DIALOG CONVERSATION @providerConversationHandle
FROM SERVICE [ProviderDataService]
TO SERVICE 'CalculatedDataService'
ON CONTRACT [ProviderDataContract]
WITH ENCRYPTION = OFF
, LIFETIME = 600;
BEGIN DIALOG CONVERSATION @curveConversationHandle
FROM SERVICE [ProviderDataService]
TO SERVICE 'CalculatedDataService'
ON CONTRACT [ProviderDataContract]
WITH RELATED_CONVERSATION = @providerConversationHandle
, ENCRYPTION = OFF
, LIFETIME = 600;
SEND ON CONVERSATION @providerConversationHandle
MESSAGE TYPE [ProviderDataMessage] ( @providerMessage );
SEND ON CONVERSATION @curveConversationHandle
MESSAGE TYPE [ProviderCurveMessage] ( @curveMessage );
When I query the queue I see two messages, but they don't have the same conversation_group_id.
Any ideas?
Thanks.
View 9 Replies
View Related
Jul 26, 2006
I have not been successfull in getting conversation group to work. My understanding is that I can specify a 'guid' for a conversation group id in the create dialog and when I send a message on this conversation it will have that specific guid for its conversation group. When I do this it does not appear this way in the "target" queue.
I am looking for an example to help me understand how to use a conversation group. The MSDN has not really provided that run able example that I can run and verify and tweak.
The idea that I would like to try is that the initiator must send 5 different XML messages to a target. These 5 messages are all related and must exist together. What I assume is that if I want the target to get all 5 messages together out of the queue all messages must be sent in their own conversation but all linked with the same conversation group Id. I have not been able to get this to work.
The communication is really a one way where the initiator sends the data to the target and does not process or need a message sent back from the target.
Thank you for your time.
View 3 Replies
View Related
Jul 13, 2006
I have a conversation that I want to know has not ended so I am using LIFETIME.
When the conversation times out I then have three records in the queue.
1. The original conversation record that has not been received.
2. Error message to the initiator.
3. Error message to the target.
Both message bodies on the error records say that it was a lifetime error.
If I end the conversation on the initiator side after it is sent, I still get the target error record but the message_body field is null.
So say I don't end the conversation on the initator side. My next receive on the target side will pull the original record. Then it will pull the initator record and then it will pull the target record. Nothing on that record says that it had timed out.
What is the best practice for handling lifetime errors?
View 1 Replies
View Related
May 1, 2007
I need help about this , What i've done so far is a data processing extension that gets its data from a WS , I use it as a datasource for a report , That report will be displayed withing an ASPX page containing a reportviewer control that displays that report , The problem is catching errors that occurs withing the data processing extension .. ,
When i debugged the extension i saw that after throwing an exception the code ends up inside the "cancel()" event inside the IDBcommand implementation...
In all examples for a data processing extension i could find online they always have a comment saying the cancel is not implemented and throw a NewUnsupportedExtension()..
What happens is i get some uninformative , non-userfriendly error from the reporting services ,
something like "an error occurred during the report processing(rssomehing)"
How should i go about handling those exception that happened inside the data processing extension?
I've tried catching the error inside the event of the reportviewercontrol - reporterror but it seems that event don't fire when those errors are generated..
Any help on how to approach this would be highly welcome
View 7 Replies
View Related
Mar 21, 2007
When I try to execute the following statement:
END CONVERSATION DIALOG CONVERT(uniqueidentifier, '58C1A7AA-C0D7-DB11-B4C6-005056C00008')
using ICommand.Execute method, I got an error:
Incorrect syntax near the keyword 'END'.
I use SQL Native Client as OLE DB Provider.
In profiler I can see that my statement becomes like this:
exec END CONVERSATION CONVERT(uniqueidentifier, '903586ED-C0D7-DB11-B4C6-005056C00008')
So provider add 'exec' before my statement, maybe because NCLI don't know such statetment and thinks that it is a stored procedure call with the name 'END'?
Is there is a way to avoid it except of using following construction:
exec sp_executesql N''END CONVERSATION ...
?
View 5 Replies
View Related
