Error handling in long-running batch jobs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Error handling in long-running batch jobs

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-12-18 : 08:51:56

Andy writes "We are developing a set of stored procedures that will run overnight, scheduled by the SQL Server 7 Job Scheduler. These jobs perform various (potentially) long-running activities, and result in large and complex changes in our data.

In order for our production support team to sucessfully support these jobs, we want to output progress messages and, in the event of failure, error messages. It's possible that, for a given batch run, there could be thousands of messages being written.

Initially, we used PRINT statements, and captured the output of the jobs to a file. This does capture all the info required, but is not exactly an elegant solution. Since the action required in response to a problem relies heavily on this information, we decided that the obvious place to store the info is in SQL Server tables, what we've called the Batch Log, and provide a nice user interface to view the messages, raise alerts, etc.

We've run into a logical catch-22 though (I'm sure it's obvious to everyone). A discrete unit of work (a transaction) may generate multiple output messages, in some cases hundreds. If this transaction fails, we need to rollback. But we want to keep the messages in the Batch Log tables. Problem.

We've come up with a few ugly options, all revolving around the idea of somehow stepping around the transaction rollback, carrying the log information forward and committing it. One suggestion is, when logging a message, call xp_cmdshell that calls a batch file, that calls ISQL to insert the message row. Another is to somehow decipher the log after the rollback.

This seems to be a tricky problem. Can you offer any advice?

Thanks,
Andy."

tansaku
Starting Member

3 Posts

Posted - 2005-07-06 : 00:21:00

A very inventive solution indeed - I've run it locally and it works. However when I run with the logging operation inside a cursor loop the whole thing seems to slow down enormously.

The process usually takes about 2 seconds. Adding all the code to create the OLE connection doesn't change that - nor a single log statement outside the cursor loop (I say log, actually its a statement creating a table).

However when I set it so that one statement inside the cursor loop (an insert) goes over the OLE connection the whole thing just takes forever - it hasn't finished yet - been running for half an hour.

I was wondering if anyone else had experienced this? Is there some restriction on this kind of operation in loops, or can only one statement be sent per connection?

Many thanks in advance.

CHEERS> SAM

p.s. my code (the procedure is called from the spunit framework which runs everything from within a transaction):

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE usp_SimulateByEventLog
@startDatetime datetime, --= CONVERT(datetime,'2005-01-28 03:59:52.950'),
@CeregoUser_ID varchar(16) = '350251001585434',
@noStudyOperations int = 2,
@studyInterval int = 24, -- in hours
@lessonsPerOperation int = 2,
@lessonInterval int = 2, -- in mins
@Min float = 0.6,
@Range float = 0.3,
@Engine_ID int = 1,
@Verbosity nvarchar(8) = 'QUIET'
AS
BEGIN

-- set up OLE objects for permanent storage

DECLARE @oSQLServer INTEGER
DECLARE @oDatabase INTEGER
DECLARE @oQueryResults INTEGER

DECLARE @CurrentDB VARCHAR(255)
DECLARE @Return INTEGER
DECLARE @Source VARCHAR(255)
DECLARE @Length INTEGER

DECLARE @Error INTEGER
DECLARE @ErrorRet INTEGER
DECLARE @ErrorMsg VARCHAR(255)

-- Create the SQLServer object
EXEC @Error = sp_OACreate 'SQLDMO.SQLServer', @oSQLServer OUT
IF @Error <> 0
GOTO OA_Error

-- Set the login process to use NT Authentication
EXEC @Error = sp_OASetProperty @oSQLServer, 'LoginSecure', -1
IF @Error <> 0
GOTO OA_Error

-- Connect to server using NT Authentication
EXEC @Error = sp_OAMethod @oSQLServer, 'Connect', NULL, @@SERVERNAME
IF @Error <> 0
GOTO OA_Error

-- Verify the connection
EXEC @Error = sp_OAMethod @oSQLServer, 'VerifyConnection', @Return OUTPUT
IF @Error <> 0
GOTO OA_Error
IF @Return = 0
GOTO OA_Error

