Job Aborting ??

I have a job created on my server which runs a stored procedure. The
SP uses a cursor on a recordset to insert rows into a table (via a
stored procedure - hence the cursor).
It is expected that some inserts will fail due to missing information
or that a record already exists for it. One of the main reasons for me
using the cursor is so that ALL the data is not rolled back when this
occurrs.
But it seems that when I run the job, the job is aborting after a few
error messages. When I execute the SP throught query analyzer, it
completes. I can tell this is happening because of the difference in
row numbers afterwards. Another indication that the job is aborting is
that a summary message does not show up in the job history.
What could be the cause of the job aborting? What other info do I need
to provide? (Me posting table schemas and sample data may not help
since I could not give you a large enough dataset to simulate.)
Job Output (from history)
--
Executed as user: LSP\sa. Based on security type [Future], the
underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
[SQLSTATE 42000] (Error 50000) Associated statement is not prepared
[SQLSTATE HY007] (Error 0) Based on security type [Future], the
underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
[SQLSTATE 42000] (Error 50000). The step failed.
Count after Job
--
1737
Query Analyzer Output (notice last line summary output)
--
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: GGZ3 9999/GGZ3 XNAS
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: GGZ3 9999/GGZ3 XNAS
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P GGZ3
9999/GGZ3
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C GGZ3
9999/GGZ3
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: ERH4 9999/ERH4 XNAS
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: ERZ3 9999/ERZ3 XNAS
...
... and so on
...
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: NQH4 9999/NQH4 XNAS
SecurityMaster load completed : 6345 success, 0 skipped, 0
unqualified, 97 failed
Count after Job
--
6342Jason,
That is just how Agent work for TSQL jobsteps. It will abort processing if any error. I suggest you
put your TSQL in a script file and use a CmdExec jobstep instead, and use OSQL.EXE with the script
as an input file.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason Callas" <JayCallas@.hotmail.com> wrote in message
news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> I have a job created on my server which runs a stored procedure. The
> SP uses a cursor on a recordset to insert rows into a table (via a
> stored procedure - hence the cursor).
> It is expected that some inserts will fail due to missing information
> or that a record already exists for it. One of the main reasons for me
> using the cursor is so that ALL the data is not rolled back when this
> occurrs.
> But it seems that when I run the job, the job is aborting after a few
> error messages. When I execute the SP throught query analyzer, it
> completes. I can tell this is happening because of the difference in
> row numbers afterwards. Another indication that the job is aborting is
> that a summary message does not show up in the job history.
> What could be the cause of the job aborting? What other info do I need
> to provide? (Me posting table schemas and sample data may not help
> since I could not give you a large enough dataset to simulate.)
> Job Output (from history)
> --
> Executed as user: LSP\sa. Based on security type [Future], the
> underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> [SQLSTATE 42000] (Error 50000). The step failed.
> Count after Job
> --
> 1737
> Query Analyzer Output (notice last line summary output)
> --
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: GGZ3 9999/GGZ3 XNAS
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: GGZ3 9999/GGZ3 XNAS
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P GGZ3
> 9999/GGZ3
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C GGZ3
> 9999/GGZ3
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: ERH4 9999/ERH4 XNAS
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: ERZ3 9999/ERZ3 XNAS
> ...
> ... and so on
> ...
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: NQH4 9999/NQH4 XNAS
> SecurityMaster load completed : 6345 success, 0 skipped, 0
> unqualified, 97 failed
> Count after Job
> --
> 6342
>|||What you said makes sense but I am unable to prove it.
Given the following code, the print statement 'Got to here' should NOT print
when run as a job but it does.
USE TempDb
GO
IF EXISTS (SELECT * FROM sysobjects WHERE [id] =object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id], N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spTest]
GO
CREATE PROCEDURE spTest
AS
SET NOCOUNT ON
RAISERROR('Test error', 15, 1)
PRINT 'Got to end'
GO
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> Jason,
> That is just how Agent work for TSQL jobsteps. It will abort processing if
any error. I suggest you
> put your TSQL in a script file and use a CmdExec jobstep instead, and use
OSQL.EXE with the script
> as an input file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > I have a job created on my server which runs a stored procedure. The
> > SP uses a cursor on a recordset to insert rows into a table (via a
> > stored procedure - hence the cursor).
> >
> > It is expected that some inserts will fail due to missing information
> > or that a record already exists for it. One of the main reasons for me
> > using the cursor is so that ALL the data is not rolled back when this
> > occurrs.
> >
> > But it seems that when I run the job, the job is aborting after a few
> > error messages. When I execute the SP throught query analyzer, it
> > completes. I can tell this is happening because of the difference in
> > row numbers afterwards. Another indication that the job is aborting is
> > that a summary message does not show up in the job history.
> >
> > What could be the cause of the job aborting? What other info do I need
> > to provide? (Me posting table schemas and sample data may not help
> > since I could not give you a large enough dataset to simulate.)
> >
> > Job Output (from history)
> > --
> > Executed as user: LSP\sa. Based on security type [Future], the
> > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > [SQLSTATE 42000] (Error 50000). The step failed.
> >
> > Count after Job
> > --
> > 1737
> >
> > Query Analyzer Output (notice last line summary output)
> > --
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: GGZ3 9999/GGZ3 XNAS
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: GGZ3 9999/GGZ3 XNAS
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> > The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P GGZ3
> > 9999/GGZ3
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> > The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C GGZ3
> > 9999/GGZ3
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: ERH4 9999/ERH4 XNAS
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: ERZ3 9999/ERZ3 XNAS
> > ...
> > ... and so on
> > ...
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: NQH4 9999/NQH4 XNAS
> > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > unqualified, 97 failed
> >
> > Count after Job
> > --
> > 6342
> >
> >
>|||I created a TSQL jobstep where I call the proc and an output file. I did not get the print in the
output file. Do you see anything else?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason Callas" <JayCallas@.hotmail.com> wrote in message
news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> What you said makes sense but I am unable to prove it.
> Given the following code, the print statement 'Got to here' should NOT print
> when run as a job but it does.
> USE TempDb
> GO
> IF EXISTS (SELECT * FROM sysobjects WHERE [id] => object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id], N'IsProcedure') = 1)
> DROP PROCEDURE [dbo].[spTest]
> GO
> CREATE PROCEDURE spTest
> AS
> SET NOCOUNT ON
> RAISERROR('Test error', 15, 1)
> PRINT 'Got to end'
> GO
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > Jason,
> >
> > That is just how Agent work for TSQL jobsteps. It will abort processing if
> any error. I suggest you
> > put your TSQL in a script file and use a CmdExec jobstep instead, and use
> OSQL.EXE with the script
> > as an input file.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > I have a job created on my server which runs a stored procedure. The
> > > SP uses a cursor on a recordset to insert rows into a table (via a
> > > stored procedure - hence the cursor).
> > >
> > > It is expected that some inserts will fail due to missing information
> > > or that a record already exists for it. One of the main reasons for me
> > > using the cursor is so that ALL the data is not rolled back when this
> > > occurrs.
> > >
> > > But it seems that when I run the job, the job is aborting after a few
> > > error messages. When I execute the SP throught query analyzer, it
> > > completes. I can tell this is happening because of the difference in
> > > row numbers afterwards. Another indication that the job is aborting is
> > > that a summary message does not show up in the job history.
> > >
> > > What could be the cause of the job aborting? What other info do I need
> > > to provide? (Me posting table schemas and sample data may not help
> > > since I could not give you a large enough dataset to simulate.)
> > >
> > > Job Output (from history)
> > > --
> > > Executed as user: LSP\sa. Based on security type [Future], the
> > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > [SQLSTATE 42000] (Error 50000). The step failed.
> > >
> > > Count after Job
> > > --
> > > 1737
> > >
> > > Query Analyzer Output (notice last line summary output)
> > > --
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: GGZ3 9999/GGZ3 XNAS
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: GGZ3 9999/GGZ3 XNAS
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> > > The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P GGZ3
> > > 9999/GGZ3
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> > > The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C GGZ3
> > > 9999/GGZ3
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: ERH4 9999/ERH4 XNAS
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: ERZ3 9999/ERZ3 XNAS
> > > ...
> > > ... and so on
> > > ...
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: NQH4 9999/NQH4 XNAS
> > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > unqualified, 97 failed
> > >
> > > Count after Job
> > > --
> > > 6342
> > >
> > >
> >
> >
>|||This is the output from job history.
Executed as user: LSP\sa. Test Error [SQLSTATE 42000] (Error 50000) Got to
end [SQLSTATE 01000] (Error 0). The step failed.
Could you post your code you I can try it on my system?
Thanks,
Jason
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:enWaMPXxDHA.556@.TK2MSFTNGP11.phx.gbl...
> I created a TSQL jobstep where I call the proc and an output file. I did
not get the print in the
> output file. Do you see anything else?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> > What you said makes sense but I am unable to prove it.
> >
> > Given the following code, the print statement 'Got to here' should NOT
print
> > when run as a job but it does.
> >
> > USE TempDb
> > GO
> >
> > IF EXISTS (SELECT * FROM sysobjects WHERE [id] => > object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id], N'IsProcedure') =1)
> > DROP PROCEDURE [dbo].[spTest]
> > GO
> >
> > CREATE PROCEDURE spTest
> > AS
> > SET NOCOUNT ON
> >
> > RAISERROR('Test error', 15, 1)
> >
> > PRINT 'Got to end'
> > GO
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > Jason,
> > >
> > > That is just how Agent work for TSQL jobsteps. It will abort
processing if
> > any error. I suggest you
> > > put your TSQL in a script file and use a CmdExec jobstep instead, and
use
> > OSQL.EXE with the script
> > > as an input file.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > I have a job created on my server which runs a stored procedure. The
> > > > SP uses a cursor on a recordset to insert rows into a table (via a
> > > > stored procedure - hence the cursor).
> > > >
> > > > It is expected that some inserts will fail due to missing
information
> > > > or that a record already exists for it. One of the main reasons for
me
> > > > using the cursor is so that ALL the data is not rolled back when
this
> > > > occurrs.
> > > >
> > > > But it seems that when I run the job, the job is aborting after a
few
> > > > error messages. When I execute the SP throught query analyzer, it
> > > > completes. I can tell this is happening because of the difference in
> > > > row numbers afterwards. Another indication that the job is aborting
is
> > > > that a summary message does not show up in the job history.
> > > >
> > > > What could be the cause of the job aborting? What other info do I
need
> > > > to provide? (Me posting table schemas and sample data may not help
> > > > since I could not give you a large enough dataset to simulate.)
> > > >
> > > > Job Output (from history)
> > > > --
> > > > Executed as user: LSP\sa. Based on security type [Future], the
> > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > > [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> > > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > > [SQLSTATE 42000] (Error 50000). The step failed.
> > > >
> > > > Count after Job
> > > > --
> > > > 1737
> > > >
> > > > Query Analyzer Output (notice last line summary output)
> > > > --
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
95
> > > > The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P
GGZ3
> > > > 9999/GGZ3
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
95
> > > > The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C
GGZ3
> > > > 9999/GGZ3
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: ERH4 9999/ERH4 XNAS
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: ERZ3 9999/ERZ3 XNAS
> > > > ...
> > > > ... and so on
> > > > ...
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: NQH4 9999/NQH4 XNAS
> > > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > > unqualified, 97 failed
> > > >
> > > > Count after Job
> > > > --
> > > > 6342
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yes, indeed, you are right. I see that now when looking a bit closer (my class was about to start,
so I was a bit hasty when looking as the output earlier). So, it seems like Agent will stop
processing under *some* circumstances (evidently) and I guess one could do some research to find out
under what circumstances. Fact is, however, that Agent will stop processing under some circumstances
and a way around that is to execute the SQL code from OSQL instead of Agent. :-)
...
I tried one more thing. Adding a second PRINT after the proc call didn't make a difference. However,
when adding another batch (GO) that batch was not executed if the first had an error, while it was
executed if the first one was execute OK. I guess it all makes sense if you think about how batches
are handled in SQL Server.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason Callas" <JayCallas@.hotmail.com> wrote in message
news:%23eBHumXxDHA.1576@.TK2MSFTNGP11.phx.gbl...
> This is the output from job history.
> Executed as user: LSP\sa. Test Error [SQLSTATE 42000] (Error 50000) Got to
> end [SQLSTATE 01000] (Error 0). The step failed.
> Could you post your code you I can try it on my system?
> Thanks,
> Jason
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:enWaMPXxDHA.556@.TK2MSFTNGP11.phx.gbl...
> > I created a TSQL jobstep where I call the proc and an output file. I did
> not get the print in the
> > output file. Do you see anything else?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> > > What you said makes sense but I am unable to prove it.
> > >
> > > Given the following code, the print statement 'Got to here' should NOT
> print
> > > when run as a job but it does.
> > >
> > > USE TempDb
> > > GO
> > >
> > > IF EXISTS (SELECT * FROM sysobjects WHERE [id] => > > object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id], N'IsProcedure') => 1)
> > > DROP PROCEDURE [dbo].[spTest]
> > > GO
> > >
> > > CREATE PROCEDURE spTest
> > > AS
> > > SET NOCOUNT ON
> > >
> > > RAISERROR('Test error', 15, 1)
> > >
> > > PRINT 'Got to end'
> > > GO
> > >
> > > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > > Jason,
> > > >
> > > > That is just how Agent work for TSQL jobsteps. It will abort
> processing if
> > > any error. I suggest you
> > > > put your TSQL in a script file and use a CmdExec jobstep instead, and
> use
> > > OSQL.EXE with the script
> > > > as an input file.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > I have a job created on my server which runs a stored procedure. The
> > > > > SP uses a cursor on a recordset to insert rows into a table (via a
> > > > > stored procedure - hence the cursor).
> > > > >
> > > > > It is expected that some inserts will fail due to missing
> information
> > > > > or that a record already exists for it. One of the main reasons for
> me
> > > > > using the cursor is so that ALL the data is not rolled back when
> this
> > > > > occurrs.
> > > > >
> > > > > But it seems that when I run the job, the job is aborting after a
> few
> > > > > error messages. When I execute the SP throught query analyzer, it
> > > > > completes. I can tell this is happening because of the difference in
> > > > > row numbers afterwards. Another indication that the job is aborting
> is
> > > > > that a summary message does not show up in the job history.
> > > > >
> > > > > What could be the cause of the job aborting? What other info do I
> need
> > > > > to provide? (Me posting table schemas and sample data may not help
> > > > > since I could not give you a large enough dataset to simulate.)
> > > > >
> > > > > Job Output (from history)
> > > > > --
> > > > > Executed as user: LSP\sa. Based on security type [Future], the
> > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > > > [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> > > > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > > > [SQLSTATE 42000] (Error 50000). The step failed.
> > > > >
> > > > > Count after Job
> > > > > --
> > > > > 1737
> > > > >
> > > > > Query Analyzer Output (notice last line summary output)
> > > > > --
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 95
> > > > > The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P
> GGZ3
> > > > > 9999/GGZ3
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 95
> > > > > The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C
> GGZ3
> > > > > 9999/GGZ3
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: ERH4 9999/ERH4 XNAS
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: ERZ3 9999/ERZ3 XNAS
> > > > > ...
> > > > > ... and so on
> > > > > ...
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: NQH4 9999/NQH4 XNAS
> > > > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > > > unqualified, 97 failed
> > > > >
> > > > > Count after Job
> > > > > --
> > > > > 6342
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I modified my problem job to use osql and seems to be running as expected.
What is the difference in how Agent submits jobs and how OSQL does?
Thanks for all your help.
- Jason
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eDkPJwXxDHA.3416@.tk2msftngp13.phx.gbl...
> Yes, indeed, you are right. I see that now when looking a bit closer (my
class was about to start,
> so I was a bit hasty when looking as the output earlier). So, it seems
like Agent will stop
> processing under *some* circumstances (evidently) and I guess one could do
some research to find out
> under what circumstances. Fact is, however, that Agent will stop
processing under some circumstances
> and a way around that is to execute the SQL code from OSQL instead of
Agent. :-)
> ...
> I tried one more thing. Adding a second PRINT after the proc call didn't
make a difference. However,
> when adding another batch (GO) that batch was not executed if the first
had an error, while it was
> executed if the first one was execute OK. I guess it all makes sense if
you think about how batches
> are handled in SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> news:%23eBHumXxDHA.1576@.TK2MSFTNGP11.phx.gbl...
> > This is the output from job history.
> >
> > Executed as user: LSP\sa. Test Error [SQLSTATE 42000] (Error 50000) Got
to
> > end [SQLSTATE 01000] (Error 0). The step failed.
> >
> > Could you post your code you I can try it on my system?
> >
> > Thanks,
> > Jason
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:enWaMPXxDHA.556@.TK2MSFTNGP11.phx.gbl...
> > > I created a TSQL jobstep where I call the proc and an output file. I
did
> > not get the print in the
> > > output file. Do you see anything else?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> > > > What you said makes sense but I am unable to prove it.
> > > >
> > > > Given the following code, the print statement 'Got to here' should
NOT
> > print
> > > > when run as a job but it does.
> > > >
> > > > USE TempDb
> > > > GO
> > > >
> > > > IF EXISTS (SELECT * FROM sysobjects WHERE [id] => > > > object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id],
N'IsProcedure') => > 1)
> > > > DROP PROCEDURE [dbo].[spTest]
> > > > GO
> > > >
> > > > CREATE PROCEDURE spTest
> > > > AS
> > > > SET NOCOUNT ON
> > > >
> > > > RAISERROR('Test error', 15, 1)
> > > >
> > > > PRINT 'Got to end'
> > > > GO
> > > >
> > > > "Tibor Karaszi"
> > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > > wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > > > Jason,
> > > > >
> > > > > That is just how Agent work for TSQL jobsteps. It will abort
> > processing if
> > > > any error. I suggest you
> > > > > put your TSQL in a script file and use a CmdExec jobstep instead,
and
> > use
> > > > OSQL.EXE with the script
> > > > > as an input file.
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > Archive at:
> > > >
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > > >
> > > > >
> > > > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > > > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > > I have a job created on my server which runs a stored procedure.
The
> > > > > > SP uses a cursor on a recordset to insert rows into a table (via
a
> > > > > > stored procedure - hence the cursor).
> > > > > >
> > > > > > It is expected that some inserts will fail due to missing
> > information
> > > > > > or that a record already exists for it. One of the main reasons
for
> > me
> > > > > > using the cursor is so that ALL the data is not rolled back when
> > this
> > > > > > occurrs.
> > > > > >
> > > > > > But it seems that when I run the job, the job is aborting after
a
> > few
> > > > > > error messages. When I execute the SP throught query analyzer,
it
> > > > > > completes. I can tell this is happening because of the
difference in
> > > > > > row numbers afterwards. Another indication that the job is
aborting
> > is
> > > > > > that a summary message does not show up in the job history.
> > > > > >
> > > > > > What could be the cause of the job aborting? What other info do
I
> > need
> > > > > > to provide? (Me posting table schemas and sample data may not
help
> > > > > > since I could not give you a large enough dataset to simulate.)
> > > > > >
> > > > > > Job Output (from history)
> > > > > > --
> > > > > > Executed as user: LSP\sa. Based on security type [Future], the
> > > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3
XNAS
> > > > > > [SQLSTATE 42000] (Error 50000) Associated statement is not
prepared
> > > > > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3
XNAS
> > > > > > [SQLSTATE 42000] (Error 50000). The step failed.
> > > > > >
> > > > > > Count after Job
> > > > > > --
> > > > > > 1737
> > > > > >
> > > > > > Query Analyzer Output (notice last line summary output)
> > > > > > --
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 95
> > > > > > The underlying security does not exist: GGZ33DEC420P
GGZ33DEC420P
> > GGZ3
> > > > > > 9999/GGZ3
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 95
> > > > > > The underlying security does not exist: GGZ33DEC460C
GGZ33DEC460C
> > GGZ3
> > > > > > 9999/GGZ3
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: ERH4 9999/ERH4 XNAS
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: ERZ3 9999/ERZ3 XNAS
> > > > > > ...
> > > > > > ... and so on
> > > > > > ...
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: NQH4 9999/NQH4 XNAS
> > > > > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > > > > unqualified, 97 failed
> > > > > >
> > > > > > Count after Job
> > > > > > --
> > > > > > 6342
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> What is the difference in how Agent submits jobs and how OSQL does?
AFAIK, both use ODBC. Apart from that, I don't know of any technical details released on the
subject.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason Callas" <JayCallas@.hotmail.com> wrote in message
news:eBTMeDYxDHA.2712@.tk2msftngp13.phx.gbl...
> I modified my problem job to use osql and seems to be running as expected.
> What is the difference in how Agent submits jobs and how OSQL does?
> Thanks for all your help.
> - Jason
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eDkPJwXxDHA.3416@.tk2msftngp13.phx.gbl...
> > Yes, indeed, you are right. I see that now when looking a bit closer (my
> class was about to start,
> > so I was a bit hasty when looking as the output earlier). So, it seems
> like Agent will stop
> > processing under *some* circumstances (evidently) and I guess one could do
> some research to find out
> > under what circumstances. Fact is, however, that Agent will stop
> processing under some circumstances
> > and a way around that is to execute the SQL code from OSQL instead of
> Agent. :-)
> > ...
> >
> > I tried one more thing. Adding a second PRINT after the proc call didn't
> make a difference. However,
> > when adding another batch (GO) that batch was not executed if the first
> had an error, while it was
> > executed if the first one was execute OK. I guess it all makes sense if
> you think about how batches
> > are handled in SQL Server.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > news:%23eBHumXxDHA.1576@.TK2MSFTNGP11.phx.gbl...
> > > This is the output from job history.
> > >
> > > Executed as user: LSP\sa. Test Error [SQLSTATE 42000] (Error 50000) Got
> to
> > > end [SQLSTATE 01000] (Error 0). The step failed.
> > >
> > > Could you post your code you I can try it on my system?
> > >
> > > Thanks,
> > > Jason
> > >
> > > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > wrote in message news:enWaMPXxDHA.556@.TK2MSFTNGP11.phx.gbl...
> > > > I created a TSQL jobstep where I call the proc and an output file. I
> did
> > > not get the print in the
> > > > output file. Do you see anything else?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > > news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> > > > > What you said makes sense but I am unable to prove it.
> > > > >
> > > > > Given the following code, the print statement 'Got to here' should
> NOT
> > > print
> > > > > when run as a job but it does.
> > > > >
> > > > > USE TempDb
> > > > > GO
> > > > >
> > > > > IF EXISTS (SELECT * FROM sysobjects WHERE [id] => > > > > object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id],
> N'IsProcedure') => > > 1)
> > > > > DROP PROCEDURE [dbo].[spTest]
> > > > > GO
> > > > >
> > > > > CREATE PROCEDURE spTest
> > > > > AS
> > > > > SET NOCOUNT ON
> > > > >
> > > > > RAISERROR('Test error', 15, 1)
> > > > >
> > > > > PRINT 'Got to end'
> > > > > GO
> > > > >
> > > > > "Tibor Karaszi"
> > > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > > > wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > > > > Jason,
> > > > > >
> > > > > > That is just how Agent work for TSQL jobsteps. It will abort
> > > processing if
> > > > > any error. I suggest you
> > > > > > put your TSQL in a script file and use a CmdExec jobstep instead,
> and
> > > use
> > > > > OSQL.EXE with the script
> > > > > > as an input file.
> > > > > >
> > > > > > --
> > > > > > Tibor Karaszi, SQL Server MVP
> > > > > > Archive at:
> > > > >
> > >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > > > >
> > > > > >
> > > > > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > > > > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > > > I have a job created on my server which runs a stored procedure.
> The
> > > > > > > SP uses a cursor on a recordset to insert rows into a table (via
> a
> > > > > > > stored procedure - hence the cursor).
> > > > > > >
> > > > > > > It is expected that some inserts will fail due to missing
> > > information
> > > > > > > or that a record already exists for it. One of the main reasons
> for
> > > me
> > > > > > > using the cursor is so that ALL the data is not rolled back when
> > > this
> > > > > > > occurrs.
> > > > > > >
> > > > > > > But it seems that when I run the job, the job is aborting after
> a
> > > few
> > > > > > > error messages. When I execute the SP throught query analyzer,
> it
> > > > > > > completes. I can tell this is happening because of the
> difference in
> > > > > > > row numbers afterwards. Another indication that the job is
> aborting
> > > is
> > > > > > > that a summary message does not show up in the job history.
> > > > > > >
> > > > > > > What could be the cause of the job aborting? What other info do
> I
> > > need
> > > > > > > to provide? (Me posting table schemas and sample data may not
> help
> > > > > > > since I could not give you a large enough dataset to simulate.)
> > > > > > >
> > > > > > > Job Output (from history)
> > > > > > > --
> > > > > > > Executed as user: LSP\sa. Based on security type [Future], the
> > > > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3
> XNAS
> > > > > > > [SQLSTATE 42000] (Error 50000) Associated statement is not
> prepared
> > > > > > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3
> XNAS
> > > > > > > [SQLSTATE 42000] (Error 50000). The step failed.
> > > > > > >
> > > > > > > Count after Job
> > > > > > > --
> > > > > > > 1737
> > > > > > >
> > > > > > > Query Analyzer Output (notice last line summary output)
> > > > > > > --
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 95
> > > > > > > The underlying security does not exist: GGZ33DEC420P
> GGZ33DEC420P
> > > GGZ3
> > > > > > > 9999/GGZ3
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 95
> > > > > > > The underlying security does not exist: GGZ33DEC460C
> GGZ33DEC460C
> > > GGZ3
> > > > > > > 9999/GGZ3
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: ERH4 9999/ERH4 XNAS
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: ERZ3 9999/ERZ3 XNAS
> > > > > > > ...
> > > > > > > ... and so on
> > > > > > > ...
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: NQH4 9999/NQH4 XNAS
> > > > > > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > > > > > unqualified, 97 failed
> > > > > > >
> > > > > > > Count after Job
> > > > > > > --
> > > > > > > 6342
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>