Job with stored proc succeeds with sqlstate 01000

I have a stored procedure that I am executing through the sql server
job scheduler, it executes properly but after each line in the log file
there is a sqlstate message.
procedure name p_document_purge [SQLSTATE 01000]
Archiving records [SQLSTATE 01000]
51 rows archived [SQLSTATE 01000]
Purging duplicate records [SQLSTATE 01000]
51 rows purged [SQLSTATE 01000]
Purge completed successfully [SQLSTATE 01000]
Does anyone know what might cause this? The @.@.ERROR during the
procedure is always 0 or it would rollback the entire transaction. Also
If I run the proc from query analyzer I do not get any negative
feedback regarding it. I tried looking in the documentation, but this
is listed as a general error which doesn't really help.
Thanks
BillPrint statements in the stored procedure can result in the
sqlstate message.
-Sue
.
On 23 Feb 2006 06:30:49 -0800, william_dudek@.yahoo.com
wrote:

>I have a stored procedure that I am executing through the sql server
>job scheduler, it executes properly but after each line in the log file
>there is a sqlstate message.
>procedure name p_document_purge [SQLSTATE 01000]
>Archiving records [SQLSTATE 01000]
>51 rows archived [SQLSTATE 01000]
>Purging duplicate records [SQLSTATE 01000]
>51 rows purged [SQLSTATE 01000]
>Purge completed successfully [SQLSTATE 01000]
>Does anyone know what might cause this? The @.@.ERROR during the
>procedure is always 0 or it would rollback the entire transaction. Also
>If I run the proc from query analyzer I do not get any negative
>feedback regarding it. I tried looking in the documentation, but this
>is listed as a general error which doesn't really help.
>Thanks
>Bill

Job with stored proc succeeds with sqlstate 01000

I have a stored procedure that I am executing through the sql server
job scheduler, it executes properly but after each line in the log file
there is a sqlstate message.
procedure name p_document_purge [SQLSTATE 01000]
Archiving records [SQLSTATE 01000]
51 rows archived [SQLSTATE 01000]
Purging duplicate records [SQLSTATE 01000]
51 rows purged [SQLSTATE 01000]
Purge completed successfully [SQLSTATE 01000]
Does anyone know what might cause this? The @.@.ERROR during the
procedure is always 0 or it would rollback the entire transaction. Also
If I run the proc from query analyzer I do not get any negative
feedback regarding it. I tried looking in the documentation, but this
is listed as a general error which doesn't really help.
Thanks
BillPrint statements in the stored procedure can result in the
sqlstate message.
-Sue
.
On 23 Feb 2006 06:30:49 -0800, william_dudek@.yahoo.com
wrote:
>I have a stored procedure that I am executing through the sql server
>job scheduler, it executes properly but after each line in the log file
>there is a sqlstate message.
>procedure name p_document_purge [SQLSTATE 01000]
>Archiving records [SQLSTATE 01000]
>51 rows archived [SQLSTATE 01000]
>Purging duplicate records [SQLSTATE 01000]
>51 rows purged [SQLSTATE 01000]
>Purge completed successfully [SQLSTATE 01000]
>Does anyone know what might cause this? The @.@.ERROR during the
>procedure is always 0 or it would rollback the entire transaction. Also
>If I run the proc from query analyzer I do not get any negative
>feedback regarding it. I tried looking in the documentation, but this
>is listed as a general error which doesn't really help.
>Thanks
>Bill

Job with stored proc succeeds with sqlstate 01000

I have a stored procedure that I am executing through the sql server
job scheduler, it executes properly but after each line in the log file
there is a sqlstate message.
procedure name p_document_purge [SQLSTATE 01000]
Archiving records [SQLSTATE 01000]
51 rows archived [SQLSTATE 01000]
Purging duplicate records [SQLSTATE 01000]
51 rows purged [SQLSTATE 01000]
Purge completed successfully [SQLSTATE 01000]
Does anyone know what might cause this? The @.@.ERROR during the
procedure is always 0 or it would rollback the entire transaction. Also
If I run the proc from query analyzer I do not get any negative
feedback regarding it. I tried looking in the documentation, but this
is listed as a general error which doesn't really help.
Thanks
Bill
Print statements in the stored procedure can result in the
sqlstate message.
-Sue
..
On 23 Feb 2006 06:30:49 -0800, william_dudek@.yahoo.com
wrote:

