I created a DTS package & CAN run it (successfully) from Enterprise Manager
and from a command prompt.

When I try to schedule a job for this DTS package under SQL Server Agent
Jobs it gets the following error:

ATTRIBUTE_TYPES to [ThankYouAcknowledgements].[dbo].[ATTRIBUTE_TYPES] Step
DTSRun OnError: Copy Data from ATTRIBUTE_TYPES to
[ThankYouAcknowledgements].[dbo].[ATTRIBUTE_TYPES] Step, Error =
-2147467259 (80004005) Error string: [Sybase][ODBC Driver]Unable to
connect to database server: specified database not found Error source:
Microsoft OLE DB Provider for ODBC Drivers Help file: Help
context: 0 Error Detail Records: Error: -2147467259 (80004005);
Provider Error: 83 (53) Error string: [Sybase][ODBC Driver]Unable to
conn. The step failed.

Why does this package get an error ONLY when it's a scheduled job?

Thanks in advance.
--
Tyler

"Tyler" <tylerf(REMOVETHISPART)@bocafed.org> wrote


Hi,

This is a problem of security context

- If the package is run from a command prompt by using DTSRun.exe or from
DTS Designer, the credentials of the currently logged-in Windows NT account
is used
- If the package is run as a SQL Server Agent job, then the integrated
security connection is made using the account you used to start SQL Agent

Notes :
if the SQL Server was started using the Local System account, the DTS
package has no permissions outside of the computer that is running SQL
Server.

SQL Server Agent account must be a sysadmin group member.

So, your schedule package fails because the account you use to start SQL
Server Agent has NOT enought rights to make a connection to your database
server from your ODBC DSN

Hope it's help you
------
Philip

Also, if you are using ODBC, create System DSN, and delete User DSN.

"Philippe" <pbonal (AT) club-internet (DOT) fr> wrote

How do I determine which account was used to start SQL Agent?

My userid is a local administrator.
I have:
Stopped & restarted the SQL Service Manager
Rebooted the SQL Server 2000
Upgraded to SP4.

The problem remains.

Thanks for your help.

--
Tyler


"Philippe" wrote:

To determine wich account was used by SQL Agent :
- On Entreprise Manager, Management, Deploy SQL Agent node
- Right Click on SQL Agent and choose properties

You can change the account SQL Agent will be used

SP4 fixes a bug on scheduled job (http://support.microsoft.com/kb/825042/)
Now, a non-syadmin user could run schedule DTS

So it's resolved your issue

------
Philip


"Tyler" <tylerf(REMOVETHISPART)@bocafed.org> wrote

You can have a look at the properties of the Agent

Right Click on the SQL Server Agent icon in EM and choose properties. The
page here will tell you the account.

Have a look here

http://support.microsoft.com/?kbid=269074

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Tyler" <tylerf(REMOVETHISPART)@bocafed.org> wrote

Tyler,

I just resolved a similar issue, I hope this may help you:

I have a DTS package that connects via ODBC and it runs fine as long as I am
logged into the server. I could not get it to run under SQLServerAgent.
So I created a batch file and ran it under DTSRun command line. I scheduled
the batch file in Windows Scheduler. Still it errored off if I logged off
the machine. Turned out to be a DSN config file used by the System DSN.
Apparently it only looked for the file if I was logged out. I talked with
my network admin and got a readme file about the ODBC driver which allowed me
to configure the file properly. Now I can schedule my DTS packages in
Windows Scheduler and log off and they run. In other words, take a look at
your configuration of your ODBC DSN, it may have something to do with it.

"Tyler" wrote:

--
Tyler


"Philippe" wrote:

security connection is made using the account you used to start SQL Agent"

I am trying to run my DTS packages as a scheduled job. The account for the
SQL Server Agent job is FED\SERVICE which is a domain account & a member of
the SYSADMIN group. I've also given FED\SERVICE it every authority that I
can find.

The problem remains.

I've gone through this document. Below are my results section by section.

"Where is the DTS package running?"
I use Remote Desktop to access the server where the DTS package was
developed and scheduled. So the package should always have been run on the
server.

"Who Owns the Job That Runs the DTS Package"
The SQL Agent Service is owned by the Windows account FED\SERVICE which is a
member of the Sysadmin group. I've tried changing the owner to
FED\ADMINSTRATOR. Same error.

"How is the DTS Package Launched?"
It is being launced as a scheduled job from Enterprise Manager. It fails
only when it is run as a scheduled job (whether by time or manually).

"Mapped drives"
Not using mapped drives. Using ODBC.

"Relative Path"
Not using relative paths. Also the DTS package was developed on the server
that is is scheduled to run on.

"COM Components in ActiveX Scripts"
My DTS package doesn't use these.

"Package Security"
My DTS package doesn't use security.
--
Tyler


"Allan Mitchell" wrote:

My DTS packages do use ODBC to connect to an SQLAnywhere 5.0 database. I
have deleted all user & file DSNs leaving only the system DSNs.

The problem remains.

--
Tyler


"Stanko Milosev" wrote: