A Question On Conversation Timer Persistence

Apr 24, 2006

I'd like to add code to a trigger to calculate the time to fire a message into a queue based on a field changing, and conversation timers seem like the way to go. My first question refers to this line from the BOL:

"Calling BEGIN CONVERSATION TIMER on a conversation before the timer has expired sets the timeout to the new value."

I think that in this trigger, I can simply begin a new conversation if the given field has changed to reset the timer. But intuition tells me that in order to change the timer to a new value, I need to retrieve the existing conversation, correct?

Also, I've read that conversation timers are persistent in that they survive database restarts and shutdowns. But I'm not sure to what extent. After a database restart/shutdown, does the conversation timer "reset" itself to the time interval specified when the conversation was begun or is it able to account for the time the database was down/offline?

Thanks,

Chris

View 5 Replies

Maximum Conversation Timer Interval

Apr 27, 2006

I'm using conversation timers successfully to fire events at a predetermined time in the future, but I'm running into issues when using an interval of considerable size. I set the conversation timeout like so:

set @Timeout = DATEDIFF(SECOND, GETDATE(), DATEADD(MINUTE, -(@TimeOffset), @FollowUpDateTime));

if (@Timeout < 0)

set @Timeout = 1;

// begin dialog

begin conversation timer (@FollowUpConversation)

timeout = @Timeout;

In this case @Timeout was 94693494.

In the SQL error log I see the following error: "Invalid subcommand value 94693494. Legal range from 1 to 2147483."

I thought I may check the @Timeout value and simply set it to 2147483 if it is larger than 2147483, but I was wondering if there was a reason the upper limit was so small.

Thanks,

Chris

View 10 Replies View Related

Conversation Timer Versus LIFETIME

Nov 17, 2006

I need to follow up on a message and check on its status. I am planning on using Conversation Timers (self addressed). I've tried it and they do work well. I am wondering if the LIFETIME parameter can be used for the same purpose. If the dialog has not been closed and the LIFETIME expires, will a message be queued into the service's queue? It does not seem that this is the case, but it is worth checking, as it could be a much desired feature.

Thanks,

View 5 Replies View Related

Conversation Timer Problem : Timeout Not Effective

Apr 30, 2007

Hi,

I am using conversation Timer for delaying a message for a few seconds but I can see the message immediately in the queue.

Here is the code i am using. This is a part of a stored procedure I have used.

BEGIN CONVERSATION TIMER ( @h ) TIMEOUT = @DelayBySeconds;

SEND ON CONVERSATION @h

MESSAGE TYPE [sendmsg]

(@msg);

I am executing this stored procedure with following statements.

exec set_ssb_msg 'test3', 25;

exec set_ssb_msg 'test1', 1;

select * from q1

I was hoping to see just the 'Test1' and see test3 after 25 seconds. But I could see both the messages in a queue as soon as i run the stored proc.

If I execute a receive command on the queue, I am receiving 'test3' first and then 'test1'. This is exactly opposit of what i expected.

Can you please let me know if I am doing anything wrong or missing a step.

Any help is greatly appreciated.

Thanks,

Don.

View 1 Replies View Related

From The Sql Profiler Constantly Displays Exec Sp_executesql N'BEGIN CONVERSATION TIMER ... It That Ok?

Mar 8, 2007

Hello,
I'm using SQL service Broker 2005 with ASP.NET 2.0 in order to use the sql cache dependency.
everything works fine...
I have only a doubt regarding a query that is constantly executed on mu db ( i can see it be means of the SQL Profiler)

The query is:
exec sp_executesql N'BEGIN CONVERSATION TIMER ("'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle,
cast(message_body AS XML) as message_body from [SqlQueryNotificationService-GUID]), TIMEOUT @p2;',N'@p2 int',@p2=60000

The web application is not running from a browser ...

It this ok or I forget to clean/reset something from my web application and/or sql server?

Thank you

Marina B.

View 3 Replies View Related

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 View Related

Conversation ID Cannot Be Associated With An Active Conversation

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

Conversation Handle Reuse And Conversation Handle XXX Not Found

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

Help!!! First Timer With SQL

Dec 8, 2003

I am trying to write my first app using SQL Server. I am not able to even open a connection. I get the following error message:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

I am not sure first of all why the user is listed as null. I hard coded a user name in for test purposes. Second, how DO I establish a trusted connection with a SQL Server. The SQL Server is located on an internal domain controller.

