[sims] Command Line Reporter Issue

[cn198]

Hi there, just recently I'm having problems running reports with the SIMS Command Line Report utility. I've been using it for the last 8 years or so without too much bother, but all of a sudden when I run the report, the export file doesn't always get exported. The Windows Command window pops up fine, shows I'm logged in and finds and runs the report. But the window closes without showing the Exporting file... line. If I try and run the report again, with everything being the same, it will work fine and I get the export file back. I have about 10 different types of reports that I run, and it can happen to any of them, and it doesn't happen all the time, just every now and then. Has anyone noticed this type of behaviour? No error message is ever displayed either. The utility version is 7.188.41.0.

 

Chris

[Esteban_Child_of_the_Sun]

We've found that since moving to SQL2016 that our reports now timeout after 300 seconds, I logged it in Feb 2019 and Capita are still investigating. If you open a command prompt and run the script that generates your report manually do you see any other messages/errors? If it's a PowerShell script then you may want to re-direct all the output from CommandReporter.exe to a log file to get a better view of what's going on and when. I'd also put an output of the current time before and after running CommandReporter.exe in the log file.

[cn198]

Thanks for your reply. I've found that this happens regardless of report run time. So we have some reports that can take a couple of minutes, some can take just a few seconds. It happens in all of them, but only very occasionally, and as I say, if it fails to export I just run it again (exactly the same parameters), it runs fine.

 

I run my reports automatically through Excel VBA. So there is a button in my spreadsheet to run the code, and then I use the Shell() command which uses the command prompt to run commandreporter.exe. When the report has finished running the command prompt closes immediately so it's often hard to see what's written. When this problem occurs it looks like nothing gets printed after it says "Running Report...", but I can't be 100% sure. You mention about logging the output from this window. Do you know how to do this, because that sounds like it could be very useful.

[Knil92]

So you could try using the /K to keep the cmd window open after it runs the shell arguments and see if its coming back with any errors?

[cn198]

So you could try using the /K to keep the cmd window open after it runs the shell arguments and see if its coming back with any errors?

 

 

If I use /K in my shell command nothing changes, where do you use it?

 

My code that then runs in the command prompt is below (with the /K)...

 

C:\Program Files (x86)\SIMS\SIMS .net\CommandReporter.exe /user:cn /password:"" /K /report:"SIMS Databook Report" /output:"\\User\Documents\\Testing\Ce11_SIMS_report_output.xml" /paramfile:"\\User\Testing\SIMS_filters_def.xml"

[Esteban_Child_of_the_Sun]

from https://www.myonlinetraininghub.com/vba-shell

[h=3]Internal Commands[/h]To call an internal command like dir, you must start an instance of the actual shell, which in Windows is cmd.exe. You then say that you want to use the dir command. The /k switch specifies that cmd should not terminate after dir has finished. You can terminate cmd later.

[font=monospace]PID = Shell("cmd /k dir", vbNormalNoFocus)[/font]

[Knil92]

The excel bit should look something like;

 

cmd = cmd.exe /K C:\Program Files (x86)\SIMS\SIMS .net\CommandReporter.exe /user:cn /password:"" /report:"SIMS Databook Report" /output:"\\User\Documents\\Testing\Ce11_SIMS_report_output.xml" /paramfile:"\\User\Testing\SIMS_filters_def.xml"

 

Shell (cmd)

[cn198]

I got in a right mess with my quote marks! So I build my command in VBA to then use in the Shell, called like this: Shell(cmdStr,vbNormalFocus), but trying to use this different method of using the command prompt I initially tried Shell("cmd /k " & cmdStr,vbNormalFocus), but that fails because of the space in my exe call. After faffing about I finally realised my command string should be entered as a string into the shell command, like this: Shell("cmd /k " & """" & cmdStr & """",vbNormalFocus). So that now runs happily, leaving the shell window open. So far though, typically, no errors. I'll keep trying! Thanks for both your help here, this is helping me to at least make progress!

 

Chris

[Knil92]

Yeah I always get confused with the amount of "'s and it gets even better when you're trying to make a batch file from vbs, that creates a text file with special characters

[cn198]

When I first started out using VBA to interface into SIMS with the command reporter, I made a mistake in the call to the commandreporter.exe file, and don't ask me how because I don't know how or why this happened (although I think the quote marks were part of the problem), but I overwrote the exe file! So that meant no more command reporter! I had to sheepishly go to the IT manager and get him to repair the install. Oops, lesson learn't! A little knowledge can do a lot of damage!