Job fails when run as SQL Agent

Hi,

I have developed a package which selects records from SQL 2000 table from a server on network and writes it into an excel sheet.

The package runs fine from IDE. I am using "DoNotSaveSensitive" option. I have deployed this package on my local server which is SQL 2005 with SP2.

and scheduled through a job to run as SQLAgent. But I am getting the following error:

Message

Executed as user: STEL540\SYSTEM. ...on 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:22:20 p.m.

Error: 2007-08-20 15:22:22.00

Code: 0xC0202009

Source: LastMonthCallTagging Connection manager "HAMDB02-GenCallTagging"

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.

Error code: 0x80004005. An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "Communication link failure". An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".

End Error Error: 2007-08-20 15:22:22.02 Code: 0xC020... The package execution fa... The step failed.

Can someone please guide on this error?

Thanks,

How are you connecting to HAMDB02-GenCallTagging? Windows or sql server authentication?

|||

When you are running packages it is all about context, when you run it manually it runs in the context of your permissions, as a Job it runs in the context of SQL Server Agent. Try the links below for details the Agent and the owner of the package needs admin level permissions.

http://support.microsoft.com/kb/918760/

http://support.microsoft.com/kb/938086/en-US

Job fails when run as SQL Agent

Hi,

I have developed a package which selects records from SQL 2000 table from a server on network and writes it into an excel sheet.

The package runs fine from IDE. I am using "DoNotSaveSensitive" option. I have deployed this package on my local server which is SQL 2005 with SP2.

and scheduled through a job to run as SQLAgent. But I am getting the following error:

Message

Executed as user: STEL540\SYSTEM. ...on 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:22:20 p.m.

Error: 2007-08-20 15:22:22.00

Code: 0xC0202009

Source: LastMonthCallTagging Connection manager "HAMDB02-GenCallTagging"

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.

Error code: 0x80004005. An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "Communication link failure". An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80004005

Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".

End Error Error: 2007-08-20 15:22:22.02 Code: 0xC020... The package execution fa... The step failed.

Can someone please guide on this error?

Thanks,

How are you connecting to HAMDB02-GenCallTagging? Windows or sql server authentication?

|||

When you are running packages it is all about context, when you run it manually it runs in the context of your permissions, as a Job it runs in the context of SQL Server Agent. Try the links below for details the Agent and the owner of the package needs admin level permissions.

http://support.microsoft.com/kb/918760/

http://support.microsoft.com/kb/938086/en-US

Job Fails accessing mapped drive

I built a package in SSIS that exports some data in a database and writes it to a flat file. The flat file is written to a mapped drive on another server (shared folder). The package runs without failure in SSIS and when I deploy it on the SQL Server. The problem comes when I try to run the package as a job; it fails. If I change the file location to the local c: drive, the job runs to sucess. The id that starts the SQL Server Agent is a domain id that is included in both the domain admin group and the local admin group. All the job log indicates is that the job failed; no details.

What am I missing?

ThanksFully qualifying the share fixed the problem. Sure would like to understand why.

Job Fail.

Hi,
I have a job that reads from server A and writes the data
in server B. The job is scheduled to run every 5 min. Now
and again, the job fails and I get the following
message: "Incorrect function. [SQLSTATE 42000] (Error
50000). The step failed." but everything is OK for the
next run.
Is it a network problem (between the servers), a server
problem, a Windows problem? I will appreciate any help on
that.
Thankx,
Specify an output file for the jobstep and then after next execution look in that file for error
messages. Also, try View Job History, check "Show Step Details", navigate to the failed jobstep and
hunt for error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mona" <anonymous@.discussions.microsoft.com> wrote in message
news:22de301c45d9b$f4e61550$a001280a@.phx.gbl...
> Hi,
> I have a job that reads from server A and writes the data
> in server B. The job is scheduled to run every 5 min. Now
> and again, the job fails and I get the following
> message: "Incorrect function. [SQLSTATE 42000] (Error
> 50000). The step failed." but everything is OK for the
> next run.
> Is it a network problem (between the servers), a server
> problem, a Windows problem? I will appreciate any help on
> that.
> Thankx,
|||Specify an output file for the jobstep and then after next execution look in that file for error
messages. Also, try View Job History, check "Show Step Details", navigate to the failed jobstep and
hunt for error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mona" <anonymous@.discussions.microsoft.com> wrote in message
news:22de301c45d9b$f4e61550$a001280a@.phx.gbl...
> Hi,
> I have a job that reads from server A and writes the data
> in server B. The job is scheduled to run every 5 min. Now
> and again, the job fails and I get the following
> message: "Incorrect function. [SQLSTATE 42000] (Error
> 50000). The step failed." but everything is OK for the
> next run.
> Is it a network problem (between the servers), a server
> problem, a Windows problem? I will appreciate any help on
> that.
> Thankx,
|||I specified an output file and waiting for the next
failure. The error message in my first mail is from
the "view Job History".
How come there is no error number 50000 in the
master.dbo.sysmessages?