-- Set the database object to be the current database
-- (We do this so we don't need to execute the SQL against the
-- SQLServer object, which would require explicit DB prefixing)
SELECT @CurrentDB = DB_NAME()
SELECT @CurrentDB = 'Databases("' + RTRIM(@CurrentDB) + '")'
EXEC @Error = sp_OAGetProperty @oSQLServer, @CurrentDB, @oDatabase OUT
IF @Error <> 0
GOTO OA_Error

-- remove the temporary tables associated with the previous simulation

if exists (select * from sysobjects where id = object_id(N'[dbo].[tblTmpRatios2]') )--and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTmpRatios2]

if exists (select * from sysobjects where id = object_id(N'[dbo].[tblTmpTrace]') )--and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTmpTrace]

if exists (select * from sysobjects where id = object_id(N'[dbo].[tblTmpStudyData]') )--and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTmpStudyData]

-- create a temporary table to store the ratios generated by our simulated user

CREATE TABLE tblTmpRatios2(
TmpRatioID int IDENTITY(1,1),
Item_ID int,
Upload_ID int,
Lesson_ID int,
Course_ID int,
CeregoUser_ID varchar(16),
Ratio float,
TimeStamp datetime
)

DECLARE @limit int
SET @limit = (0 - @noStudyOperations) * @lessonsPerOperation

DECLARE @sessionAt datetime
SET @sessionAt = @startDatetime

-- creates a table to store a trace of user activity item by item

DECLARE @FieldSep VARCHAR(2)
SET @FieldSep = ', '

DECLARE @createTraceTable varchar(8000)
SET @createTraceTable = 'CREATE TABLE tblTmpTrace(Timestamp datetime,'

DECLARE @Course_ID int
DECLARE @Lesson_ID int
DECLARE @Item_ID int
DECLARE @new_lesson_ID int
DECLARE @new_item_ID int
DECLARE @TmpRatioID int

/****************************
Sam's fix to Herry's fix
****************************/

SELECT TOP 1 @Course_ID = Course_ID
FROM tblUserCourse WHERE CeregoUser_ID = @CeregoUser_ID
--print @course_ID
/****************************
Herry's fix
****************************/