>I have a stored procedure that I am executing through the sql server
>job scheduler, it executes properly but after each line in the log file
>there is a sqlstate message.
>procedure name p_document_purge [SQLSTATE 01000]
>Archiving records [SQLSTATE 01000]
>51 rows archived [SQLSTATE 01000]
>Purging duplicate records [SQLSTATE 01000]
>51 rows purged [SQLSTATE 01000]
>Purge completed successfully [SQLSTATE 01000]
>Does anyone know what might cause this? The @.@.ERROR during the
>procedure is always 0 or it would rollback the entire transaction. Also
>If I run the proc from query analyzer I do not get any negative
>feedback regarding it. I tried looking in the documentation, but this
>is listed as a general error which doesn't really help.
>Thanks
>Bill
sql

Job Step

Hi All,

I am executing a stored procedure within a job step. This stored procedure executes the xp_sqlmaint stored procedure with some parameters. I run this job as 'sa'.
The job fails with the following error message:

sqlmaint.exe failed. Step failed.

What could cause this error? Please help. Thanks.Copy and paste the sqlmaint command to Query Analyzer and see if you get more informative error messages.|||This is the stored procedure that I have created.

CREATE PROCEDURE usp_RebuildIndexes

AS

DECLARE @.PlanID char(36)
SET @.PlanID = NEWID()

EXECUTE master.dbo.xp_sqlmaint N'-PlanID '' + @.PlanID + '' -Rpt "M:\MaintPlanRpts\Redlight Maintenance Plan0.txt" -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10 '

Then within the job step I run:

execute msdb.dbo.usp_RebuildIndexes

and when I run the job I get that error message.|||You seem to be confused about PlanIDs. They are created by the Maintenance Plan Wizard and have to correspond to an existing Maintenance Plan. You are create a random PlanID on the fly using newid(), which of course does not correspond to any existing plan, so your sqlmaint statement fails.

Here's a tip: you can refer to your maintenance plans by name rather than by PlanID by using the "N'-PlanName" parameter. Take a few minutes with Books Online to review all the parameters that are available for sql_maint. It is quite powerful.|||Thank you very much for your help.|||This is what the job step looks like now and I am still getting the same error message.

EXECUTE master.dbo.xp_sqlmaint N'-PlanName OptimizationPlan -Rpt "M:\MaintPlanRpts\Redlight Maintenance Plan0.txt" -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10 '

What am I doing wrong now?|||SQL Server does not like double quotes. Try this:EXECUTE master.dbo.xp_sqlmaint N'-PlanName OptimizationPlan -Rpt M:\MaintPlanRpts\Redlight Maintenance Plan0.txt -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10'|||Just tried it. Same error message.|||Ok. Try eliminating the spaces in your file name. SQLMaint may be interpreting as separate parameters:
EXECUTE master.dbo.xp_sqlmaint N'-PlanName OptimizationPlan -Rpt M:\MaintPlanRpts\Redlight_Maintenance_Plan0.txt -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10'|||That didn't work either. Same error.|||Not sure what is going on then. Is that the error you get when you run the statement through Query Analyzer?

Try removing parameters one at a time to narrow in on the culprit.|||I executed it as a step in the job in EM. When I looked in the maintenance report I found the following error:

Error 21268: [SQL-DMO]Row or column specified is outside the range of the specified query result set.|||That's the error I got when I executed your statement in Query Analyzer, so try dropping off parameters to see which one is causing the problem.|||Once I added the Server name, the user and the password to the script it worked. I made this user a sysadmin.

So now it looks like this:

EXEC master.dbo.xp_sqlmaint ' -S servername -U user -P password
-PlanName OptimizationPlan -Rpt "C:\MaintPlanRpts\Plan.txt" -DelTxtRpt 1DAYS -WriteHistory -RebldIdx 10 -RmUnusedSpace 25 10 '

I don't understand why I need to supply these 3 parameters. Any ideas?|||I would guess it has something to do with the accounts under which you, SQL Server, and SQL Server Agent are logging in.|||Thanks for the help.

Job Scheduler

