October 14, 2008 at 3:14 pm
Hi Experts,
I use a CRM application that uses ActiveReports to enable report development. I have a procedure that takes a minute or more but the CRM application sends a generic timeout error because the procedure doesn't finish within the timeout limit. We have asked but cannot get to the timeout limit. My question is, is there a way for procedure 'A' that the CRM application calls to start procedure 'B' and letting procedure 'A' finish immediately while procedure 'B' finishes producing the report (procedure 'B' uses sp_makewebtask to write an Excel file for the report output).
Warm regards,
October 14, 2008 at 3:37 pm
You want to use either Service Broker or a work table that is then scanned by a polling process running in a separate context (probably a SQL Agent Job). Both of these are pretty deep water for a Newbie, but using a polling SQL Agent Job is probably the simpler of the two, though it has more latency (typically an extra minute).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 4:26 pm
Howdy RBarry - to get rid of the latency, what if you added a call to sp_start_job? It would return immediately and the job would run "in the background".
Chad
(edited) - clarified what sp_start_job would help with
October 14, 2008 at 4:34 pm
You can, but direct calls to SQL Agent are a lot worse that direct calls to Service Broker. If you were going to go that route, you might as well just use Service Broker.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 15, 2008 at 6:21 am
Any chance of tuning the procedure or adding indexes to help it out?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 17, 2008 at 2:36 pm
I use five temp tables and four have indexes. I'm pretty sure I have them tuned as best they can be, tuning the fourth table cut 45 seconds off.
I still need a way to start this procedure from another one. Something I didn't mention is that this procedure has four parameters I would like to pass in somehow, maybe a global temp table.
Warm regards,
October 17, 2008 at 2:48 pm
FAST number_rows
Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.
how about using option fast
October 17, 2008 at 9:49 pm
h.schlais (10/17/2008)
I still need a way to start this procedure from another one. Something I didn't mention is that this procedure has four parameters I would like to pass in somehow, maybe a global temp table.
Service Broker can easily do that also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 20, 2008 at 6:02 am
danschl (10/17/2008)
FAST number_rowsSpecifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.
how about using option fast
But you end up with two execution plans for the data. The first is focused on getting out X number of rows as fast as possible, the second is usually a pretty indiferent execution plan that can cause any number of problems. This approach is only useful if you don't care about actual performance, but are interested in perceived performance because, overall, performance will degrade using this FAST N.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 20, 2008 at 3:15 pm
just throwing this out there...
1. you pass the 4 variables to the SP
2. SP then calls a batch file via xm_cmdshell
3. batch file uses the 'start' command to spawn a new process which allows the batch file to close/finish
4a. The SP is done and the program gets a return code
4b. the new process was a call to the SP that actually makes the report.
October 20, 2008 at 8:08 pm
This essentially is what you can do with Service Broker, only without having to use xp_CmdShell.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 29, 2008 at 4:08 pm
Hi, regarding Service Broker I am testing. The message types and everything are set up and seem to be fine. I can run the procedure that begins the conversation. I have a test procedure set on activation of my request queue. I wrote the following just to see what things look like but Query Analyzer says 1) that @profilename is not a parameter for procedure sp_notify_operator and 2) it also says the conversation handle is missing, this error is in the ELSE clause on the line where I am setting the @Body text (but it probably means the END CONVERSATION line). Can you make any suggestions as what might be the problem. Thank you very much. Warm regards.
ALTER PROCEDURE [dbo].[z_TEST_Target_DEV]
AS
BEGIN
SET NOCOUNT ON
DECLARE @XMLMessage XML
DECLARE @DialogHandle UNIQUEIDENTIFIER
DECLARE @MessageType VARCHAR(500)
DECLARE @ProfileName varchar(50)
DECLARE @Recipients varchar(400)
DECLARE @Subject varchar(80)
DECLARE @Body varchar(5000)
;RECEIVE TOP(1)
@DialogHandle = conversation_handle,
@XMLMessage = message_body,
@MessageType = message_type_name
FROM TESTRequestQueue
IF @@ROWCOUNT = 0
BEGIN
-- send an email to whoever is the SQL Server Agent Operator
SET @ProfileName = 'DBMailProfile'
SET @Recipients = 'SQL Server Agent Operator'
SET @Subject = 'URGENT: TESTRequestQueue '
SET @Body = 'Invalid activation.'
EXEC msdb.dbo.sp_notify_operator @profilename=@ProfileName,@subject=@Subject,@body=@Body,@name=@Recipients
END
ELSE
BEGIN
-- test message to IT
SET @ProfileName = 'DBMailProfile'
SET @Recipients = 'SQL Server Agent Operator'
SET @Subject = 'URGENT: COBRARequestQueue '
SET @Body = 'Activation. Message contents: '+CHAR(10)+CHAR(10)
+'DialogHandle: '+CONVERT(VARCHAR(4000),@DialogHandle)+CHAR(10)
+'MessageType: '+CONVERT(VARCHAR(500),@MessageType)+CHAR(10)
+'XMLMessage: '+CONVERT(VARCHAR(4000),@XMLMessage)
EXEC msdb.dbo.sp_notify_operator @profilename=@ProfileName,@subject=@Subject,@body=@Body,@name=@Recipients
END
END CONVERSATION @DialogHandle
END
October 29, 2008 at 4:34 pm
Hi,
Problem 1) taken care of: sp_notify_operator works with the parameter @profile_name not the parameter @profilename. The Help on sp_notify_operator is wrong.
So that just leaves me wondering about problem 2) the missing conversation handle. Did I explain that the procedure I included (z_TEST_Target_DEV) is the procedure I want to activate from the 'request queue'... If the Initiator procedure ran w/o error how can I see what is on the 'request queue' that it sent?
Warm regards,
October 29, 2008 at 7:26 pm
h.schlais (10/29/2008)
1) that @profilename is not a parameter for procedure sp_notify_operator and 2) it also says the conversation handle is missing, this error is in the ELSE clause on the line where I am setting the @Body text (but it probably means the END CONVERSATION line).
1) Correct spelling is @profile_name.
2) For various reasons, it is normal for a Queue's activation procedure to be activated more often than there are unique conversations pending for it. As there you have no WAITFOR (which is normal & OK), the RECEIVE will return immediately if there is no pending message. In this case, @DialogHandle will be NULL, resulting in the error from END CONVERSATION later on. The standard way to test for the "No Message Pending" condition is in fact to check to see if the returned conversation handle is NULL. Obviously, you want to do this immediately after the attempted RECEIVE.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 30, 2008 at 6:42 am
Should the END CONVERSATION be wrapped in an IF to avoid ending a conversation with a NULL handle ?
For various reasons, it is normal for a Queue's activation procedure to be activated more often than there are unique conversations pending for it.
Why is the above normal, what's going on there ?
Thank you and warm regards,
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply