Scheduling dos commands through jobs dtsrun

Question

Scheduling dos commands through jobs dtsrun

Stewart Martin

SSC Rookie

Points: 36
More actions
October 8, 2004 at 7:36 am

#162385

We are currently migrating our old DB server from Win2000/SQL 2000 to a new Win2003/SQL 2000 server.
Everything is patched on the new server etc.
We have scheduled jobs on the old server which run various dts packages via dtsrun.
However one of those dts packages is an activeX script which creates a batch file and runs a dos ftp session to receive data and then process it.
All works well on the old server but on the new server the job fails to launch the DOS session.
I've search for similar problems but haven't been able to find a solutions. (We even set the dtsrun command line to create a logfile. There's no error the job just hangs when we try to run it and get no log file.
Interestingly the dts package works fine if executed on its own so my assumption is it's something to do with the Agent.

Any ideas?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SALIM ALI SSC Eights! Points: 859 More actions · Answer 1

I would check to see if the sqlagent service (and maybe also the MSSqlServer service) is starting up using the local system account which may not have suffiecient permissions to be running tasks on your network.

I had something similar happen to me running dos scripts in a scheduled task via the sql agent and starting that service using a user account that authenticates to the network solved the problem of the dos script not working.

hope that helps

Sal.

Stewart Martin SSC Rookie Points: 36 More actions · Answer 2

Thanks for that. Changed the Service startup account to the main admin account as a test and that worked OK. Will test what rights are required for the SQL service now. Thanks for hte advice.

Paul McCurdy Ten Centuries Points: 1377 More actions · Answer 3

Interesting, as I have what could be a similar problem that I was just going to post:

I have a DTS job that executes just fine when executed manually, but fails if I schedule it using SQL Agent. We have other packages that SQL Agent runs OK, but this particular one, which has multiple steps that run SQL against a Teradata DB, before selecting and returning a dataset into a SQLServer table, always fails with SQL Agent.

The other jobs that do execute OK, also use SQL against the same Teradata source, so I don't think it's that. However, at one time the error message did mention something about MS ODBC. Could it be Microsoft's driver that's at fault?

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

I have to deal with a Teradata data warehouse and gave up on the ODBC driver long ago. I don't know if it is Microsoft or Teradata code at fault, but it's way too slow. I have to access it over a VPN, which makes it even slower.

Use Teradata's Fastload and Fastexport tools to create .BAT files to run your queries, then call those from the DTS package.

But getting back to your Agent issue, you say other jobs (DTS jobs?) execute ok (run manually, or from Agent?) using the Teradata source, so how could the ODBC driver be the problem? Is it something that fails immediately, or times out? All my timeout problems disappeared when I switched to FastExport.

The big difference with scheduled jobs (as discussed earlier in this thread) is the login account the Agent service runs under. If the problem is something that broke when migrated to Windows 2003, it can be some obscure permission that used to be on but is now off by default.

Paul McCurdy Ten Centuries Points: 1377 More actions · Answer 5

I'll give the Fastload a try. The SQLAgent uses a special id 'Jobadmin' with similar privileges to SA, so that shouldn't be the problem. Nor the timeout. It always chokes on 1 step, which I'm having a hard time identifying in the package.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

If its a permissions problem, its almost certainly an OS permissions problem not SQL Server and having "similar privelges to SA" may not be good enough. To close security loopholes in Win2k3 MS has changed a lot of settings and added new ones. Linked servers require access from one server to another, which is how a lot of viruses and worms propogate, so they are greatly affected.

When I migrated to SQL on Windows 2000, I had a problem with using a linked server for DBase files. The Jet provider requires rights to create a separate process on the server, and users (by default) do not have that right. Having full sysadmin rights on the server only covered me in the SQL Server world, not the OS. I could import the files using DTS, which uses ODBC drivers and merely needed read permissions on the database files. I could use the linked server when logged in as sa, because then Windows saw the SQL Server local system login and not my Windows login (but not if the DBase file was on another network share where sa had no access).

Recently I had a problem with a script on a Windows 2003 server calling a remote stored procedure on Windows 2000 server. If I just called the stored procedure to read the results it worked fine, but if I tried to save them with INSERT - EXEC it failed. This escalates the call to a distributed transaction. Distributed Transaction Controller was installed and running on both systems, but in the Microsoft Knowledge Base I discovered that the Windows 2003 DTC service is installed with SQL Server but configured to not allow distributed transactions by default. (This may only affect Win2003 - Win2000 cases, where Win2003 can't use full Kerberos login authentication. Or it may be something else entirely, gimme a break I'm a DBA not a network admin.)