Hi all
When I run a stored procedure using the job scheduler in
SQL 2000 it takes more time to complete comparing when I
run it in the Query analyser.
Is there any reason behind this?
Example
I am running a stored Procedure for generating a report
every one minute using a job scheduler.
It takes 13 seconds to complete.
Whereas when I run the same SP in Query Analyser it takes
only 2 sec or 3 seconds.
Please clarify.
Thanks in advance
AnandThis could be the problem:
http://support.microsoft.com/defaul...730&Product=sql
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Anand" <gurusanand@.yahoo.com> wrote in message
news:024101c3db49$baff3990$a001280a@.phx.gbl...
Hi all
When I run a stored procedure using the job scheduler in
SQL 2000 it takes more time to complete comparing when I
run it in the Query analyser.
Is there any reason behind this?
Example
I am running a stored Procedure for generating a report
every one minute using a job scheduler.
It takes 13 seconds to complete.
Whereas when I run the same SP in Query Analyser it takes
only 2 sec or 3 seconds.
Please clarify.
Thanks in advance
Anand

Job Scheduler

Hi all
When I run a stored procedure using the job scheduler in
SQL 2000 it takes more time to complete comparing when I
run it in the Query analyser.
Is there any reason behind this?
Example
I am running a stored Procedure for generating a report
every one minute using a job scheduler.
It takes 13 seconds to complete.
Whereas when I run the same SP in Query Analyser it takes
only 2 sec or 3 seconds.
Please clarify.
Thanks in advance
AnandThis could be the problem:
http://support.microsoft.com/default.aspx?scid=kb;en-us;249730&Product=sql
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Anand" <gurusanand@.yahoo.com> wrote in message
news:024101c3db49$baff3990$a001280a@.phx.gbl...
Hi all
When I run a stored procedure using the job scheduler in
SQL 2000 it takes more time to complete comparing when I
run it in the Query analyser.
Is there any reason behind this?
Example
I am running a stored Procedure for generating a report
every one minute using a job scheduler.
It takes 13 seconds to complete.
Whereas when I run the same SP in Query Analyser it takes
only 2 sec or 3 seconds.
Please clarify.
Thanks in advance
Anand|||Do you SET NOCOUNT ON at the beginning of your stored
procedure? Sometimes this can help with this type of
problem.
Matthew Bando
BandoM@.CSCTGI(remove this).com
>--Original Message--
>Hi all
>When I run a stored procedure using the job scheduler in
>SQL 2000 it takes more time to complete comparing when I
>run it in the Query analyser.
>Is there any reason behind this?
>Example
>I am running a stored Procedure for generating a report
>every one minute using a job scheduler.
>It takes 13 seconds to complete.
>Whereas when I run the same SP in Query Analyser it
takes
>only 2 sec or 3 seconds.
>Please clarify.
>Thanks in advance
>Anand
>.
>

job retry

I have a step in a job that is set to retry on failure. Last night we made a change to a store procedure before the step failed. The retry started about 20 minutes after the update of the stored procedure. Will SQL Server use the updated version stored procedure on the retry?Yes, it will. Once you update a sproc, any subsequent calls to that procedure after it has been changed will invoke the new procedure version.|||Thank you.

Job Outcome Constants (SQLDMO_JOBOUTCOME_TYPE)

How can i write a simple procedure as below, call it from a job, and because
@.m = 2 it will fail the job step and so show me the red x beside the job nam
e
in ent mgr?
create procedure marc1 as
declare @.m int
set @.m = 2
if @.m = 1
begin
print '2'
end
else
begin
print '2'
return -1
endmarcmc wrote:
> How can i write a simple procedure as below, call it from a job, and becau
se
> @.m = 2 it will fail the job step and so show me the red x beside the job n
ame
> in ent mgr?
> create procedure marc1 as
> declare @.m int
> set @.m = 2
> if @.m = 1
> begin
> print '2'
> end
> else
> begin
> print '2'
> return -1
> end
Use RAISERROR:
e.g. RAISERROR('My error message', 16, 1)

job fails when run with sqlagent.

I have a storeded procedure 'refresh_all' that denormalizes some tables for
easier reporting. I can run it fine from isqlw command window, but when I
put it into a job it consistently fails.
I'm probably over looking something really basic.
Here is the error from the single step
Executed as user: son\kevinrug. String or binary data would be truncated.
[SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE
01000] (Error 3621). The step failed.
At first I thought it was the user account, but I verified the it has
permissions in the database and on the procedure. Beside, I am system admin
and dbo so that shouldn't have been a problem.
Also, I am starting the service (sqlagent) under my network account.
Help greatly appreciated.
kevin
>> ... String or binary data would be truncated.
The error message suggests it has to do with the data in the tables
involved. Check your processes for datatype conversions, lengthy character
data inserted into tables with types of shorter length, mismatched sql
operations like joins on columns with incompatible types etc.
Anith
|||ok I'll look at that. I was wondering if this is what it meant, but since it
ran ok interactively I wasn't sure.
Also, will try running each sub-proc to see which is failing.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eoJP1GJEFHA.612@.TK2MSFTNGP15.phx.gbl...
> The error message suggests it has to do with the data in the tables
> involved. Check your processes for datatype conversions, lengthy character
> data inserted into tables with types of shorter length, mismatched sql
> operations like joins on columns with incompatible types etc.
> --
> Anith
>
|||Thanks again.
After investigating, I found I had turned ansi_warnings OFF, once I put this
in the procedure it runs great.
kevin
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eoJP1GJEFHA.612@.TK2MSFTNGP15.phx.gbl...
> The error message suggests it has to do with the data in the tables
> involved. Check your processes for datatype conversions, lengthy character
> data inserted into tables with types of shorter length, mismatched sql
> operations like joins on columns with incompatible types etc.
> --
> Anith
>

job fails when run with sqlagent.

I have a storeded procedure 'refresh_all' that denormalizes some tables for
easier reporting. I can run it fine from isqlw command window, but when I
put it into a job it consistently fails.
I'm probably over looking something really basic.
Here is the error from the single step
Executed as user: son\kevinrug. String or binary data would be truncated.
[SQLSTATE 22001] (Error 8152) The statement has been terminated. [S
QLSTATE
01000] (Error 3621). The step failed.
At first I thought it was the user account, but I verified the it has
permissions in the database and on the procedure. Beside, I am system admin
and dbo so that shouldn't have been a problem.
Also, I am starting the service (sqlagent) under my network account.
Help greatly appreciated.
kevin>> ... String or binary data would be truncated.
The error message suggests it has to do with the data in the tables
involved. Check your processes for datatype conversions, lengthy character
data inserted into tables with types of shorter length, mismatched sql
operations like joins on columns with incompatible types etc.
Anith|||ok I'll look at that. I was wondering if this is what it meant, but since it
ran ok interactively I wasn't sure.
Also, will try running each sub-proc to see which is failing.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eoJP1GJEFHA.612@.TK2MSFTNGP15.phx.gbl...
> The error message suggests it has to do with the data in the tables
> involved. Check your processes for datatype conversions, lengthy character
> data inserted into tables with types of shorter length, mismatched sql
> operations like joins on columns with incompatible types etc.
> --
> Anith
>|||Thanks again.
After investigating, I found I had turned ansi_warnings OFF, once I put this
in the procedure it runs great.
kevin
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eoJP1GJEFHA.612@.TK2MSFTNGP15.phx.gbl...
> The error message suggests it has to do with the data in the tables
> involved. Check your processes for datatype conversions, lengthy character
> data inserted into tables with types of shorter length, mismatched sql
> operations like joins on columns with incompatible types etc.
> --
> Anith
>

job fails when run with sqlagent.

I have a storeded procedure 'refresh_all' that denormalizes some tables for
easier reporting. I can run it fine from isqlw command window, but when I
put it into a job it consistently fails.
I'm probably over looking something really basic.
Here is the error from the single step
Executed as user: son\kevinrug. String or binary data would be truncated.
[SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE
01000] (Error 3621). The step failed.
At first I thought it was the user account, but I verified the it has
permissions in the database and on the procedure. Beside, I am system admin
and dbo so that shouldn't have been a problem.
Also, I am starting the service (sqlagent) under my network account.
Help greatly appreciated.
kevin>> ... String or binary data would be truncated.
The error message suggests it has to do with the data in the tables
involved. Check your processes for datatype conversions, lengthy character
data inserted into tables with types of shorter length, mismatched sql
operations like joins on columns with incompatible types etc.
--
Anith|||ok I'll look at that. I was wondering if this is what it meant, but since it
ran ok interactively I wasn't sure.
Also, will try running each sub-proc to see which is failing.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eoJP1GJEFHA.612@.TK2MSFTNGP15.phx.gbl...
> >> ... String or binary data would be truncated.
> The error message suggests it has to do with the data in the tables
> involved. Check your processes for datatype conversions, lengthy character
> data inserted into tables with types of shorter length, mismatched sql
> operations like joins on columns with incompatible types etc.
> --
> Anith
>|||Thanks again.
After investigating, I found I had turned ansi_warnings OFF, once I put this
in the procedure it runs great.
kevin
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eoJP1GJEFHA.612@.TK2MSFTNGP15.phx.gbl...
> >> ... String or binary data would be truncated.
> The error message suggests it has to do with the data in the tables
> involved. Check your processes for datatype conversions, lengthy character
> data inserted into tables with types of shorter length, mismatched sql
> operations like joins on columns with incompatible types etc.
> --
> Anith
>