SELECT @createTraceTable=@createTraceTable+'Item'+CONVERT(varchar(5),Item_ID)+ ' NVARCHAR(16) DEFAULT ''''' + @FieldSep FROM tblLessonItem WHERE Lesson_ID IN
(SELECT Lesson_ID FROM tblCourseLesson WHERE Course_ID = @Course_ID)
SET @createTraceTable = LEFT(@createTraceTable, LEN(@createTraceTable) - LEN(@FieldSep))
SET @createTraceTable = @createTraceTable + ')'
--PRINT @createTraceTable
EXEC (@createTraceTable)
-- us this below to leave the trace table behind after operation
--EXEC usp_ExecuteOutsideTransaction @createTraceTable

-- creates a table that will hold all the study data we are interested in

DECLARE @createStudyDataTable varchar(8000)
SET @createStudyDataTable = 'CREATE TABLE tblTmpStudyData(
Item_ID int,
Lesson_ID int,
Cue nvarchar(32),
Response nvarchar(32),
Urgency float,
now datetime,
max_timestamp datetime,
max_ratio float,
TimeStamp datetime,
Ratio float
)'

--EXEC usp_ExecuteOutsideTransaction_SetUp @oSQLServer OUTPUT, @oDatabase OUTPUT, @oQueryResults OUTPUT
--EXEC usp_ExecuteOutsideTransaction @oSQLServer, @oDatabase, @oQueryResults, @createStudyDataTable
--EXEC (@createStudyDataTable)

SET @Length = LEN(@createStudyDataTable)
SET @ErrorMsg = '' -- Must initialize the @ErrorMsg string
EXEC @Error = sp_OAMethod @oDatabase, 'ExecuteWithResultsAndMessages', @oQueryResults OUTPUT, @Length = @Length, @Command = @createStudyDataTable , @Messages = @ErrorMsg OUTPUT
IF @Error <> 0
GOTO OA_Error

DECLARE @insertStudyDataTable varchar(8000)
SET @insertStudyDataTable = '
INSERT INTO tblTmpStudyData (Item_ID, Lesson_ID, Cue, Response, Urgency, now, max_timestamp, max_ratio, TimeStamp, Ratio)
(SELECT u.Item_ID, u.Lesson_ID, u.Cue, u.Response, u.Urgency, u.now, u.max_timestamp, u.max_ratio, r.TimeStamp, r.Ratio
FROM tblTmpUrgencies AS u
LEFT JOIN tblTmpRatios2 AS r ON u.Item_ID = r.Item_ID)'

DECLARE @Upload_ID int

DECLARE upload_cursor CURSOR FOR
SELECT Upload_ID, MIN([TimeStamp]) as Start
FROM tblEventLog
WHERE (CeregoUser_ID = @CeregoUser_ID)
GROUP BY Upload_ID
ORDER BY Start

IF @Verbosity = 'VERBOSE'
BEGIN
print @CeregoUser_ID
--DECLARE @CeregoUser_ID varchar(16)
--SET @CeregoUser_ID = '350251001585434'
--SELECT DISTINCT Upload_ID
--FROM tblEventLog
--WHERE CeregoUser_ID = @CeregoUser_ID
END

OPEN upload_cursor

FETCH NEXT FROM upload_cursor
INTO @Upload_ID, @sessionAt

DECLARE @subtraction int
SET @subtraction = -1

WHILE @@FETCH_STATUS = 0
BEGIN

-- since we don't currently have an event for the data request have to subtract some value from the first event we saw

SET @sessionAt = DATEADD(mi,@subtraction,@sessionAt)

IF @Verbosity = 'VERBOSE'
BEGIN
print @Upload_ID
END
-- now insert ratios for recommended items
-- this will insert the ratios from the tblTmpRatios2 table and put them in tblRatioSummary

EXEC usp_InsertRatioSummariesAndCalculateUrgencies @CeregoUser_ID, @sessionAt, @Upload_ID, 'tblTmpRatios2','tblTmpUrgencies', @Engine_ID = @Engine_ID

-- the next round of suggested study will now be in tblTmpUrgencies

IF @Verbosity = 'VERBOSE'
BEGIN
print 'first sp executed'
print @sessionAt
SELECT * from tblTmpRatios2
select * from tblRatioSummary
END

-- clear tmpRatio table of any old ratios

DELETE FROM tblTmpRatios2

-- this statement grabs the event log for this user and places ratio summaries for this upload id
-- in tblTmpRatios2 - however these will be for the items the user originally studies
-- ideally this should be taken from engine, but at the moment only the complete one can handle dynamic tables
EXEC usp_PopulateRatioSummaries_Complete @Upload_ID, @Engine_ID, 'tblTmpRatios2','tblEventLog'

-- now we need to adjust the item_ids and lesson_ids in tblTmpRatios2

DECLARE rewrite_cursor CURSOR FOR SELECT TmpRatioID FROM tblTmpRatios2
OPEN rewrite_cursor

FETCH NEXT FROM rewrite_cursor
INTO @TmpRatioID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @new_lesson_ID = NULL
SET @new_item_ID = NULL
-- the newid creates randomness that we may want to avoid ...
SELECT TOP 1 @new_lesson_ID = Lesson_ID, @new_item_ID = Item_ID
FROM tblTmpUrgencies WHERE Item_ID NOT IN (SELECT Item_ID FROM tblTmpRatios2) ORDER BY newid()

IF(@new_lesson_ID IS NOT NULL AND @new_lesson_ID IS NOT NULL)
BEGIN
UPDATE tblTmpRatios2
SET Lesson_ID = @new_lesson_ID, Item_ID = @new_item_ID
WHERE TmpRatioID = @TmpRatioID
-- if we tracked the itemid swaps here we'd effectively have
-- item difficulty incorporated - actually no, because we can't rely on things repeating with the same frequency
END

FETCH NEXT FROM rewrite_cursor
INTO @TmpRatioID
END
CLOSE rewrite_cursor
DEALLOCATE rewrite_cursor

IF @Verbosity = 'VERBOSE'
BEGIN
print 'second sp executed'
--select * from tblTmpUrgencies
select * from .tblTmpRatios2
END

-- This stored procedure adds a line to the trace table for a rotated view of the users progress so it easier to follow the ERS

EXEC usp_InsTraceTable 'tblTmpTrace', 'tblTmpUrgencies', 'tblTmpRatios2'

-- This insert takes the current rows from tblTmpRatios2 and places them in tblTmpStudyData
-- so that we have a complete record of all study by this user (we are deleting the contents of tblTmpRatios2)

--EXEC usp_ExecuteOutsideTransaction @oSQLServer, @oDatabase, @oQueryResults, @insertStudyDataTable
--EXEC (@insertStudyDataTable)

SET @Length = LEN(@insertStudyDataTable)
SET @ErrorMsg = '' -- Must initialize the @ErrorMsg string
EXEC @Error = sp_OAMethod @oDatabase, 'ExecuteWithResultsAndMessages', @oQueryResults OUTPUT, @Length = @Length, @Command = @insertStudyDataTable , @Messages = @ErrorMsg OUTPUT
IF @Error <> 0
GOTO OA_Error

if exists (select * from dbo.sysobjects where id = object_id(N'tblTmpUrgencies') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table tblTmpUrgencies

IF @Verbosity = 'VERBOSE'
BEGIN

print 'third sp executed'
select * from tblRatioSummary
END

FETCH NEXT FROM upload_cursor
INTO @Upload_ID, @sessionAt
END

CLOSE upload_cursor
DEALLOCATE upload_cursor

--EXEC usp_InsertRatioSummariesAndCalculateUrgencies @CeregoUser_ID, @sessionTwo, -2, '.tmpRatios','tmpUrgencies'
IF @Verbosity = 'VERBOSE' OR @Verbosity = 'BASIC'
BEGIN
SELECT * FROM tblTmpTrace
SELECT * FROM tblTmpStudyData
END
--TRUNCATE TABLE .tmpTrace
--TRUNCATE TABLE .tmpStudyData

--EXEC usp_ExecuteOutsideTransaction_TearDown @oSQLServer, @oDatabase, @oQueryResults

EXEC @Error = sp_OADestroy @oSQLServer
EXEC @Error = sp_OADestroy @oDatabase
EXEC @Error = sp_OADestroy @oQueryResults
-----------------------------------------------

-----------------------------------------------
OA_Error:

-- Get the error text
EXEC sp_OAGetErrorInfo @oSQLServer, @Source OUT, @ErrorMsg OUT
SELECT
@ErrorMsg = CONVERT(CHAR(16), @Error) + ': ' + @ErrorMsg + ' (Source: ' + @Source + ')'

-- Display the error message
print @ErrorMsg

EXEC @Error = sp_OADestroy @oSQLServer
EXEC @Error = sp_OADestroy @oDatabase
EXEC @Error = sp_OADestroy @oQueryResults

END

-- SELECT REVERSE(Item_ID), (1), ('fred'),(RAND(UNICODE(CONVERT(nvarchar,CONVERT(binary,NEWID()))))),
--CONVERT(int,CONVERT(binary,NEWID())),UNICODE(CONVERT(nvarchar,CONVERT(binary,NEWID()))), NEWID() FROM tblRatioSummary

-- SELECT LEFT('0xBC661F64B8E5854290A92126A5A6BAE20000000000000000000000000000',10)
/*
INSERT INTO .tmpRatios ( Ratio,Item_ID, TimeStamp)
VALUES ( 0.3,1, @sessionOne)
INSERT INTO .tmpRatios ( Ratio,Item_ID, TimeStamp)
VALUES ( 0.3,2, @sessionOne)
INSERT INTO .tmpRatios ( Ratio,Item_ID, TimeStamp)
VALUES ( 0.5,3, @sessionOne)
INSERT INTO .tmpRatios ( Ratio,Item_ID, TimeStamp)
VALUES ( 0.5,4, @sessionOne)
*/

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

kleine
Starting Member

1 Post

Posted - 2006-08-28 : 16:38:36

I've run the script on SQl Server 2000.
But now with Sql Server 2005 the Connect with SQLDMO doesn't work.

I get no error desciption. Only error number -2147165949.

Any idea ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 01:38:41

I think SQL DMO is no more with SQL Server 2005. It is called SMO. However, since DMO is deprecated in 2005, it still supports DMO. Not fully though.

http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources