Execute proceduers from another Proceduer with error handling

I need some help to solve this problem with some stored procedures.
Sens we use ControlM I need to schedule jobs there and not in SQL. With
a simple batchfile I execute an OSQL from ControlM that start a
procedure that calls the other procedures that should bee used. My
problem are the error handling, how would I do to stop the execution of
SP if one of the fails? Any one got an idea?

I figured out the first simple step below :-)

CREATE PROCEDURE [dbo].[USP_RUNJOB] AS

EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO

Regard JoelCheck the proc return code and raise an error with state 127 on failure.
OSQL will then terminate. For example:

DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST1
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST1 return code is %d', 16, 127,
@.ReturnCode)
END
GO

DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST2
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST2 return code is %d', 16, 127,
@.ReturnCode)
END

--
Hope this helps.

Dan Guzman
SQL Server MVP

<joel.sjoo@.gmail.comwrote in message
news:1166184223.625115.296200@.79g2000cws.googlegro ups.com...

Quote:

Originally Posted by

>I need some help to solve this problem with some stored procedures.
Sens we use ControlM I need to schedule jobs there and not in SQL. With
a simple batchfile I execute an OSQL from ControlM that start a
procedure that calls the other procedures that should bee used. My
problem are the error handling, how would I do to stop the execution of
SP if one of the fails? Any one got an idea?
>
I figured out the first simple step below :-)
>
CREATE PROCEDURE [dbo].[USP_RUNJOB] AS
>
EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO
>
>
Regard Joel
>

|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:

Quote:

Originally Posted by

Check the proc return code and raise an error with state 127 on failure.
OSQL will then terminate. For example:
>
DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST1
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST1 return code is %d', 16, 127,
@.ReturnCode)
END
GO
>
DECLARE @.ReturnCode int
EXEC @.ReturnCode = dbo.usp_TEST2
IF @.ReturnCode <0
BEGIN
RAISERROR('Procedure dbo.usp_TEST2 return code is %d', 16, 127,
@.ReturnCode)
END

Even better is this check:

IF @.Returcode <0 OR @.@.error <0

The procedure may not set a return code in case of errors, and there
are errors where the proceudure does not return a value at all. (More
precisely compilation error, in which case the procedure is terminated
and execution continues with the next statement.)

If Joel is on SQL 2005 he should of course use TRY CATCH, but since he
using OSQL, I assmue that he is on SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||(joel.sjoo@.gmail.com) writes:

Quote:

Originally Posted by

I figured out the first simple step below :-)
>
CREATE PROCEDURE [dbo].[USP_RUNJOB] AS
>
EXEC DBO.SP_TEST1
GO
EXEC DBO.SP_TEST2
GO

I don't relly know what this is supposed to be, but note that the first
GO marks the end of USP_RUNJOB, so the call to SP_TEST2 is not part of
that procedure.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

execute batch file as sql job

I'm trying schedule a batch file to run as a job from sql server agent. The batch file copies files from one server directory to a directory on another server. The batch file works properly when executed directly. The job is being executed under the sql service account login. I've given the service account access to both the source and destination directories.

When I try to run the job it fails with an "Access is denied" error on both the source and destination directories (as read from job history).

Any ideas?I'd try to log into the server under the sql service acct and try to execute the batch file directly that way. It may give you some more clues.

Beyond that, maybe place some output in the SP to verify the user it is running under when executed from the job.
SELECT SYSTEM_USER|||I can log in and run the job under the service account. I imagine there must be some kind of proxy account or something that it's run under. Like aspuser in .net.|||Create a simple table with a varchar column in it. From the stored procedure, insert SYSTEM_USER into the table. After the job executes, check the table and see what the user name was.
Something like:
CREATE tmpTable (c1 varchar(30) )
INSERT INTO tmpTable SELECT SYSTEM_USER|||maske sure the sql service is running under a domain account and that account has permissions over both directories.