Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 771
  • Last Modified:

Problem with XP_CMDSHELL - Running an Executable

Hello,

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:
DECLARE @CMDSTRING AS VARCHAR(255)
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?
0
Roxanne25
Asked:
Roxanne25
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Have you tried specifying the literal path to Ledger_Grp.xml?
0
 
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.

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

Ended up being the trick.
0
 
Roxanne25Author Commented:
Question solved on my own.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now