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?