executecommand() with batch files

[whitej_d]

See the attached screenshot. The column on the right shows the windows user which must have the correct permissions to run the bat file.

The TM1 user is irrelevant as all interaction between Windows and TM1 is done under the windows user set in the services console.

[l_kothamasu]

Hi Everybody,
Thanks to everyone.
The problem is solved, I tried with VB script and used executecommand () and it worked without any issues. I don’t why it didn’t work with .bat file, anyhow once again thanks to everyone.
Regards,

[tomok]

Hi Everybody,
Thanks to everyone.
The problem is solved, I tried with VB script and used executecommand () and it worked without any issues. I don’t why it didn’t work with .bat file, anyhow once again thanks to everyone.
Regards,

Your batch file wouldn't run because, as yyi pointed out, you have to open a command window (with the cmd /c command) as part of the batch. Using VBScript you don't have to.

[CognosConnect]

...
The problem is solved, I tried with VB script and used executecommand () and it worked without any issues
...

Can you show how VB script launches via ExecuteCommand()?
As for me, it looks like VB script does nothing though I don't get any error messages.

[rmackenzie]

Can you show how VB script launches via ExecuteCommand()?

Code: Select all

sCommand = 'cscript.exe YOUR_SCRIPT.vbs';
ExecuteCommand ( sCommand, 1 );

The 'cscript' executable should be in the system path i.e. Windows knows where to find it as it is a standard part of Windows. Note that the second argument to ExecuteCommand is important as it will cause TM1 to wait for the script to execute - use 0 if you want to immediately continue execution with your TI

As the VBScript will run 'invisibly' then in order to debug the output you simply include text file output in your VB script e.g.:

Code: Select all

Set objFSO=CreateObject("Scripting.FileSystemObject")
outFile="c:\test.txt"
Set objFile = objFSO.CreateTextFile(outFile,True)
objFile.Write "test string" 
objFile.Close

[CognosConnect]

Thanks for your quick and such a detailed answer.

I call ExecuteCommand the same way.
I think that my problem lies on x86/x64 issues. So I tried to run script under 'C:\Windows\System32\cscript.exe' and under 'C:\Windows\SysWOW64\cscript.exe'. Both without result.
I was sure that my script does not run from TI Process at all because it work fine from within Windows Explorer or Command Prompt.

But your hint about some debug info led me to that the script crashes at:

Code: Select all

Set objExcel = CreateObject("Excel.Application")

Do You have any suggestion?

[rmackenzie]

On my system with 32-bit Excel 2010, a TI running under 64-bit TM1 will run 64-bit cscript which will automate 32-bit Excel with that CreateObject command. I'm not sure what version of Excel you are running?

Irrespective, it sounds like a permissions issue. The Excel automation will be attempted under the account that your TM1 service is running under. If this account isn't sufficiently privileged then you are going to have a hard time debugging the problem. Try running the TM1 service under a local admin account (with TM1 and Excel on the same box) and see what happens. If that's not possible then you may need to stuff around with dcomcnfg.exe or do some other sort of registry hacks. If you can't run TM1 as a local admin then trying these things is probably going to be difficult for you anyway and I'd suggest you are already in deep water and should look at some other method of automating your Excel processes.

Note that Microsoft do not recommend server-side automation of their Office products in the way you are trying to do:

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

Although it can be extremely useful and quick way of getting stuff done with TM1 and Excel, VBScript definitely falls under this bracket and therefore should be approached with much caution and robust error trapping.

[CognosConnect]

TM1 service running under a local admin account

[rmackenzie]

Best of luck then.... sounds like something funky is happening on your server, unfortunately.

[CognosConnect]

My problem was solved. It was not in the area of TM1.

Further localization of problem showed that error occurs exactly at method Workbooks.Open.
Thinking that there are some nuances exactly in TM1, I tried to run the script from SQL Server (using xp_cmdshell) and received the error message at the same point of script.

Further search led me to the answer:
http://blogs.msdn.com/b/sqlserverfaq/ar ... ected=true
Resolution
************
· A “Desktop” folder seems to be necessary in the “systemprofile” folder in the location C:\Windows\SysWOW64\config\ to open an Excel file
· Create the “Desktop” folder for Windows 2008 Server (x64) under the location C:\Windows\SysWOW64\config\systemprofile
· And for a 32 bit Windows 2008 Server create the “Desktop” folder under the location C:\Windows\System32\config\systemprofile
· After creating the folder the SQL Server jobs should execute successfully

In my case I create folder C:\Windows\SysWOW64\config\systemprofile\Desktop. And this fixes the problem.

----
Note again that there were no problems when executing a script in interactive mode (including when run it on behalf of the account under which the TM1 service starts).
This suggests that some in-depth interactions (such as COM/DCOM calls) nevertheless run under local system account (some "system profile"). And finally convinces in truth of rmackenzie's:

Note that Microsoft do not recommend server-side automation of their Office products...

Again, thanks a lot to Robin (rmackenzie)!