>--Original Message--
>Specify an output file for the jobstep and then after
next execution look in that file for error
>messages. Also, try View Job History, check "Show Step
Details", navigate to the failed jobstep and
>hunt for error messages.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"mona" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:22de301c45d9b$f4e61550$a001280a@.phx.gbl...
data[vbcol=seagreen]
Now[vbcol=seagreen]
on
>
>.
>
|||I specified an output file and waiting for the next
failure. The error message in my first mail is from
the "view Job History".
How come there is no error number 50000 in the
master.dbo.sysmessages?

>--Original Message--
>Specify an output file for the jobstep and then after
next execution look in that file for error
>messages. Also, try View Job History, check "Show Step
Details", navigate to the failed jobstep and
>hunt for error messages.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"mona" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:22de301c45d9b$f4e61550$a001280a@.phx.gbl...
data[vbcol=seagreen]
Now[vbcol=seagreen]
on
>
>.
>
|||Ahh, I missed that error number. Error 50000 is what you get if you execute RAISERROR and pass a
string. So you have to look at your code and see what part executes the RAISERROR command. Remember
that is can be hidden in a trigger or stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mona" <anonymous@.discussions.microsoft.com> wrote in message
news:22bd301c45db6$b1977260$a401280a@.phx.gbl...[vbcol=seagreen]
> I specified an output file and waiting for the next
> failure. The error message in my first mail is from
> the "view Job History".
> How come there is no error number 50000 in the
> master.dbo.sysmessages?
> next execution look in that file for error
> Details", navigate to the failed jobstep and
> message
> data
> Now
> on
|||Ahh, I missed that error number. Error 50000 is what you get if you execute RAISERROR and pass a
string. So you have to look at your code and see what part executes the RAISERROR command. Remember
that is can be hidden in a trigger or stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mona" <anonymous@.discussions.microsoft.com> wrote in message
news:22bd301c45db6$b1977260$a401280a@.phx.gbl...[vbcol=seagreen]
> I specified an output file and waiting for the next
> failure. The error message in my first mail is from
> the "view Job History".
> How come there is no error number 50000 in the
> master.dbo.sysmessages?
> next execution look in that file for error
> Details", navigate to the failed jobstep and
> message
> data
> Now
> on

Job Fail.

