January 11, 2011 at 2:59 pm
On our Production server where our reports used to run the following statement works perfectly:
Declare @CmdLineRun nvarchar(2000)
set @CmdLineRun = 'C:\TotalHTMLConverter\HtmlConverter.exe "C:\TotalHTMLConverter\Reports\PaymentConfirmation_39385_2011-01-11_1327.htm" "C:\TotalHTMLConverter\Reports\PaymentConfirmation_39385_2011-01-11_1327.pdf" -cPDF -LM 2 -TM 1'
PRINT @CmdLineRun
EXECUTE MASTER.dbo.xp_cmdshell @CmdLineRun
We recently moved the executable above and installed the app on a new reporting server (and while our report writers state it's been running successfully up until a few days ago) we can't get it to work via management studio or in an agent job.
A few things to note:
1. There is a valid proxy account and it does have prvillieges to run
2. If I copy and paste the code above into a command window and run it, it works
3. If I run a modified command (like below) from management studio, it also works
Declare @CmdLineRun nvarchar(2000)
--set @CmdLineRun = 'C:\TotalHTMLConverter\HtmlConverter.exe "C:\TotalHTMLConverter\Reports\PaymentConfirmation_39385_2011-01-11_1327.htm" "C:\TotalHTMLConverter\Reports\PaymentConfirmation_39385_2011-01-11_1327.pdf" -cPDF -LM 2 -TM 1'
set @CmdLineRun = 'dir *.*'
PRINT @CmdLineRun
EXECUTE MASTER.dbo.xp_cmdshell @CmdLineRun
However, when I attempt to run the original command (using the TotalHTMLCOnverter) it runs (never returns an error, and will just sit there executing for as long as I let it run. Firing off an sp_shom2 shows me immediately that the process is sitting in PREEMPTIVE_OS_PIPEOPS - and will stay there until the kill the HTMLConverter.exe process from the task manager.
Any ideas on why I can run all kinds of queries with the shell except the one I need to run???
PLEASE help
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 11, 2011 at 3:52 pm
There is a connect item about that issue similar with xp_cmdshell call hanging with this wait type. It appears it may be an issue outside of SQL Server, something on the OS side. Since it appears that SQL is calling the HTMLConverter.exe it is just not able to handle what it could be returning.
Just curious are you generating the reports from SQL 2008 Reporting Services? If so, why not just let it generate the PDF files for you?
Connect Item found here
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 11, 2011 at 4:26 pm
Again, I can run it from our production box just fine as is (and is SQL 2005), but not from the new box which is SQL 2008.
With the reporting model we have, using the Reporting Services to create the PDF is not an option
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 12, 2011 at 12:13 pm
I have not worked with this converter before but found the command line options here. I noticed it has a -log option to write to a log, if you do this does it ever write anything to it?
If you enable logging on the job step in the SQL Agent job does it output any errors? EDIT: I just remembered you mentioned it just sits there when you run it, so this may or maynot give you anything.
I saw that the website for this utility has a forum on it you might want to post something there. I don't really know of anything that changed from SQL 2005 to SQL 2008 with the xp_cmdshell command.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 12, 2011 at 3:59 pm
Found it!
Turns out that the software install is user specific and must be individually licensed by each user on the server: Simple installing it as an administrator and registering it doesn't work.
As a local admin I logged onto the box via RDP and successfully installed and registered the software. This allowed me to run the commands via the command prompt. However, when running it via Management Studio it no longer cares who I am logged into SQL as, only which account is executing the xp_cmdshell (the proxy account from AD). This is why when run from SQL the process hung and immiedately went into the PREEMPTIVE wait...as it was waiting for it to be registered.
Our domain account used for the proxy account is valid and has appropriate security privileges but is unable to logon to workstations/servers so the only wauy I could get it registered for that user was to run:
C:\Runas /user:MYDOMAIN\thisaccount "C:\TotalHTMLConverter\HtmlConverter.exe"
This then prompted me for a password for the proxy user, and then to select a language. After this I had to re-register the software and restart the program entirely.
Now it works.
What a rough ride...Shiizzzza!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply