Job Failure

Dear All

A quick question:

I run a set of scheduled jobs. The jobs run Stored Procedures.
However, if the sproc fails, the job quits and moves on to the next
one. However, the sproc should carry on. E.g, IF find bad records,
EXPORT to file, GO TO NEXT RECORD (BUT DON'T QUIT THE SPROC). The job
scheduler does not allow this, therefore, the sproc does not get a
chance to finish.

Is there a way to ensure a sproc can finish before moving on to the
next step?

Thanks

Simonaaronss@.the-mdu.com (Simon) wrote in message news:<f526ea06.0402050300.499b8b81@.posting.google.com>...
> Dear All
> A quick question:
> I run a set of scheduled jobs. The jobs run Stored Procedures.
> However, if the sproc fails, the job quits and moves on to the next
> one. However, the sproc should carry on. E.g, IF find bad records,
> EXPORT to file, GO TO NEXT RECORD (BUT DON'T QUIT THE SPROC). The job
> scheduler does not allow this, therefore, the sproc does not get a
> chance to finish.
> Is there a way to ensure a sproc can finish before moving on to the
> next step?
> Thanks
> Simon

I'm not sure I understand you exactly - I guess the issue is not
really the scheduling, but rather how to handle an error in your
stored procedure? If the procedure exists because of an error, then
control goes back to the scheduled job. You could set a number of
retry attempts for that job step, but it would probably be better to
handle or prevent the error in your procedure. There are some useful
articles here:

http://www.sommarskog.se/index.html

If this isn't helpful, perhaps you could post (some of) your procedure
code to show where it fails, along with the error message, and someone
may be able to suggest how to handle the error condition.

Simon

Job Failure

I have a job that runs stored procedures. I ran the stored
procedures in the correct order myself and they ran fine.
But when I try to run them in a job the job fails
immediately. I tried changing the user for the job so
that it matches the creator of the database and stored
procedures, and the job still failed. So then I wrote the
output of the job to a file, and this is the error that is
happening.
Msg 7399, Sev 16: OLE DB provider 'SQLOLEDB' reported an
error. [SQLSTATE 42000]
Msg 7312, Sev 16: [SQLSTATE 01000]
Does anyone have any ideas?
Thank you for your assistance,
AdamIs the job owner sysadmin? Are you accessing linked server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <anonymous@.discussions.microsoft.com> wrote in message news:fed501c411be$81b90020$a6
01280a@.phx.gbl...
> I have a job that runs stored procedures. I ran the stored
> procedures in the correct order myself and they ran fine.
> But when I try to run them in a job the job fails
> immediately. I tried changing the user for the job so
> that it matches the creator of the database and stored
> procedures, and the job still failed. So then I wrote the
> output of the job to a file, and this is the error that is
> happening.
> Msg 7399, Sev 16: OLE DB provider 'SQLOLEDB' reported an
> error. [SQLSTATE 42000]
> Msg 7312, Sev 16: [SQLSTATE 01000]
> Does anyone have any ideas?
> Thank you for your assistance,
> Adam|||Yes, I am accessing several linked servers. I have tried
running the job with the owner as sysadmin and as another
user. Both fail.
>--Original Message--
>Is the job owner sysadmin? Are you accessing linked
server?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Adam" <anonymous@.discussions.microsoft.com> wrote in
message news:fed501c411be$81b90020$a601280a@.phx.gbl...
stored
fine.
the
is
>
>.
>|||Do you get the same error regardless of which login owns the job?
You will get problems is the job owner isn't sysadmin, as Agent then tries t
o emulate the job owner's login's
user name in the database using the SETUSER command. And after executing SET
USER, you are not allowed to do
operations at the server level. accessing linked server is a server (long) l
evel operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:12d7401c411df$be50c4c0$a401280a
@.phx.gbl...
> Yes, I am accessing several linked servers. I have tried
> running the job with the owner as sysadmin and as another
> user. Both fail.
> server?
> message news:fed501c411be$81b90020$a601280a@.phx.gbl...
> stored
> fine.
> the
> is|||Yes, I have tried running the job as the database's owner
and as the 'sa' system account. Both fail, yet both are
set up to use a valid account on the linked servers. I
have this same setup on another server linked to the same
servers, and it works just fine.
>--Original Message--
>Do you get the same error regardless of which login owns
the job?
>You will get problems is the job owner isn't sysadmin, as
Agent then tries to emulate the job owner's login's
>user name in the database using the SETUSER command. And
after executing SETUSER, you are not allowed to do
>operations at the server level. accessing linked server
is a server (long) level operation.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
><anonymous@.discussions.microsoft.com> wrote in message
news:12d7401c411df$be50c4c0$a401280a@.phx
.gbl...
tried
another
so
stored
wrote
that
reported an
>
>.
>|||I see... There goes my theory. I'm afraid I'm out of ideas, then. If you hav
e searched KB and are on current
service pack, and you don't get other suggestions here, I suppose its MS PSS
time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Adam" <anonymous@.discussions.microsoft.com> wrote in message news:1339b01c411ec$094cd200$a
101280a@.phx.gbl...
> Yes, I have tried running the job as the database's owner
> and as the 'sa' system account. Both fail, yet both are
> set up to use a valid account on the linked servers. I
> have this same setup on another server linked to the same
> servers, and it works just fine.
> the job?
> Agent then tries to emulate the job owner's login's
> after executing SETUSER, you are not allowed to do
> is a server (long) level operation.
> news:12d7401c411df$be50c4c0$a401280a@.phx
.gbl...
> tried
> another
> so
> stored
> wrote
> that
> reported an

Job failure

I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
MarcIs the recovery model of the destination database changed recently? Are the
backup jobs running successfully?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
I have a job that runs every evening that calls 2 stored
procedures. The first sp inserts new records from a
source database.table into a destination database.table
the second job updates the records that were changed at
the source and updates them at the destination.
This job has run successfully without problems for 2+
years. Now for the past two days the second step is
blowing up the transaction log to more than 7GB (it's
limit). Any idea what could cause this new behaviour?
Any suggestions as to what I need to look at would be
most appreciated.
Thanks,
Marc|||Could be that the recovery model was changed from simple
to full, but I'm not 100% as this isn't my server.
The backups that are scheduled after this job runs fail
as there is no longer adequate disk space.
>--Original Message--
>Is the recovery model of the destination database
changed recently? Are the
>backup jobs running successfully?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
>I have a job that runs every evening that calls 2 stored
>procedures. The first sp inserts new records from a
>source database.table into a destination database.table
>the second job updates the records that were changed at
>the source and updates them at the destination.
>This job has run successfully without problems for 2+
>years. Now for the past two days the second step is
>blowing up the transaction log to more than 7GB (it's
>limit). Any idea what could cause this new behaviour?
>Any suggestions as to what I need to look at would be
>most appreciated.
>Thanks,
>Marc
>
>.
>|||Hi,
Execute the below command to get the recovery model.
select databasepropertyex('dbname','recovery')
If the recovery model is FULL or BULK_LOGGED, you need to perform a
trasnaction log backup inbetween your back job.
Currently to reduce the trasnaction log file size you have backup the
transaction log shrink the file
Steps to shrink the Log file.
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager) or (if you do need
the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Thanks
Hari
MCDBA
"Marc" <anonymous@.discussions.microsoft.com> wrote in message
news:2415501c45f52$18baf880$a301280a@.phx.gbl...
> Could be that the recovery model was changed from simple
> to full, but I'm not 100% as this isn't my server.
> The backups that are scheduled after this job runs fail
> as there is no longer adequate disk space.
> >--Original Message--
> >Is the recovery model of the destination database
> changed recently? Are the
> >backup jobs running successfully?
> >--
> >HTH,
> >Vyas, MVP (SQL Server)
> >http://vyaskn.tripod.com/
> >Is .NET important for a database professional?
> >http://vyaskn.tripod.com/poll.htm
> >
> >
> >"Marc" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
> >I have a job that runs every evening that calls 2 stored
> >procedures. The first sp inserts new records from a
> >source database.table into a destination database.table
> >the second job updates the records that were changed at
> >the source and updates them at the destination.
> >
> >This job has run successfully without problems for 2+
> >years. Now for the past two days the second step is
> >blowing up the transaction log to more than 7GB (it's
> >limit). Any idea what could cause this new behaviour?
> >
> >Any suggestions as to what I need to look at would be
> >most appreciated.
> >
> >Thanks,
> >Marc
> >
> >
> >.
> >|||Thanks for the info Hari, but I've done all that.
There are transaction log backups every two hours.
What I'm trying to resolve is the offending job that
unexplainably filling up the log to capacity every
evening and then subsequently failing out.
Thanks,
Marc
>--Original Message--
>Hi,
>Execute the below command to get the recovery model.
>select databasepropertyex('dbname','recovery')
>If the recovery model is FULL or BULK_LOGGED, you need
to perform a
>trasnaction log backup inbetween your back job.
>Currently to reduce the trasnaction log file size you
have backup the
>transaction log shrink the file
>Steps to shrink the Log file.
>1. Backup the transaction log (Use command:- BACKUP Log
dbname to
>disk='c:\backup\dbname.tr1' (or use enterprise
manager) or (if you do need
>the trasaction log backup execute below command)
> backup log <dbname> with truncate_only
>2. Indetify the log file for shrinking:-
> use <dbname>
> go
> sp_helpfile
>Based on the name column for the transaction log file
execute the dbcc
>shrinkfile
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>
>Have a look into the below article,
>INF: Shrinking the Transaction Log in SQL Server 2000
with DBCC SHRINKFILE
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
>
>--
>Thanks
>Hari
>MCDBA
>"Marc" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2415501c45f52$18baf880$a301280a@.phx.gbl...
>> Could be that the recovery model was changed from
simple
>> to full, but I'm not 100% as this isn't my server.
>> The backups that are scheduled after this job runs fail
>> as there is no longer adequate disk space.
>> >--Original Message--
>> >Is the recovery model of the destination database
>> changed recently? Are the
>> >backup jobs running successfully?
>> >--
>> >HTH,
>> >Vyas, MVP (SQL Server)
>> >http://vyaskn.tripod.com/
>> >Is .NET important for a database professional?
>> >http://vyaskn.tripod.com/poll.htm
>> >
>> >
>> >"Marc" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:244ad01c45f4f$3aef6880$a501280a@.phx.gbl...
>> >I have a job that runs every evening that calls 2
stored
>> >procedures. The first sp inserts new records from a
>> >source database.table into a destination
database.table
>> >the second job updates the records that were changed
at
>> >the source and updates them at the destination.
>> >
>> >This job has run successfully without problems for 2+
>> >years. Now for the past two days the second step is
>> >blowing up the transaction log to more than 7GB (it's
>> >limit). Any idea what could cause this new behaviour?
>> >
>> >Any suggestions as to what I need to look at would be
>> >most appreciated.
>> >
>> >Thanks,
>> >Marc
>> >
>> >
>> >.
>> >
>
>.
>

Job Executes Only First Step

I have three stored procedures that need to run nightly in SQL 7. The three
procedures are not related; but to keep the procedures from running at the
same time, I placed them as three steps of a single job. The first two steps
are set to "Goto next step" on success; the last step is set to "Quit with
success."

The job runs every night. However, only the first step/procedure is
executed. Also, the first step has a green flag next to its ID in the Steps
tab of the job properties

I suppose I could just call all three SPs from a single stored procedure
that is run nightly. But I thought that putting them as three steps in a
single job would cause all three to run. What am I doing wrong?

Thanks!

NeilAre you sure only the first step is executing? If you right click on the
job and choose show history, then check the checkbox to show the steps do
you see all three or only one?

--
Andrew J. Kelly SQL MVP

"Neil" <nospam@.nospam.net> wrote in message
news:KL5If.1367$UN.1000@.newsread2.news.pas.earthli nk.net...
>I have three stored procedures that need to run nightly in SQL 7. The three
>procedures are not related; but to keep the procedures from running at the
>same time, I placed them as three steps of a single job. The first two
>steps are set to "Goto next step" on success; the last step is set to "Quit
>with success."
> The job runs every night. However, only the first step/procedure is
> executed. Also, the first step has a green flag next to its ID in the
> Steps tab of the job properties
> I suppose I could just call all three SPs from a single stored procedure
> that is run nightly. But I thought that putting them as three steps in a
> single job would cause all three to run. What am I doing wrong?
> Thanks!
> Neil|||Yes, you are right. All three steps are executing. There was a problem with
the data which was giving the impression that the job wasn't executing.
Thanks!

Neil

"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OsJ7bwNMGHA.740@.TK2MSFTNGP12.phx.gbl...
> Are you sure only the first step is executing? If you right click on the
> job and choose show history, then check the checkbox to show the steps do
> you see all three or only one?
> --
> Andrew J. Kelly SQL MVP
>
> "Neil" <nospam@.nospam.net> wrote in message
> news:KL5If.1367$UN.1000@.newsread2.news.pas.earthli nk.net...
>>I have three stored procedures that need to run nightly in SQL 7. The
>>three procedures are not related; but to keep the procedures from running
>>at the same time, I placed them as three steps of a single job. The first
>>two steps are set to "Goto next step" on success; the last step is set to
>>"Quit with success."
>>
>> The job runs every night. However, only the first step/procedure is
>> executed. Also, the first step has a green flag next to its ID in the
>> Steps tab of the job properties
>>
>> I suppose I could just call all three SPs from a single stored procedure
>> that is run nightly. But I thought that putting them as three steps in a
>> single job would cause all three to run. What am I doing wrong?
>>
>> Thanks!
>>
>> Neil
>>

Job Executes Only First Step

I have three stored procedures that need to run nightly in SQL 7. The three
procedures are not related; but to keep the procedures from running at the
same time, I placed them as three steps of a single job. The first two steps
are set to "Goto next step" on success; the last step is set to "Quit with
success."
The job runs every night. However, only the first step/procedure is
executed. Also, the first step has a green flag next to its ID in the Steps
tab of the job properties
I suppose I could just call all three SPs from a single stored procedure
that is run nightly. But I thought that putting them as three steps in a
single job would cause all three to run. What am I doing wrong?
Thanks!
NeilAre you sure only the first step is executing? If you right click on the
job and choose show history, then check the checkbox to show the steps do
you see all three or only one?
Andrew J. Kelly SQL MVP
"Neil" <nospam@.nospam.net> wrote in message
news:KL5If.1367$UN.1000@.newsread2.news.pas.earthlink.net...
>I have three stored procedures that need to run nightly in SQL 7. The three
>procedures are not related; but to keep the procedures from running at the
>same time, I placed them as three steps of a single job. The first two
>steps are set to "Goto next step" on success; the last step is set to "Quit
>with success."
> The job runs every night. However, only the first step/procedure is
> executed. Also, the first step has a green flag next to its ID in the
> Steps tab of the job properties
> I suppose I could just call all three SPs from a single stored procedure
> that is run nightly. But I thought that putting them as three steps in a
> single job would cause all three to run. What am I doing wrong?
> Thanks!
> Neil
>|||I believe that I saw this same question posted not too long ago.
The SP has to return an integer return status in order for DTS to know if it
completed successfully.
In other words, put
RETURN 0
at the bottom of your procedure.
--
"Neil" wrote:

> I have three stored procedures that need to run nightly in SQL 7. The thre
e
> procedures are not related; but to keep the procedures from running at the
> same time, I placed them as three steps of a single job. The first two ste
ps
> are set to "Goto next step" on success; the last step is set to "Quit with
> success."
> The job runs every night. However, only the first step/procedure is
> executed. Also, the first step has a green flag next to its ID in the Step
s
> tab of the job properties
> I suppose I could just call all three SPs from a single stored procedure
> that is run nightly. But I thought that putting them as three steps in a
> single job would cause all three to run. What am I doing wrong?
> Thanks!
> Neil
>
>|||Yes, you are right. All three steps are executing. There was a problem with
the data which was giving the impression that the job wasn't executing.
Thanks!
Neil
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OsJ7bwNMGHA.740@.TK2MSFTNGP12.phx.gbl...
> Are you sure only the first step is executing? If you right click on the
> job and choose show history, then check the checkbox to show the steps do
> you see all three or only one?
> --
> Andrew J. Kelly SQL MVP
>
> "Neil" <nospam@.nospam.net> wrote in message
> news:KL5If.1367$UN.1000@.newsread2.news.pas.earthlink.net...
>

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

JDBC: Retrieving stored procedures column information returns "-9" data type for nvarc

Hello,

I'm using the latest JDBC driver for SQL server 2005 (1.0.809.102).

Retrieving stored procedures column information returns "-9" data type for nvarchar:

public class driver {
public static void main(String[] args) throws java.lang.Throwable {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();

java.util.Properties properties = new java.util.Properties ();
properties.put("username", "sa");
properties.put("password", "sa");
properties.put("database", "GHTDB");

Connection con = DriverManager.getConnection("jdbc:sqlserver://POWERGH", properties);

ResultSet rs = con.getMetaData().getProcedureColumns("GHTDB", null, "SalesByCategory", null);

while (rs.next()) {
int i = rs.getInt("DATA_TYPE");
String s = rs.getString("TYPE_NAME");

System.out.println(i); // prints -9 for nvarchar
System.out.println(s);
}

}
}

Is this behavior documented anywhere and is by design?

Any help will be appreciated,
Regards,
Kosta

Kosta:

java.sql.Types has no value for any of the unicode text types so we return the SQL Server value for nvarchar (-9) rather than mis-reporting the type as a non-unicode varchar (12).

Both options here are bad, although, returning 12 in this case has the advantage of being wrong in the company of most other JDBC drivers, including our SQL Server 2000 JDBC driver.

If you think we should fix this, please file a bug at the product feedback center (http://lab.msdn.microsoft.com/productfeedback/) and we'll revisit the issue and make a change or doc it.

-shelby

Shelby Goerlitz

SQL Server Data Programmability -- JDBC