Job and Stored Procedure Problems

I've written 2 stored procedures.
Proc 1, cycles through the system table and pulls out the names of all DB's.
Proc 2 is called by Proc 1, after it grabs 1 of the database names. It then
performs a type of backup based on a flag:
F = Full, D = Differential, and T = Transaction Logs.
I capture any errors and return them to Proc 1, so I can formulate an email
giving status.
At the end of Proc 1, I generate an email and ship off, giving the status of
each of the backups.
The process works just fine, if you do the TSQL statement through a query
window, reporting the status of the backup or error message. The problem I
am having, is when i schedule the job. To test it, I took one of my DB's
offline. When I run it through the Query Window, all errors and successes
report just fine.
When I run it through the Job, it fails at the offline database and kills
the entire job. Since the email isn't sent till the end of proc 1, the
email is never being generated, because upon failure of proc 2, it aborts
the entire job with an error.
It still backs up all the databases, just doesn't finish the email portion
of the process.
I've played with the job options, to continue to next step, even though one
doesn't exist, exit with success, doesn't do it, I've even played with SET
XACT_ABORT OFF
Thanks.Hi Kevin
You should be checking the status of the database in sysdatabases to exclude
offline one.
John
"Kevin Antel" wrote:

> I've written 2 stored procedures.
> Proc 1, cycles through the system table and pulls out the names of all DB'
s.
> Proc 2 is called by Proc 1, after it grabs 1 of the database names. It th
en
> performs a type of backup based on a flag:
> F = Full, D = Differential, and T = Transaction Logs.
> I capture any errors and return them to Proc 1, so I can formulate an emai
l
> giving status.
> At the end of Proc 1, I generate an email and ship off, giving the status
of
> each of the backups.
> The process works just fine, if you do the TSQL statement through a query
> window, reporting the status of the backup or error message. The problem
I
> am having, is when i schedule the job. To test it, I took one of my DB's
> offline. When I run it through the Query Window, all errors and successes
> report just fine.
> When I run it through the Job, it fails at the offline database and kills
> the entire job. Since the email isn't sent till the end of proc 1, the
> email is never being generated, because upon failure of proc 2, it aborts
> the entire job with an error.
> It still backs up all the databases, just doesn't finish the email portion
> of the process.
> I've played with the job options, to continue to next step, even though on
e
> doesn't exist, exit with success, doesn't do it, I've even played with SET
> XACT_ABORT OFF
> Thanks.
>
>

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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

JDBC: calling a stored procedure with multiple return values.

Using JDBC, is there a way to call a stored procedure with multiple
return values? Thanks.
randy.p.ho@.gmail.com wrote:

> Using JDBC, is there a way to call a stored procedure with multiple
> return values? Thanks.

Absolutely. What do you mean by 'multiple return values'? Multiple output
parameters? Multiple result sets and/or update counts? Multiple mixes of
result sets and update counts?
If you will show the procedure signature and maybe even the text? Tell
us what the body of the procedure returns.

Joe Weinstein at BEA|||Thanks for the reply. I meant "multiple output parameters".

Here is how I execute the stored procedure:

declare @.ErrorID int
declare @.ErrorStr varchar(255)
exec procName
@.customerId = '1234567890',
@.customerName = 'some name',
@.error_code = @.ErrorID,
@.error_state = @.ErrorStr

Here is the procedure:

create procedure uxt1.procName
@.customerId char(15) output,
@.customerName char(64) output,
@.error_code int output,
@.error_state varchar(255) output
... ...
/* all the business logic */
... ...
return (@.error_state)
GO