Hi,
I have a job that reads from server A and writes the data
in server B. The job is scheduled to run every 5 min. Now
and again, the job fails and I get the following
message: "Incorrect function. [SQLSTATE 42000] (Error
50000). The step failed." but everything is OK for the
next run.
Is it a network problem (between the servers), a server
problem, a Windows problem? I will appreciate any help on
that.
Thankx,Specify an output file for the jobstep and then after next execution look in that file for error
messages. Also, try View Job History, check "Show Step Details", navigate to the failed jobstep and
hunt for error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mona" <anonymous@.discussions.microsoft.com> wrote in message
news:22de301c45d9b$f4e61550$a001280a@.phx.gbl...
> Hi,
> I have a job that reads from server A and writes the data
> in server B. The job is scheduled to run every 5 min. Now
> and again, the job fails and I get the following
> message: "Incorrect function. [SQLSTATE 42000] (Error
> 50000). The step failed." but everything is OK for the
> next run.
> Is it a network problem (between the servers), a server
> problem, a Windows problem? I will appreciate any help on
> that.
> Thankx,|||I specified an output file and waiting for the next
failure. The error message in my first mail is from
the "view Job History".
How come there is no error number 50000 in the
master.dbo.sysmessages?
>--Original Message--
>Specify an output file for the jobstep and then after
next execution look in that file for error
>messages. Also, try View Job History, check "Show Step
Details", navigate to the failed jobstep and
>hunt for error messages.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"mona" <anonymous@.discussions.microsoft.com> wrote in
message
>news:22de301c45d9b$f4e61550$a001280a@.phx.gbl...
>> Hi,
>> I have a job that reads from server A and writes the
data
>> in server B. The job is scheduled to run every 5 min.
Now
>> and again, the job fails and I get the following
>> message: "Incorrect function. [SQLSTATE 42000] (Error
>> 50000). The step failed." but everything is OK for the
>> next run.
>> Is it a network problem (between the servers), a server
>> problem, a Windows problem? I will appreciate any help
on
>> that.
>> Thankx,
>
>.
>|||Ahh, I missed that error number. Error 50000 is what you get if you execute RAISERROR and pass a
string. So you have to look at your code and see what part executes the RAISERROR command. Remember
that is can be hidden in a trigger or stored procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mona" <anonymous@.discussions.microsoft.com> wrote in message
news:22bd301c45db6$b1977260$a401280a@.phx.gbl...
> I specified an output file and waiting for the next
> failure. The error message in my first mail is from
> the "view Job History".
> How come there is no error number 50000 in the
> master.dbo.sysmessages?
> >--Original Message--
> >Specify an output file for the jobstep and then after
> next execution look in that file for error
> >messages. Also, try View Job History, check "Show Step
> Details", navigate to the failed jobstep and
> >hunt for error messages.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"mona" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:22de301c45d9b$f4e61550$a001280a@.phx.gbl...
> >> Hi,
> >>
> >> I have a job that reads from server A and writes the
> data
> >> in server B. The job is scheduled to run every 5 min.
> Now
> >> and again, the job fails and I get the following
> >> message: "Incorrect function. [SQLSTATE 42000] (Error
> >> 50000). The step failed." but everything is OK for the
> >> next run.
> >> Is it a network problem (between the servers), a server
> >> problem, a Windows problem? I will appreciate any help
> on
> >> that.
> >> Thankx,
> >
> >
> >.
> >

Job Fail.

Hi,
I have a job that reads from server A and writes the data
in server B. The job is scheduled to run every 5 min. Now
and again, the job fails and I get the following
message: "Incorrect function. [SQLSTATE 42000] (Error
50000). The step failed." but everything is OK for the
next run.
Is it a network problem (between the servers), a server
problem, a Windows problem? I will appreciate any help on
that.
Thankx,Specify an output file for the jobstep and then after next execution look in
that file for error
messages. Also, try View Job History, check "Show Step Details", navigate to
the failed jobstep and
hunt for error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mona" <anonymous@.discussions.microsoft.com> wrote in message
news:22de301c45d9b$f4e61550$a001280a@.phx
.gbl...
> Hi,
> I have a job that reads from server A and writes the data
> in server B. The job is scheduled to run every 5 min. Now
> and again, the job fails and I get the following
> message: "Incorrect function. [SQLSTATE 42000] (Error
> 50000). The step failed." but everything is OK for the
> next run.
> Is it a network problem (between the servers), a server
> problem, a Windows problem? I will appreciate any help on
> that.
> Thankx,|||I specified an output file and waiting for the next
failure. The error message in my first mail is from
the "view Job History".
How come there is no error number 50000 in the
master.dbo.sysmessages?

>--Original Message--
>Specify an output file for the jobstep and then after
next execution look in that file for error
>messages. Also, try View Job History, check "Show Step
Details", navigate to the failed jobstep and
>hunt for error messages.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"mona" <anonymous@.discussions.microsoft.com> wrote in
message
> news:22de301c45d9b$f4e61550$a001280a@.phx
.gbl...
data[vbcol=seagreen]
Now[vbcol=seagreen]
on[vbcol=seagreen]
>
>.
>|||Ahh, I missed that error number. Error 50000 is what you get if you execute
RAISERROR and pass a
string. So you have to look at your code and see what part executes the RAIS
ERROR command. Remember
that is can be hidden in a trigger or stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mona" <anonymous@.discussions.microsoft.com> wrote in message
news:22bd301c45db6$b1977260$a401280a@.phx
.gbl...[vbcol=seagreen]
> I specified an output file and waiting for the next
> failure. The error message in my first mail is from
> the "view Job History".
> How come there is no error number 50000 in the
> master.dbo.sysmessages?
>
> next execution look in that file for error
> Details", navigate to the failed jobstep and
> message
> data
> Now
> on

JDBC ResultSet writes to tmp file

The problem I got is when I am using the ResultSet to loop through the
result rows, the JDBC driver keeps writing each row's data to a tmp
file at /Tomcat/temp/ After the loop is finished, I do a
"ResultSet.close();". After that, the content in that tmp file is
gone.
Because the content written to the tmp file is really big during the
looping, it significantly slows done the performance. Following is my
code.
[code]
int size = 0;
int i = 0;
ObjectOutputStream outputToApplet = new
ObjectOutputStream(response.
getOutputStream());
ResultSet resultSet = statement.executeQuery(q);
if (page == 0) { //send back the number of rows.
resultSet.last();
size = resultSet.getRow();
resultSet.beforeFirst();
outputToApplet.writeObject(new Integer(size));
outputToApplet.flush();
}
else if (page > 0) { //send back 1000 as the number of rows,
and start from page*1000
outputToApplet.writeObject(new Integer(1000));
outputToApplet.flush();
resultSet.absolute(page * 1000);
}
while (resultSet.next()) {
String MailID = resultSet.getString("mail_Id");
String Name = resultSet.getString("mailFrom");
String To = resultSet.getString("mailToSorted");
String Subject = resultSet.getString("Subject");
String InsertDate = resultSet.getString("insertDate");
CustomerServiceReturn C = new CustomerServiceReturn(Name,
To, MailID, Subject, InsertDate);
outputToApplet.writeObject(C);
outputToApplet.flush();
i++;
if (i == 1000) {
outputToApplet.close();
resultSet.close();
return;
}
}
resultSet.close();
outputToApplet.close();
[/code]
The name of the file is "ddtbxxxxx.tmp". xxxxx is a number.
Thank you so much for any help.
Hong
Hong wrote:
> The problem I got is when I am using the ResultSet to loop through the
> result rows, the JDBC driver keeps writing each row's data to a tmp
> file at /Tomcat/temp/ After the loop is finished, I do a
> "ResultSet.close();". After that, the content in that tmp file is
> gone.
> Because the content written to the tmp file is really big during the
> looping, it significantly slows done the performance.
Did you actually verify this with some measurements that the tmp file is
the reason for slow performance?

> Following is my
> code.
> [code]
> int size = 0;
> int i = 0;
> ObjectOutputStream outputToApplet = new
> ObjectOutputStream(response.
> getOutputStream());
> ResultSet resultSet = statement.executeQuery(q);
> if (page == 0) { //send back the number of rows.
> resultSet.last();
> size = resultSet.getRow();
> resultSet.beforeFirst();
> outputToApplet.writeObject(new Integer(size));
> outputToApplet.flush();
> }
> else if (page > 0) { //send back 1000 as the number of
> rows, and start from page*1000
> outputToApplet.writeObject(new Integer(1000));
> outputToApplet.flush();
> resultSet.absolute(page * 1000);
> }
> while (resultSet.next()) {
> String MailID = resultSet.getString("mail_Id");
> String Name = resultSet.getString("mailFrom");
> String To = resultSet.getString("mailToSorted");
> String Subject = resultSet.getString("Subject");
> String InsertDate = resultSet.getString("insertDate");
> CustomerServiceReturn C = new
> CustomerServiceReturn(Name, To, MailID, Subject, InsertDate);
> outputToApplet.writeObject(C);
> outputToApplet.flush();
> i++;
> if (i == 1000) {
> outputToApplet.close();
> resultSet.close();
> return;
> }
> }
> resultSet.close();
> outputToApplet.close();
> [/code]
> The name of the file is "ddtbxxxxx.tmp". xxxxx is a number.
> Thank you so much for any help.
I'd remove the invocations of "outputToApplet.flush();". Closing does a
flush automatically and with the flushes in between you underutilize the
streams buffers which might be *one* reasons for bad performance.
It's also a good idea to put close() calls into finally blocks. That way
you make sure that even in case of an exception resources are properly
deallocated.
Kind regards
robert