Hi,

Im having problems with calling stored procedures on a SQL server.

Basicly we have night job, that does some database checking. The stored procedure we call takes about 40 seconds to complete, but the scheduler exists after 17 secs and dosnt log anything.

I think it might be some sort of timeout issue, but i cannot be sure.

my job is :
dim Return, Number
DatabaseConnect "MyConnection"
DatabaseExecute( "exec owner.MySTPJob", Return )

The stored procedure has been testet in sql analyzer, but basicly it looks like this:
CREATE PROCEDURE owner.mystpjob
as
declare @count int
select something that returns a row for each error
set @count = @@rowcount

if (@count > 0) begin

raiserror ('Job error count %d', 16, 1, @count)

return 1
end

return 0

The database trace is:
/*---------------------------------------------------*/
/* 12-02-2004 13:57 */
/*---------------------------------------------------*/
(4a809a0): LOGIN: (469 MilliSeconds)
(4a809a0): CONNECT TO TRACE MSS:
(4a809a0): DATABASE=
(4a809a0): LOGID=
(4a809a0): LOCK=Read Committed
(4a809a0): DBPARM=AppName='24x7',Async=1,DBGetTime=300
(4a809a0): SERVER= (47 MilliSeconds)
(4a809a0): PREPARE: (0 MilliSeconds)
(4a809a0): EXECUTE:
(4a809a0): exec owner.mystpjob (17140 MilliSeconds)
(4a809a0): GET AFFECTED ROWS: (0 MilliSeconds)
(4a809a0): ^ 0 Rows Affected
(4a809a0): CANCEL: (125 MilliSeconds)
(4a809a0): COMMIT: (47 MilliSeconds)
(4a809a0):
Error 3902 (rc -1) : The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
(4a809a0): DISCONNECT: (0 MilliSeconds)
(4a809a0): SHUTDOWN DATABASE INTERFACE: (0 MilliSeconds)

I tried to make a raiserror immidiately inside the stored procedure, then it all works fine, and 24x7 catches the raiserror and put it in the job log.

If you procedure returns at least 1 result set (e.g. SELECT) make sure it happens at the end of the procedure run, not in the middle.
If this is the case either move the SELECT to the end of save it an a temp table SELECT ... INTO #temp and then at the end do another SELECT from the temp table.
If this is not what you have please post the procedure code.

: Hi,

: Im having problems with calling stored procedures on a SQL server.

: Basicly we have night job, that does some database checking. The stored
: procedure we call takes about 40 seconds to complete, but the scheduler
: exists after 17 secs and dosnt log anything.

: I think it might be some sort of timeout issue, but i cannot be sure.

: my job is : dim Return, Number
: DatabaseConnect "MyConnection"
: DatabaseExecute( "exec owner.MySTPJob", Return )

: The stored procedure has been testet in sql analyzer, but basicly it looks
: like this: CREATE PROCEDURE owner.mystpjob
: as
: declare @count int
: select something that returns a row for each error
: set @count = @@rowcount

: if (@count > 0) begin

: raiserror ('Job error count %d', 16, 1, @count)

: return 1
: end

: return 0

: The database trace is:
: /*---------------------------------------------------*/
: /* 12-02-2004 13:57 */
: /*---------------------------------------------------*/
: (4a809a0): LOGIN: (469 MilliSeconds)
: (4a809a0): CONNECT TO TRACE MSS: (4a809a0): DATABASE=
: (4a809a0): LOGID=
: (4a809a0): LOCK=Read Committed
: (4a809a0): DBPARM=AppName='24x7',Async=1,DBGetTime=300
: (4a809a0): SERVER= (47 MilliSeconds)
: (4a809a0): PREPARE: (0 MilliSeconds)
: (4a809a0): EXECUTE: (4a809a0): exec owner.mystpjob (17140 MilliSeconds)
: (4a809a0): GET AFFECTED ROWS: (0 MilliSeconds)
: (4a809a0): ^ 0 Rows Affected
: (4a809a0): CANCEL: (125 MilliSeconds)
: (4a809a0): COMMIT: (47 MilliSeconds)
: (4a809a0): Error 3902 (rc -1) : The COMMIT TRANSACTION request has no
: corresponding BEGIN TRANSACTION.
: (4a809a0): DISCONNECT: (0 MilliSeconds)
: (4a809a0): SHUTDOWN DATABASE INTERFACE: (0 MilliSeconds)

: I tried to make a raiserror immidiately inside the stored procedure, then it
: all works fine, and 24x7 catches the raiserror and put it in the job log.

The result rows of the procedure is not used.

All i want is a procedure that makes a raiserror if something is wrong, the raiserror will be cought by 24x7 errorhandling and the job will fail.

My problem is that 24x7 does not catch the raiserror from the procedure, probally cause the procedure is taking too long and times out, if i move the raiserror to the top of the procedure and return immidiately 24x7 catches the raiserror properly.

My guess is that 24x7 times out on the procedure call, but i dont get an error code.

DBTrace is included in my first post, so is the procedure code.

: If you procedure returns at least 1 result set (e.g. SELECT) make sure it
: happens at the end of the procedure run, not in the middle.
: If this is the case either move the SELECT to the end of save it an a temp
: table SELECT ... INTO #temp and then at the end do another SELECT from the
: temp table.
: If this is not what you have please post the procedure code.

I don't think it times out. It simply stops the processing as soon as it gets control back from the database. If your database were not return the control back then everything would be ok.
Try this: 1. Set the job to run synchronous. 2. Make sure the asynchronous option in the database profile properties is unchecked. 3. Run the job manually and compare the trace before and after the change.

If this doesn't help find out which particular operation in the stored procedure gets executed last before the control gets back to 24x7. If there are PRINT commands OR untargeted SELECTs (not SELECT INTO and not SELECT…INSERT and not SELECT @variable=) comment them out. If you call other procedure from your procedure or do an INSERT/DELETE/UPDATE on a table with a trigger make sure you don’t have untargeted PRINT/SELECT in these callable programs.

: The result rows of the procedure is not used.

: All i want is a procedure that makes a raiserror if something is wrong, the
: raiserror will be cought by 24x7 errorhandling and the job will fail.

: My problem is that 24x7 does not catch the raiserror from the procedure,
: probally cause the procedure is taking too long and times out, if i move
: the raiserror to the top of the procedure and return immidiately 24x7
: catches the raiserror properly.

: My guess is that 24x7 times out on the procedure call, but i dont get an
: error code.

: DBTrace is included in my first post, so is the procedure code.