Execute Process Task not failing, but not executing the batch comm

Hi!

I can execute the following command from the windows "Run" prompt and it
runs perfectly. It takes about 5 minutes, and I can see it running in the
task manager.

C:\Progra~1\BMGPEACH\mngrexec.exe c:\chartimport.cds


I also have a batch file that runs perfectly when you double-click on it.
It's called c:\ChartImport.bat and has just two lines.
_________________________________________
cd\Program Files\BMGPEACH
mngrexec.exe chartimport.cds
________________________________________

I can also see this running in the task manager when I double click on it
and run it.

But, when I try the following line within tsql the program doesn't show up
in the task manager.

EXEC master..xp_cmdshell 'C:\ChartImport.bat'
___________________________________________________________________
NULL
C:\Program Files\Common Files\SYSTEM\MSMAPI\1033>cd\Program Files\BMGPEACH
NULL
C:\Program Files\BMGPEACH>mngrexec.exe chartimport2.cds
NULL
___________________________________________________________________

I've even tried at least four different versions (with quotes, without
quotes, trying to create a command string and execute that), and nothing
works. The following command just returns garbage about not finding the
program, even though the exact same charcters inside the single quotation
marks works from the "Run" prompt.

EXEC master..xp_cmdshell 'C:\Progra~1\BMGPEACH\mngrexec.exe
c:\chartimport.cds'

or

EXEC master..xp_cmdshell '"C:\Program Files\BMGPEACH\mngrexec.exe"
c:\chartimport.cds'

Any suggestions? Might I have something set weird in SQL Server? I am
definitely logged in as the system admin, so I KNOW its not a permissions
issue.

Any suggestions or help would be greatly appreciated.

Thanks!
Karen Grube


Comment from angelIII
Date: 09/11/2005 11:39PM PDT
Comment

Any suggestions?
rather a question: does the .exe do anything on the network, ie read some
files on the network or so?
also: do you have the possiblity to put some error logging into that program
to see how far it comes, to determine exactly where that program get's into
trouble... because I am quite sure that it is NOT the problem of sql server,
possibly a configuration change can help.

To explain: if the program does some work with resources on the network, sql
server being by default configured to run as localsystem account, it will not
have any access to the network resources. to solution would be to let sql
server run under a domain account which has the privileges needed

Comment from klgrube
Date: 09/12/2005 07:17AM PDT
Your Comment


Hi!

Thanks for the reasurrance about the output! I really was worried.

The program I'm trying to run is a commercial program from Classic
Software called Data Manager for Peachtree that reads data from Peachtree
(btrieve) and loads it up into SQL server. So, I can't really add any
debugging to it.

The Peachtree database is located on a network share. When you say that
SQL runs under the local system account, I'm assuming you mean whatever
account I have set up as the SQL Server Service account, which isn't the
local system account in this case. It's an account that I use to run all
kinds of batch jobs, retrieving data from all across our network. However,
this IS the first time I'm trying to access data using Data Manager on the
Peachtree network share quite like this.

So, I'll start off by creating a batch file that simply does a directory
of that network share after the "CD" command.

If that works, then I'll check the command file I created within Data
Manager. Perhaps when Data Manager runs, it isn't finding sufficient
permissions to access the Peachtree tables, although I don't know why that
should be the case since it runs perfectly from the 'run' command. I can
also copy the peachtree folder locally and try pointing there.

I did change the "Proxy" account in the SQL Server Agent to use the same
account as the SQL Server Service rather than 'sa'. As a test, I ran several
other small little xp_cmdshell commands (like dir commands and things like
that) so I do know that the account can run xp_cmdshell. That's the good
news. But, how can you control the account under which an "xp_cmdshell"
command runs?

But, just to clarify: all I'm trying to do is get this Data Manager
program to execute from SQL, and so far I'm not having any luck. Clearly, it
has a command line interface, since it runs properly from the Run prompt. I
just can't get it to run from SQL. I may try giving Classic Software a call
too.

I'll let you know how it goes.

Thanks!
Karen

________________________________________________________________
Hi!

I still need help with this!

At this point, I've even created a DTS package that supposedly executes
the batch file I described above within an "Execute Process Task". When I'm
in the DTS package and run it, it works perfectly. However, when I try to
execute the package from DTSRUN within SQL or from within a Job, the task
doesn't execute. Technically the step doesn't fail. It just doesn't execute
the batch file as far as I can tell. At least the table that is supposed to
be populated by this batch process is never populated. I even made sure that
security on the batch file was completely open! I can't figure this out at
all. Why does the batch file run from within the Execute Process Task inside
the DTS Designer, but not from a Job or from the Query Analyzer (using dts
run). All of the subsequent steps of the job run correctly except the
Execute Process task.

Any suggestions? Regarding the question of Permissions, I'm not sure
what account is being used when! When I log in to this server, I log in
using an account that is an Administrator, so my guess is that this is the
account being used to grab the data from a file on the network. But the
same account is used for the Job that I tried creating, and it's not working.
Is there some way of specifying the account under which an "Execute Batch
Process" runs? Or, within a batch file, is there some way of setting the
permissions being used by the command being executed?

Please let me know!

Thanks!
Karen




.

Relevant Pages

  • Re: Execute Persmission denied on object sp_OACreate
    ... > I logged in as that user, tried to execute the DTS ... which then launches the DTS package using the sp_OA* procs? ... account is used during proc execution. ... > proxy account to use in the Job Systems tab of SQL Server Agent ...
    (microsoft.public.sqlserver.security)
  • Re: xp_cmdshell issue, local system
    ... please make sure the service account SQL Server running under has ... this error may be because the SQL Server service ... account doesn't have the rights necessary to change security context to the ... >I want to give access to a regular user to execute xp_cmdshell. ...
    (microsoft.public.sqlserver.security)
  • Re: dts or xp_cmdshell permissions
    ... You could have the users execute the package ... Change the SQL Server or SQL Server Agent Service ... >> Account Without Using SQL Enterprise Manager in SQL Server ...
    (microsoft.public.sqlserver.tools)
  • Re: ADO.NET Timout
    ... That in turn execute SP ... BeginExecute method of the Command object. ... How can I call a stored procedure to do its thing asynchronously. ... see "Explicit Transactions" in SQL Server Books ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: bcp data export from SQL Server to file
    ... the OS security context will be the SQL Server ... service account when executed by a sysadmin server role member. ... But when I execute it threw the application - nothing ...
    (microsoft.public.sqlserver.programming)