RE: DTS Package fails when Scheduled

Servando,

Thank you for your help. It turns out that using the UNC in combination
with querying the database (instead of copying tables) and setting up the
connection as read only was the magic combination that resulted in success.

Apparently I was unable to pass on the database password when runninig the
Job as a SYSTEM process. I had tried querying and setting read only before,
but not with the UNC instead of a mapped drive.

I appreciate the help.

"Servando Canales" wrote:

Do not use mapped drives. It will not work.
Use UNC (ie. \\Servername\sharename\folder\...\) instead.
Make sure SQL Server Agent account has the correct rights/permissions (ACLs).

First I would try setting the Access DB locally on the same box (with
appropriate ACLs), if this works move the DB to the network share (again
check ACLs).

"Chuck G." wrote:

Servando's explanation made a lot of sense, and I tried it with the same fail
message. After numerous attempts to tweak the system dsn, I decided to try
and change the context of the sql agent to run under the Domain/Administrator
account, which is also the owner of the DTS package. When I ran the
scheduled job under this context, I still received the error, even though I
ran the DTS manually seconds later. This account has full access to
everything on both machines, so I figured if it was the permissions being
pushed from SQL Agent that this would definitely work.

Here is another idea on what could be wrong. I have the folder where the
access database resides on the remote computer shared, and have mapped the
drive on my SQL machine. I am using this mapped drive to create my ODBC
connection. Is this the proper configuration?

"Servando Canales" wrote:

Make sure the ODBC connection is a system DSN source (for every account to
view and use)
Scheduled jobs run in the context of the SQL Server Agent service account
(whatever you specified : Local System, a user account, etc). Probably that
is why this account cannot contact your defined DSN source, or it does not
have the appropriate permissions to connect to whatever Database you defined
on your DSN

Servando


"Chuck G." wrote:

I am running SQL 2000. I have a DTS that imports an Access database from a
remote source into my SQL server. When I excecute the DTS manually it runs
perfectly, but when run as a Scheduled job I get the following error:

Executed as user: SQLSERVER\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: Copy Data from Results to Results Step
DTSRun OnError: Copy Data from Results to Results Step, Error = -2147467259
(80004005) Error string: [Microsoft][ODBC Driver Manager] Data source
name not found and no default driver specified

Obviously the ODBC connection is not at fault since the package ruins fine
when not scheduled. I am looking at permissions, as the DTS scheduled job
password is different from the Access ODBC password. It doesn't make a lot
of sense that the job password would be passed onto the ODBC, but I am at a
loss.

Any ideas?


.

Relevant Pages

  • RE: Problems with WebParts
    ... to a database called aspnetdb. ... > The connection string specifies a local SQL Server Express instance using a ... > server account must have read and write access to the applications directory. ... > This is necessary because the web server account will automatically create ...
    (microsoft.public.dotnet.framework.aspnet)
  • Problems with WebParts
    ... The connection string specifies a local SQL Server Express instance using a ... database location within the applications App_Data directory. ... server account must have read and write access to the applications directory. ... logged-in user needs the dbcreator privilege in the appropriate SQL Server ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: sa password was inadvertently changed. With a kicker...
    ... Use some other account that the client has (any domain admin should have ... Did around there install package, and see if the sql script that changed ... Put the database files back where they should ... > I have a SQL Server 7 Standard edition server running in a ...
    (microsoft.public.sqlserver.security)
  • Re: does sql user need to be an admin?
    ... User accounts need to be granted access to SQL Server in order to ... file permissions do not control database access. ... This account is a member of the ... Grant a Windows account access to SQL Server: ...
    (microsoft.public.sqlserver.security)
  • Cannot create subscription for a SQL Server 2005 Mobile database
    ... SQL Server 2005 database but I haven't had any success. ... Windows account which has full admin permissions. ... I selected the New Subscription Wizard on the node of my ...
    (microsoft.public.sqlserver.replication)