Here's what SQL server gives me if I do a "Script object as Execute":
DECLARE @.RC int
DECLARE @.customerId char(15)
DECLARE @.customerName char(64)
DECLARE @.error_code int
DECLARE @.error_state varchar(255)
EXEC @.RC = [uxt1].[procName] @.customerId, @.customerName, @.error_code
OUTPUT , @.error_state OUTPUT

The following is what I've tried in a Java program:
...
CallableStatement cs = conn.prepareCall(" {? = call
uxt1.procName(?,?,?,?)}" );
cs.registerOutParameter(1,java.sql.Types.INTEGER);
cs.setString(2,"some ID");
cs.setString(3,"some Name");
cs.registerOutParameter(4,java.sql.Types.INTEGER);
cs.registerOutParameter(5,java.sql.Types.VARCHAR);
ResultSet rs = cs.executeQuery();
...

My code doesn't throw any exception; but the procedure was not executed
correctly (i.e. it's not doing what it's supposed to do, which is to
simply insert some values into a table).
Any help is appreciated. Thanks in advance.|||
randy.p.ho@.gmail.com wrote:

> Thanks for the reply. I meant "multiple output parameters".
> Here is how I execute the stored procedure:
> declare @.ErrorID int
> declare @.ErrorStr varchar(255)
> exec procName
> @.customerId = '1234567890',
> @.customerName = 'some name',
> @.error_code = @.ErrorID,
> @.error_state = @.ErrorStr
>
> Here is the procedure:
> create procedure uxt1.procName
> @.customerId char(15) output,
> @.customerName char(64) output,
> @.error_code int output,
> @.error_state varchar(255) output
> ... ...
> /* all the business logic */
> ... ...
> return (@.error_state)
> GO
>
> Here's what SQL server gives me if I do a "Script object as Execute":
> DECLARE @.RC int
> DECLARE @.customerId char(15)
> DECLARE @.customerName char(64)
> DECLARE @.error_code int
> DECLARE @.error_state varchar(255)
> EXEC @.RC = [uxt1].[procName] @.customerId, @.customerName, @.error_code
> OUTPUT , @.error_state OUTPUT
>
> The following is what I've tried in a Java program:
> ...
> CallableStatement cs = conn.prepareCall(" {? = call
> uxt1.procName(?,?,?,?)}" );
> cs.registerOutParameter(1,java.sql.Types.INTEGER);
> cs.setString(2,"some ID");
> cs.setString(3,"some Name");
> cs.registerOutParameter(4,java.sql.Types.INTEGER);
> cs.registerOutParameter(5,java.sql.Types.VARCHAR);
> ResultSet rs = cs.executeQuery();
> ...
> My code doesn't throw any exception; but the procedure was not executed
> correctly (i.e. it's not doing what it's supposed to do, which is to
> simply insert some values into a table).
> Any help is appreciated. Thanks in advance.

Is the procedure executing at all? You shouldn't be calling executeQuery()
unless the first thing the procedure does is a select. Use execute() and
then loop:

cs.execute();
while (true)
{
int update_count = ps.getUpdateCount();
ResultSet rs = ps.getResultSet();
if ((rs == null && (update_count == -1)) break; // done

if (rs != null) process rs;
ps.getMoreResults();
}
// after processing inline results, call ps.getXXX() to get output parameters.

Whatever jdbc driver you're suing is pretty flakey if it
returns a result set from executeQuery() and the
procedure didn't do a select for data to go to the caller...

Joe Weinstein at BEA|||I have closely the same problem, excepting that my stored procs returns a "TABLE".

In fact, I have a SQL function that must return a TABLE and get 2 parameters. Its signature is as follow:

FUNCTION myFunction (@.codett varchar(40), @.codet varchar(40) )
RETURNS @.Tab TABLE([cp] [varchar] (40), [pourcentage] [int])

I'd like to call it from my DAO (using JDBC). having that 'cs' is a CallableStatement, I tried:

cs = jdbcConnection.prepareCall("{? = call myFunction(?, ?)}");
cs.registerOutParameter( 1, Types.OTHER );
cs.setString(2, codett);//codett contains a String
cs.setString(3, codet);//codet contains a String
ResultSet rset = cs.executeQuery();

However, when a get to the "executeQuery()" method, I get an SQLException saying that "myFunction" is a function Object.

have you met such a problem ?