job failed on resource limit

Hi,
I have a job that pulls data from a linked server into
this server's database. I am working on sql server 2000.
It generally runs fine, but twice it failed on this error
message: 'OLE DB provider 'SQLOLEDB' reported an error.
Execution terminated by the provider because a resource
limit was reached. [SQLSTATE 42000] (Error 7399)
[SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB
Provider 'SQLOLEDB' ICommandText::Execute returned
0x80040e31: Execution terminated by the provider because
a resource limit was reached.]. [SQLSTATE 01000] (Error
7300). The step failed.'
When it says resource limit was reached, was it
complaining the number of connections at the linked server
has reached the limit?
Many thanks.
JJIt generally means a timeout has been exceeded (that's what 0x80040e31
means). You can increase the timeout to try and prevent this issue.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"JJ Wang" <jwang@.leapwireless.com> wrote in message
news:09b401c371e7$6f7330b0$a101280a@.phx.gbl...
Hi,
I have a job that pulls data from a linked server into
this server's database. I am working on sql server 2000.
It generally runs fine, but twice it failed on this error
message: 'OLE DB provider 'SQLOLEDB' reported an error.
Execution terminated by the provider because a resource
limit was reached. [SQLSTATE 42000] (Error 7399)
[SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB
Provider 'SQLOLEDB' ICommandText::Execute returned
0x80040e31: Execution terminated by the provider because
a resource limit was reached.]. [SQLSTATE 01000] (Error
7300). The step failed.'
When it says resource limit was reached, was it
complaining the number of connections at the linked server
has reached the limit?
Many thanks.
JJ|||thanks, Jasper. Where should I go to adjust the time-out?
JJ
>--Original Message--
>It generally means a timeout has been exceeded (that's
what 0x80040e31
>means). You can increase the timeout to try and prevent
this issue.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"JJ Wang" <jwang@.leapwireless.com> wrote in message
>news:09b401c371e7$6f7330b0$a101280a@.phx.gbl...
>Hi,
>I have a job that pulls data from a linked server into
>this server's database. I am working on sql server 2000.
>It generally runs fine, but twice it failed on this error
>message: 'OLE DB provider 'SQLOLEDB' reported an error.
>Execution terminated by the provider because a resource
>limit was reached. [SQLSTATE 42000] (Error 7399)
>[SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB
>Provider 'SQLOLEDB' ICommandText::Execute returned
>0x80040e31: Execution terminated by the provider because
>a resource limit was reached.]. [SQLSTATE 01000] (Error
>7300). The step failed.'
>When it says resource limit was reached, was it
>complaining the number of connections at the linked server
>has reached the limit?
>Many thanks.
>JJ
>
>.
>

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