Also, is it necessary to have anything special installed on my remote machine? As I said, I am VERY new to Microsoft SQL Server

View 3 Replies View Related

Trying To Call A Function On A Weekly Timer

Sep 17, 2007

I'm not sure this is the place for this question, but not sure where else to go. I've written asp.net code to read from a sql server 2005 db and send out customized emails based on user info.Currently the process gets rolling by clicking a button in a web page.The client doesn't want to click a button, they want to run the email sender on a timer.How can I set up my function to run on a timer either in asp.net or more likely called from sql server?

View 6 Replies View Related

Timer For Executing Stored Procedure

Jul 20, 2005

HI,I need to run same kind of transactions (basically deleting records)in a loop but I have only 1 hour in a day to run my procedure. So Ineed to set a timer in a SP so that SP terminates after one hour andthen rest of the transactions will be done next day.Can anybody suggest as how to check execution time in a storedprocedure? The execution of the SP will be scheduled every night.If u need any further info pls ask.Thanks,Subodh

View 2 Replies View Related

SQL Server Integration Services - Timer

Jun 6, 2007

Hi,

Im trying to build a solution that Selects a couple of rows, checks their timestamp and sends them as a file with FTP if they�re up to date.

However, those old rows that haven�t been updated yet I need to check every hour to see if the they have been updated and send them with FTP when thei�re up to date.

Is is possable to solve this by using SSIS?

Many thanks.

View 1 Replies View Related

ExecuteNonQuery Hangs In Timer Event Notification

Apr 13, 2007

If I call ExecuteNonQuery() in a timer event callback in a console application, it hangs. Why is that?

.B ekiM

class Program
{
static SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NONEOFYOURBISUINESS;Data Source=localhost");

static void Main(string[] args)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);
Console.WriteLine("{0} bytes", mem.ullAvailPhys);

System.Timers.Timer aTimer = new System.Timers.Timer();
// Set the Interval to 2 seconds (2000 milliseconds).
aTimer.Interval = 1000;
aTimer.Enabled = true;

// Hook up the Elapsed event for the timer.
aTimer.Elapsed += new ElapsedEventHandler(OnTimedEvent);

Console.ReadLine();
}

private static void OnTimedEvent(object source, ElapsedEventArgs e)
{
NativeMethods.MEMORYSTATUSEX mem = new NativeMethods.MEMORYSTATUSEX();
NativeMethods.GlobalMemoryStatusEx(mem);

SqlCommand cmd = new SqlCommand(
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (1, @When, @AvailPhys);" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (2, @When, @AvailPageFile);" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (3, @When, @AvailVirtual);" +
"INSERT INTO Observation (StatisticType, StatisticObserved, StatisticValue) VALUES (4, @When, @AvailExtendedVirtual);");

DateTime dt = DateTime.Now;
cmd.Parameters.AddWithValue("AvailPhys", mem.ullAvailPhys);
cmd.Parameters.AddWithValue("AvailPageFile", mem.ullAvailPageFile);
cmd.Parameters.AddWithValue("AvailVirtual", mem.ullAvailVirtual);
cmd.Parameters.AddWithValue("AvailExtendedVirtual", mem.ullAvailExtendedVirtual);
cmd.Parameters.AddWithValue("When", dt);

cmd.ExecuteNonQuery();

Console.WriteLine("Inserted {0}", dt);
}

}

View 3 Replies View Related

1st Timer Question ... I Can't Seem To Get Internal Activation To Work.

Aug 14, 2007

New to service broker, and my books haven't come in yet. I'm trying to figure out how Internal Activation works.

The following code seems to "work", in terms of the inline code, but it doesn't seem to be going through the activation PROCs. Is there something else I need to set or do?

TIA, Bob

USE master;

GO

-- Ensure Service Broker functionality is enabled.

ALTER DATABASE ICEBOX SET ENABLE_BROKER;

GO

USE ICEBOX;

GO

-- Drop all sample objects if present.

IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsReceiveService') DROP SERVICE BobsReceiveService;

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsReceiveQueue') DROP QUEUE BobsReceiveQueue;

IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsSendService') DROP SERVICE BobsSendService;

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsSendQueue') DROP QUEUE BobsSendQueue;

IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = N'BobsContract') DROP CONTRACT BobsContract;

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsSendMessageType') DROP MESSAGE TYPE BobsSendMessageType;

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsReceiveMessageType') DROP MESSAGE TYPE BobsReceiveMessageType;

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_NAME = 'USP_BobsReceiveQueueMonitor') DROP PROCEDURE dbo.USP_BobsReceiveQueueMonitor;

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_NAME = 'USP_BobsSendQueueMonitor') DROP PROCEDURE dbo.USP_BobsSendQueueMonitor;

GO

CREATE PROCEDURE dbo.USP_BobsSendQueueMonitor

AS

DECLARE @QueueTable TABLE

(status tinyint --Status of the message. For messages returned by the RECEIVE command, the status is always 1. Messages in the queue may contain one of the following values: 0=Received message1=Ready2=Not yet complete3=Retained sent message

,queuing_order bigint --Message order number within the queue.

,conversation_group_id uniqueidentifier --Identifier for the conversation group that this message belongs to.

,conversation_handle uniqueidentifier --Handle for the conversation that this message is part of.

,message_sequence_number bigint --Sequence number of the message within the conversation.

,service_name nvarchar(512) --Name of the service that the conversation is to.

,service_id int --SQL Server object identifier of the service that the conversation is to.

,service_contract_name nvarchar(256) --Name of the contract that the conversation follows.

,service_contract_id int --SQL Server object identifier of the contract that the conversation follows.

,message_type_name nvarchar(256) --Name of the message type that describes the message.

,message_type_id int --SQL Server object identifier of the message type that describes the message.

,validation nchar(2) --Validation used for the message. E=Empty N=None X=XML

,message_body varbinary(MAX) --Content of the message.

,message_id uniqueidentifier --Unique identifier for the message.

);

-- FROM BoL

-- The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id.

-- The search condition may not contain any of the other columns in the queue.

-- The conversation_handle or conversation_group_id may not be an expression.

DECLARE @SendDialog_Handle uniqueidentifier;

DECLARE @SendMsg varbinary(MAX);

DECLARE @SendMsgName nvarchar(256);

BEGIN

PRINT 'SEND' + CONVERT(VARCHAR,GETDATE(),109);

SELECT TOP (1)

@SendDialog_Handle = conversation_handle

,@SendMsg = message_body

,@SendMsgName = message_type_name

FROM

BobsSendQueue;

SELECT 'SEND',@SendDialog_Handle AS conversation_handle,CAST(@SendMsg AS VARCHAR(MAX)) AS message_body,@SendMsgName AS message_type_name;

END;

GO

CREATE PROCEDURE dbo.USP_BobsReceiveQueueMonitor

AS

DECLARE @QueueTable TABLE

(status tinyint --Status of the message. For messages returned by the RECEIVE command, the status is always 1. Messages in the queue may contain one of the following values: 0=Received message1=Ready2=Not yet complete3=Retained sent message

,queuing_order bigint --Message order number within the queue.

,conversation_group_id uniqueidentifier --Identifier for the conversation group that this message belongs to.

,conversation_handle uniqueidentifier --Handle for the conversation that this message is part of.

,message_sequence_number bigint --Sequence number of the message within the conversation.

,service_name nvarchar(512) --Name of the service that the conversation is to.

,service_id int --SQL Server object identifier of the service that the conversation is to.

,service_contract_name nvarchar(256) --Name of the contract that the conversation follows.

,service_contract_id int --SQL Server object identifier of the contract that the conversation follows.

,message_type_name nvarchar(256) --Name of the message type that describes the message.

,message_type_id int --SQL Server object identifier of the message type that describes the message.

,validation nchar(2) --Validation used for the message. E=Empty N=None X=XML

,message_body varbinary(MAX) --Content of the message.

,message_id uniqueidentifier --Unique identifier for the message.

);

-- FROM BoL

-- The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id.

-- The search condition may not contain any of the other columns in the queue.

-- The conversation_handle or conversation_group_id may not be an expression.

DECLARE @ReceiveDialog_Handle uniqueidentifier;

DECLARE @ReceiveMsg varbinary(MAX);

DECLARE @ReceiveMsgName nvarchar(256);

BEGIN

PRINT 'RECEIVE' + CONVERT(VARCHAR,GETDATE(),109);

SELECT TOP (1)

@ReceiveDialog_Handle = conversation_handle

,@ReceiveMsg = message_body

,@ReceiveMsgName = message_type_name

FROM

BobsReceiveQueue;

SELECT 'RECEIVE',@ReceiveDialog_Handle AS conversation_handle,CAST(@ReceiveMsg AS VARCHAR(MAX)) AS message_body,@ReceiveMsgName AS message_type_name;

END;

GO

CREATE MESSAGE TYPE BobsSendMessageType VALIDATION = NONE;

CREATE MESSAGE TYPE BobsReceiveMessageType VALIDATION = NONE;

CREATE CONTRACT BobsContract

(BobsSendMessageType SENT BY INITIATOR

,BobsReceiveMessageType SENT BY TARGET

);

CREATE QUEUE BobsSendQueue

WITH ACTIVATION

(STATUS = ON

,PROCEDURE_NAME = dbo.USP_BobsSendQueueMonitor

,MAX_QUEUE_READERS = 1

,EXECUTE AS OWNER

)

;

CREATE SERVICE BobsSendService ON QUEUE BobsSendQueue;

CREATE QUEUE BobsReceiveQueue

WITH ACTIVATION

(STATUS = ON

,PROCEDURE_NAME = dbo.USP_BobsReceiveQueueMonitor

,MAX_QUEUE_READERS = 1

,EXECUTE AS OWNER

)

;

CREATE SERVICE BobsReceiveService ON QUEUE BobsReceiveQueue (BobsContract);

GO

DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;

DECLARE @SendMsg NVARCHAR(100);

SET @SendMsg = N'Hello Bob';

BEGIN TRANSACTION;

BEGIN DIALOG CONVERSATION @BobsDialog_Handle FROM SERVICE BobsSendService TO SERVICE N'BobsReceiveService' ON CONTRACT BobsContract WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @BobsDialog_Handle MESSAGE TYPE BobsSendMessageType (@SendMsg);

COMMIT TRANSACTION;

SELECT 'Conversation started on Handle ' + CAST(@BobsDialog_Handle AS VARCHAR(36));

GO

DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;

DECLARE @ReceiveMsg NVARCHAR(100);

DECLARE @ResponseMsg NVARCHAR(100);

DECLARE @ReceiveMsgName SYSNAME;

SET @ResponseMsg = N'Back at ya';

BEGIN TRANSACTION;

RECEIVE TOP(1)

@BobsDialog_Handle = conversation_handle

,@ReceiveMsg = message_body

,@ReceiveMsgName = message_type_name

FROM

BobsReceiveQueue;

COMMIT TRANSACTION;

SELECT @ReceiveMsgName AS ReceiveMsgName,@ReceiveMsg AS ReceiveMsg;

SEND ON CONVERSATION @BobsDialog_Handle MESSAGE TYPE BobsReceiveMessageType (@ResponseMsg);

END CONVERSATION @BobsDialog_Handle;

GO

DECLARE @ResponseMsg NVARCHAR(100);

DECLARE @ResponseMsgName SYSNAME;

DECLARE @BobsDialog_Handle UNIQUEIDENTIFIER;

BEGIN TRANSACTION;

RECEIVE TOP(1)

@BobsDialog_Handle = conversation_handle

,@ResponseMsg = message_body

,@ResponseMsgName = message_type_name

FROM BobsSendQueue;

END CONVERSATION @BobsDialog_Handle;

COMMIT TRANSACTION;

SELECT @ResponseMsgName AS ResponseMsgName, @ResponseMsg AS ResponseMsg;

GO

IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsReceiveService') DROP SERVICE BobsReceiveService;

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsReceiveQueue') DROP QUEUE BobsReceiveQueue;

IF EXISTS (SELECT * FROM sys.services WHERE name = N'BobsSendService') DROP SERVICE BobsSendService;

IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'BobsSendQueue') DROP QUEUE BobsSendQueue;

IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = N'BobsContract') DROP CONTRACT BobsContract;

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsSendMessageType') DROP MESSAGE TYPE BobsSendMessageType;

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'BobsReceiveMessageType') DROP MESSAGE TYPE BobsReceiveMessageType;

GO

View 3 Replies View Related

How To Use A Dialog Retry Timer For Doing A Web Service Call

Feb 7, 2007

Hi Remus,

I just started looking into SB about a week ago, so my question is likely to be pretty lame. However, that's not going to stop me from asking it :-)

