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
ADVERTISEMENT
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
View Related
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
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
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
Sep 6, 2005
Is there an upper limit on the number of Conversation Timers?
View 5 Replies
View Related
Jul 21, 2014
I'm trying to create a report which will give me a break down of how many unique vehicles have been seen between two dates via a 15 minute interval and what Lane they were seen. My current script looks like this
SELECT l.Name [Name], count(l.Name) Total, p.Created
FROM PlateReads p
inner join Lanes l on p.Lane_ID = l.ID
where LicencePlate in (Select Plate from LPRnet_MelAir_C.dbo.TempVehiclePlates)
group by Name
Name being the Lane they were in and the Total being the amount of times a unique vehicle has been seen and p.Created being the date they were seen (thats what I need the interval powered off)
Ideally the output would look like this
16/03/201408:00 to 08:15Bus Lane 15
16/03/201408:00 to 08:15Elevated Road150
16/03/201408:00 to 08:15Public Pickup75
16/03/201408:15 to 08:30Bus Lane 13
16/03/201408:15 to 08:30Elevated Road120
16/03/201408:15 to 08:30Public Pickup55
All the way to 12/04/2014
I’ve got it so it says Lane and Count just can’t get the interval part
View 5 Replies
View Related
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
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
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
Sep 12, 2015
I have some code I build 2 weeks ago which I’ve been running daily but it’s suddenly stopped working with the following error.
“The table "tbl_Intraday_Tmp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit” When I google this there seems to be a related to tables with vast numbers of columns.
My table tbl_Intraday_tmp is relatively small. It has 7 columns. 1 of varchar(5), 3 of decimal(9,3) and 2 of decimal(18,0). The bit I’m puzzled with is it was working and stopped.
I don’t recall changing anything but I wouldn’t rule that out. I ‘ve inspected the source files and I don’t believe they have changed either.
DECLARE
@FileName varchar(50),
@Path varchar(50),
@SqlCmd varchar(1000)
= '',
@ASXCode varchar(5),
@Offset decimal(18,0),
[code]....
View 5 Replies
View Related
Apr 20, 2012
I am using MS SQL server 2008, and i have a table with 350 columns and when i m trying to create one more column its giving error with below message -
Warning: The table XXX has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
how can i resolve this?
View 14 Replies
View Related
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
Oct 10, 2005
SQL Server 2000 8.00.760 (SP3)I've been working on a test system and the following UDF worked fine.It runs in the "current" database, and references another database onthe same server called 127-SuperQuote.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = [127-SuperQuote].dbo.tblCompany.Address1FROM[Work] INNER JOIN[127-SuperQuote].dbo.tblCompany ON [Work].ClientID =[127-SuperQuote].dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDSo now the system has gone live and it turns out that the live"SuperQuote" database is on a different server.I've linked the server and changed the function as below, but I get anerror both in QA and when checking Syntax in the UDF builder:The number name 'Zen.SuperQuote.dbo.tblCompany' contains more than themaximum number of prefixes. The maximum is 3.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = Zen.SuperQuote.dbo.tblCompany.Address1FROM[Work] INNER JOINZen.SuperQuote.dbo.tblCompany ON [Work].ClientID =Zen.SuperQuote.dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDHow can I get round this? By the way, I've rather simplified thefunction to ease readability. Also, I haven't posted any DDL because Idon't think that's the problem!ThanksEdward
View 2 Replies
View Related
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
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
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 theyre up to date.
However, those old rows that havent been updated yet I need to check every hour to see if the they have been updated and send them with FTP when theire up to date.
Is is possable to solve this by using SSIS?
Many thanks.
View 1 Replies
View Related
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
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
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
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
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
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
Mar 16, 2006
If two records containing datetime stamps are SELECTed, how can the interval between them be quickly calculated?
View 8 Replies
View Related
Feb 1, 2008
Does anybody know what's the best way to do a count with a 1 day interval? This to me seems like the best approach, my data looks like this
work order, analyst, analysis, dateanalyzed
080154705, ZT, Special, 1/31/2008 9:35:00 PM
080154706, ZT, 8260B, 1/31/2008 10:07:00 PM
080154706, ZT, Special, 1/31/2008 10:07:00 PM
080154706, ZT, Special, 1/31/2008 10:39:00 PM
080154706, ZT, Special, 1/31/2008 11:11:00 PM
080154706, ZT Special, 1/31/2008 11:43:00 PM
080165001, ZT, 624, 2/1/2008 4:30:00 AM
080165001, ZT, 624, 2/1/2008 5:02:00 AM
080166901, ZT, 624, 2/1/2008 12:50:00 PM
080166801, ZT, 624, 2/1/2008 2:51:00 PM
080154711, ZT, Special, 2/1/2008 10:39:00 PM
080124711, ZT, Special, 2/1/2008 10:11:00 PM
What I would like is to have my data displayed as follows:
Analysis, myCount, dateanalyzed
Special, 5, 1/31/08
Special, 2, 2/1/08
624, 4, 2/1/08
8260B, 1, 1/31/08
As you can see, I will need to count the number of occurences for analysis for each day, such as Special has 5 and 2 for those two days, we can have difference analysis done each day done. A Union Join will not work because the table is huge and I will need to do at least 30 days. Is interval the best approach, I plan to use Access but can do it in SQL Server if necessary.
View 4 Replies
View Related
Mar 27, 2007
Hello
I was wondering where I can change the polling interval settings in sql server management studio..
It appears to be different from the one in Enterprise Manager.
very little info can be found on the Internet
Thanks!
Worf
View 3 Replies
View Related
Oct 1, 2007
Hello ,I have a stored procedure that group a series of event by day. My table Tevent has a timestamp column for every event.Tevent structure:idevent| event| DatetimeEvent
I just found out that I need to group the result by production shift of the day , therefore I have to group the result for everyday from 8 AM until 2 AM the next day.Any idea of how to group by time interval?Thanks
View 4 Replies
View Related
Nov 9, 2007
Hello,
I have something i called a deletelist, inside this list is items that is waiting to be deleted.
The deletelist looks like this:username nvarchar(20),itemtype int,deletedate datetime
The SP that runs once everyday and deletes post is called Data_DeleteFromDeleteList.
Inside of this SP i have afew delete functions like this one:DELETE FROM reg_user WHERE (username = (SELECT username FROM data_deletelist WHERE (deletedate < getdate() AND itemtype = 0)))
Now i want to add a new delete function to this list, this function is to execute a SP when the itemtype occurs, like this:SELECT EXEC [dbo].[Data_CompletlyDeleteAUser] @UserName = username, @QResult = @QResult OUTPUT FROM data_deletelist WHERE (username = (SELECT username FROM data_deletelist WHERE (deletedate < getdate() AND itemtype = 2)))
any idea of how i can rewrite this function so it works??in this SP im not using the @QResult for anything, thats in another SP
At the end of this SP, i just delete all the old records from the deletelist like this,
DELETE FROM data_deletelist WHERE (deletedate < getdate())
View 2 Replies
View Related
Feb 1, 2001
I have a stored procedure that needs to executed every 10 seconds (either
at a fixed 10 second interval or 10 seconds from the completion of the stored
procedure). I initially assumed that the SQL Server Agent in SQL Server
7 would allow me to schedule a recurring job at this interval but it appears
that the lowest resolution for scheduling recurring jobs is minutes. The
task needs to begin when SQL Server starts and then run repeatedly.
I would appreciate any suggestions for resolving my problem.
Thanks
View 1 Replies
View Related
Mar 1, 2015
I am new to this group and to Sql query also. My requirement is the following
I have a table with these values...
colacolb colc cold
16:01 1 2 3
17:01 1 3 4
18:01 5 6 8
16:01 8 2 1
17:01 9 5 1
18:01 3 5 1
16:01 4 6 1
17:01 2 3 5
18:01 8 4 3
Now I want to get the SUM of colb, colc on each interval between 16:01 to 18:01...
View 4 Replies
View Related
Jan 17, 2007
I'm trying to migration a database from PostgreSQL to SQL Server, but i've found a problem with interval data type, SQL server does not have an interval data type, can someone help me please? I need the solution ASAP due to work deadline =(
View 2 Replies
View Related
Mar 5, 2008
Hello,
I'm trying to create a group by a set (but can change it later on) time interval.
What I mean by this is the following. I would like to group my data by 5 second intervals, and perhaps have a count for it as well - but that's not required.
For example I have the following data:
PRODUCT, DOOR, TIME_LOCATED
-------------------------------------
chips, 1, 3/5/2008 12:33:30 PM
harddisk, 1, 3/5/2008 12:33:30 PM
tea, 1, 3/5/2008 12:33:31 PM
software, 1, 3/5/2008 12:33:31 PM
chips, 1, 3/5/2008 12:33:32 PM
chips, 1, 3/5/2008 12:33:33 PM
chips, 2, 3/5/2008 12:33:34 PM
software, 1, 3/5/2008 12:33:40 PM
tea, 1, 3/5/2008 12:33:40 PM
software, 1, 3/5/2008 12:33:41 PM
Result in 5 second intervals (so group by values between 3/5/2008 12:33:30 and 3/5/2008 12:33:35)
Something like this?, or does have somebody a better idea?
PRODUCT, DOOR, TIME_LOCATED, COUNT
-------------------------------------------
harddisk, 1, 3/5/2008 12:33:30 PM 1
tea, 1, 3/5/2008 12:33:31 PM 1
software, 1, 3/5/2008 12:33:31 PM 1
chips, 1, 3/5/2008 12:33:33 PM 3
chips, 2, 3/5/2008 12:33:34 PM 1
software, 1, 3/5/2008 12:33:40 PM 2
tea, 1, 3/5/2008 12:33:40 PM 1
THANKS!
View 6 Replies
View Related
Jul 20, 2005
I have a table(work_order) with time as varchar(5).The values in table looks like thiswork_order_id rtim1 08:152 08:453 10:134 14:56and so on...I want to count how many work orders for every half an hour.The result should look like thisHours Count8 108:30 159 349:30 03and so on....really 8 hours means the work_orders issued (rtim)between 8:00 AND 8:30.Any Help is Appreciated.Thankyou.Jaidev Paruchuri
View 4 Replies
View Related
