DTS Failure when scheduled as Job (2nd try) 2

[jcrr]

2nd try...

(Original message posted on 09/16/00)

=============================================

Greetings!

I'm having this problem in MS SQL Server 7.0:

I programmed a DTS as a Local Package. This DTS, among another things, runs a DOS batch that performs an ftp to an UNIX server to get a data that is passed to a database table afterwards. Finally, it runs a stored procedure.

If I try to execute this DTS as a Local Package (i.e. "execute package&quot it runs without problems. However, when I schedule it (which is automatically converted into a Job) and try to run it as a job (either manually or automatically) it bombs and generates this sequence of errors:

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147217887 (80040E21) Error string: Errors occurred Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217887 (80040E21); Provider Error: 0 (0) Error string: Errors occurred Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

So, the obvious question is... what I'm doing wrong?
Please advise. Thanks.

jcrivera

 

[robox]

Hi. Unfortunately, I don't have the answer, but a related problem. I was wondering since some time has passed, whether you have resolved this issue.
I believe the answer has to do with converting the DOS commands to a win32 executable file. [sig][/sig]

 

[TomSark]

I had this before...

This is almost always a permissions issue. Ensure that your SQL Agent service account has the permissions needed to do it's thing (i.e., permissions on shares, folders, files, access databases, etc.). [sig][/sig]

 

[jcrivera]

Hello (jcrr and me are the same guy)
I already checked my account permissions and made sure that in both MSSQLServer and SQLAgent services my account/password is validated.

I also checked this account's permission. Have it configured as a System Administrator with db_datawriter/db_datareader priviledges.

J.C.
[sig][/sig]

 

[TomSark]

Is this a valid NT domain account (not builtin/administrator)?

Tom [sig][/sig]

 

[robox]

Tom,
In my situation, the account used to administer SQL is a valid NT account. The account has been given administrator privledges. The file system that contains the .bat file is completely shared. The .bat file contains two DOS commands, a copy and a del. Neither command executes. Substituting the xcopy command for the .bat file does work, but only does the copy. It seems that SQL in not able to execute DOS commands. I believe this is the issue.

John [sig][/sig]

 

[jcrivera]

In my case, my account (let's name it "MyAccount&quot is a valid NT account as well (with Administrator priviledges in the local server) and the other priviledges I mentioned above. The only difference is that there exists another "MyAccount" as a local SQL Server account. I don't suspect this might be the reason of failing because these jobs were working perfectly under this "double account" environment until a couple of months ago, when simply one day they decided not to work properly anymore (why? that's what I'm trying to find in this forum).

For example, one of my DTS contains a truncate, then a DOS batch file that performs an ftp to a .txt, then the data transfer, then a stored proc. No one is running either.

PS: I'm using the NT "MyAccount" account in both the MSSQLServer and SQLAgent services.

 

[TomSark]

Here's a checklist for you that you can follow:

** The package is owned by you
** The agent account is a domain account being authenticated by a DC in, say,
DOMAIN2
** The SQLAgent is being run under the context of a domain account
** The SQLAgent account and your account are both members of
BUILTIN\administrators on the SQL Server
** The SQLAgent account and your domain account are members of the sysadmin fixed server role
** The directory in which the files reside is shared, with
"Change" and "Read" permissions granted to BUILTIN\administrators
** The SQLAgent account and your domain account have been permitted access to the SQL Server database
** These users have not been explicitly DENIED access to any object being "touched" by the DTS package in the database.

Hope this helps

Tom

 

[jcrivera]

Sorry Tom, I'm a bit confused.

Are you saying that if one of this checklist's points fails, this will result in a bombing?
Or you rather mean that all of these conditions must be accomplished in order to have success while running the job?

Please advise.

J.C.

 

[TomSark]

I would ensure that these conditions are met if you are having problems. I have had the same problem in the past, and this is a checklist that Microsoft gave me.

Tom

 

[kjobob]

jcrr
I had a very similar problem, and couln't get dts to run until we went into the services, and properties for the sql svc agent and set the logon to use the administrator logon and password.

 

[robox]

jcrr, kjobob,
My particular problem with running a package that contained a .bat file, and the .bat file not executing, was caused from using wordpad.exe instead of notepad.exe to edit the .bat file.

Once I copied the contents of the file to my clipboard from the wordpad file and into the notepad file, the bat file executed properly.

 

[TomSark]

Yes, but the issue here is that it works when you manually run the package, but doesn't when you schedule it as an agent job. When this is the case, the account that the agent uses must have the same capabilities as the account that is used when the package is manually executed...

Tom