Problem with XP_CMDSHELL - Running an Executable


We are using a tool called Cognos AAF which is a datawarehousing tool and we use SQL Server as our backend.  I'm trying to automate the loading of the data into this tool via SQL Server.  We currently do all of our job loads in SQL Server and I would like to run the data load for this AAF tool in SQL Server.

There is a command line function to run the data load using an executable file provided with AAF.  You then tell it which data load script to run and poof ...loads the data.

So, the command line would look something like this:

D:\my directory\pwexec.exe LoadScript.xml

If I open a cmd shell in windows, I can get this to work just fine via the DOS shell window.  However, when I try to run this in SQL Server with an xp_cmdshell function... it will not work.

I have tried creating a .bat file to kick off the load ... but nothing will get SQL to be able to run this script.  

This is the SQL code I'm using:
SET @CMDString = 'D:\progra~1\cognos\ap\bin\pwexec.exe Ledger_Grp.xml'
EXEC master..xp_cmdshell @CMDString

I have also made sure that the environmental variable is set for the above "bin" directory and have tried lots of variations on trying to get this to run.  Including using a batch file.

The only thing that it returns is NULL and it takes about 2 seconds to complete.  I know its not even attempting to run the .exe because no log file is being generated in the AAF application.

Anybody have any advice?
Who is Participating?
Roxanne25Author Commented:
Hi, thanks for the reply... I was able to resolve the issue using a batch file.

My batch file didn't work previously because I did not have the CD command formatted properly.

cd /D D:\progra~1\cognos\ap\bin
pwexec.exe Ledger_Grp.xml

Ended up being the trick.
Kevin CrossChief Technology OfficerCommented:
Have you tried specifying the literal path to Ledger_Grp.xml?
Roxanne25Author Commented:
Question solved on my own.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.