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
