Problem with XP_CMDSHELL - Running an Executable

Posted on 2011-10-19
Last Modified: 2012-08-14

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?
Question by:Roxanne25
    LVL 59

    Expert Comment

    by:Kevin Cross
    Have you tried specifying the literal path to Ledger_Grp.xml?

    Accepted Solution

    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.

    Author Closing Comment

    Question solved on my own.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now