We're trying to do something similar to what you're describing here. How do you "reset" a dialog timer? Why would you need to deal with resetting timers (or with timers at all for that matter) in the event of a success? Couldn't you rewrite the logic to be as follows?

begin transaction
receive message
if message is web request
save state of request (http address, caller etc)
else if message is retry timer message
load state of request
endif
commit
do the web request (no transaction open)
if success
begin transaction
send back response
end conversation
commit
else
set a retry timer on the dialog (say 1 minute) using BEGIN DIALOG TIMER
endif

Also, when you set the retry timer you have to associate it with the saved request state, right? Otherwise, how will the service know which request to load on receipt of the timer message?

TIA.

View 6 Replies View Related

How To Write A Simple, Accurate Stored Proc Timer?

Oct 25, 2006

I want to test some times it takes for a proc to run. Since I work for a large company, I rarely have access to the SQL server in a capacity where I can use profiler and such. Are there any quick and easy ways to just surround blocks of code in a T-SQL statement to get an accurate reading on how long it takes?I.e., if I surround with GETDATE() before and after, does that also measure the round trips to the server, or just the execution time.I want to just compare some different methods and see what is quicker. THX.

View 4 Replies View Related

How To Create Stored Procedure In SQL Express That Will Be Executed On Timer?

Dec 3, 2006

I need to check the databes on the server side every 3 days and delete old data.

I am using SQL Express.

View 5 Replies View Related

Need Advice - A Delay Timer In Retrieving Remote Data, A Good Idea?

May 21, 2008

I have an application that automatically reads a lot of data from a third-party application into my database, via XML. For example, I might read a couple thousand rows-worth of XML data, one row at a time in a foreach loop.
To reduce the load on their server and database, I thought about putting a 2 second delay in between each of my automatic requests. Would this really help much, or is there enough overhead (setting up/tearing down connections, etc) with each request that it wouldn't reduce server load much anyway?
Is 2 seconds enough? Too little or too much?

View 3 Replies View Related

Appropriate Use Of End Conversation

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

Ending A Conversation

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

Quiesce Then End Conversation

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

Move Conversation

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

Conversation That Dont End.

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

Conversation Groups

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

Conversation Groups

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

Conversation Group

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

Conversation LIFETIME

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

Execution Of END CONVERSATION Statement

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

Service Broker End Conversation

Jan 17, 2007

Hello people

I am new to service broker and would like a little help please. I have a SP which gathers information from a collection of tables. Depending on the data gathered it may or may not begin a dialog conversation with a service broker queue. What i'm needing to know is should at the end of the SP once the required message has been sent should i end the conversation or not?

Many thanks in advance, Michael

View 1 Replies View Related

Conversation Lifetime After Close

Oct 18, 2006

Given that the conversation states are as follows: (Thanks Rushi!)

Event Initiator Endpoint state Target Endpoint state

BEGIN DIALOG SO --
from Initiator
to Target

SEND message(s) CO --
from Initiator
to Target

Target receives a fragment CO SI
of the first message sent
or receives out of order
message

Target received entire CO CO
first message

END conversation at CO DO
target

Initiator receives EndDialog DI DO
message from target

Target receives ACK for the DI CD
EndDialog message sent

END conversation at CD CD
Initiator

When does the 30 minute timer start for clearing the conversation from the sys.conversation_handles table? Is it the same for both sides (initiator and Target) ie, the end conversation at the Initiator. I guess it must be just in case a resend is necessary.

Gary

View 2 Replies View Related

End Conversation Causing Blocks

Apr 9, 2007

Hi

I was trying to clean up some conversation in Service Broker and caused alot of blocking that I seem to unable to kill. there was 1 conversation that I was not able to end, so I wanted to restart sql service, But I can't even restart the SQL service. I get the following in Event Viewer

Timeout occurred while waiting for latch: class 'SERVICE_BROKER_TRANSMISSION_INIT', id 00000001A2B03540, type 2, Task 0x0000000000C2EDA8 : 0, waittime 5400, flags 0xa, owning task 0x00000002DEBCA5C8. Continuing to wait.

Has anyone come across this

thanks

Paul

View 1 Replies View Related

How Can I Determine The Queuing_order Of A Conversation?

Jan 9, 2007

Hi There

Is there a DMV or something in the system that can tell me what the current queue message number is for a conversation.

I would like to determine what the message queue_order number for a message is before or after i send the message, as i would like to log this information?

Thank You

View 7 Replies View Related