June 24, 2011 at 12:30 am
Hi Friends,
If i execute a stored procedure which uses cursor (to fetch 35 records), manually it is running fine even if it fails to execute few steps and goes till end, if i schedule job to run a stored procedure if it fails at sec step it is not going further,
suggest me the solution for this , Thanks in advance
June 24, 2011 at 4:57 am
HI, so if the cursor fails at some point when run on its own, sounds like this needs addressing first, Next in a stored proc if you set set xact abort on or other items then if a step fails then the whole transaction gets rolled back.
aslo take a look at http://msdn.microsoft.com/en-us/library/ms188792.aspx
***The first step is always the hardest *******
June 24, 2011 at 5:01 am
what i can do, to schedule job even it fails at middle , it should reach the end of the records and should come out ,to shows at which record it is throwing error....
June 24, 2011 at 5:04 am
i dont want transaction to be rolled back, i just want to continue with tran even if it fails at middle, please suggest me how i can achieve this
June 24, 2011 at 5:08 am
Why does it fail?
Why do you need a cursor?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 24, 2011 at 5:12 am
Below is the code im using, if i run it manually then it run fine even if it fails at 3rd step, if i schedule this sp to run in job, it coming out of the loop after the 3rd step
CREATE PROCEDURE [amwdms].[Pr_ProcessSummaryReports]
AS
BEGIN
DECLARE
@pReportCode nvarchar(10),
@pDateExists numeric(3),
@pRunDate date,
@pRunDateChar nvarchar(30),
@pSQLName nvarchar(100),
@pExcuteSQL nvarchar(4000),
@pErrMsg nvarchar(500),
@pid numeric(16,0),
@pid2 numeric(16,0)
DECLARE cursor_Rep CURSOR FOR
SELECT ReportCode FROM SummaryReports WHERE isnull(Active,'F')='T' ORDER BY ExecutionSequence
OPEN cursor_Rep
FETCH NEXT FROM cursor_Rep INTO @pReportCode
WHILE @@FETCH_STATUS = 0
BEGIN
SET @pDateExists = 0
SET @pDateExists = (Select count(*) from SummaryReportsRunDate where ReportCode = @pReportCode and RunDate = dateadd(dd,0, datediff(dd,0, getDate())))
set @pid=(select max(summaryreportsrundateid) from summaryreportsrundate)
IF (@pDateExists) = 0
INSERT INTO SummaryReportsRunDate
(summaryreportsrundateid, [rundate], reportcode, [status])
VALUES
(@pid+1, dateadd(dd,0, datediff(dd,0, getDate())) ,@pReportCode , 0)
DECLARE cursor_RepDates CURSOR FOR
SELECT RunDate FROM SummaryReportsRunDate WHERE ReportCode = @pReportCode and Status = 0 ORDER BY RunDate
OPEN cursor_RepDates
FETCH NEXT FROM cursor_RepDates INTO @pRunDateChar
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cursor_RepSQL CURSOR FOR
SELECT SQLName, ExcuteSQL FROM SummaryReportsSQL WHERE ReportCode = @pReportCode ORDER BY SQLExecutionSequence
OPEN cursor_RepSQL
FETCH NEXT FROM cursor_RepSQL INTO @pSQLName, @pExcuteSQL
WHILE @@FETCH_STATUS = 0
BEGIN
SET @pExcuteSQL = REPLACE(@pExcuteSQL,'@today',char(39)+(@pRunDateChar)+char(39))
SET @pid2=(select ISNULL(max(summaryreportslogid),0) from summaryreportslog)
INSERT INTO SummaryReportsLog
([summaryreportslogid],[rundate],[reportcode],[sqlname],[executionstatus],[errormessage])
VALUES (@pid2+1,@pRunDateChar,@pReportCode, @pSQLName , 'Done','NA')
EXEC (@pExcuteSQL)
FETCH NEXT FROM cursor_RepSQL INTO @pSQLName, @pExcuteSQL
END
CLOSE cursor_RepSQL
DEALLOCATE cursor_RepSQL
UPDATE SummaryReportsRunDate SET Status = 1 where ReportCode = @pReportCode and RunDate=@pRunDateChar
FETCH NEXT FROM cursor_RepDates INTO @pRunDateChar
END
CLOSE cursor_RepDates
DEALLOCATE cursor_RepDates
FETCH NEXT FROM cursor_Rep INTO @pReportCode
END
CLOSE cursor_Rep
DEALLOCATE cursor_Rep
END
June 24, 2011 at 5:21 am
Please mark the bit that is creating the error. 'Third step' does not make it clear to me.
Also, please post the text of the error message.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 24, 2011 at 5:23 am
Also, how do you run the stored procedure? In other words, please give an example of what parameters you pass to it.
Thanks
John
June 24, 2011 at 8:08 am
EEEK a cursor within a cursor, do you have any representative data and tables that we can use to view your code and test any sugestions we have.
Prehaps we can find alternative and more cost effective ways to comlete this task.
***The first step is always the hardest *******
June 27, 2011 at 12:21 am
we run both manually and through scheduling a job, if i run manually it works fine, if i run through job it fails
June 27, 2011 at 12:09 pm
The hard part here is that nested cursors are hideously horrible for performance. This doesn't look like you need a single cursor let alone two of them. There are few people on these forums that are comfortable with nested cursors that are doing nothing more than inserts and updates.
That aside several people have asked exactly where in the code it fails and what error message you are receiving. How long does this take when you run it manually? How do you know it fails? do you get an error message? What does that message say? You say it runs fine but with nested cursors your idea of fine and mine are probably different.
Help us help you and you will get not only an answer but I suspect you will walk away from this with a process that runs in a fraction of the time it does today.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2011 at 1:28 pm
are you sure it runs fine manually have you tested with the same data roll the data back and then run tried as a scheduled job?
***The first step is always the hardest *******
June 28, 2011 at 2:21 pm
I can see why you're using cursors here - to run procedures that run reports so I'm not going to go after you for using cursors within cursors. The main problem is that we have no idea what the procedures found in @pExcuteSQL variable are. Somewhere you're getting an error and you don't know what it is. Some ANSI setting that allows it to complete when run from SSMS?
You should probably set up a quick and dirty log table to capture what procedure is about to be run and from that determine which one failed. Something like:
CREATE TABLE RptLog
( RunTime DATETIME DEFAULT GETDATE()
, ProcedureCall VARCHAR(255)
)
Before each EXEC (@pExcuteSQL) put:
INSERT INTO RptLog
( ProcedureCall )
SELECT @pExcuteSQL
That way you will quickly find where the master procedure stopped running.
Todd Fifield
June 28, 2011 at 11:38 pm
Below error the job is throwing:
Message
Executed as user: . Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Divide by zero error encountered. [SQLSTATE 22012] (Error 8134) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
June 29, 2011 at 4:34 am
That error sounds like somting your calculating has a null value so you need to find out what and eliminate the null.
Do you have any representative data we can use to parse your cursor?
***The first step is always the hardest *******
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply