SSRS / xp_cmdshell usage

[TheCrow]

Hi,

Im using SSRS (SQL Server Reporting Services) to generate reports, all good. I want to merge all exported PDF's into a single PDF report, Iv got a batch file which works fine for this by using xp_cmdshell part of SQLserver2005. Though i want an interface so wrote a small .wsf (WFH) file that prompts the user then carrys out the .bat file if the user wants to. But i cannot get this to run from SSRS and will hang if i try to use xp_cmdshell. To try achieve this iv tried running a bat file that calls the wsf but this still hangs.

Is there any ways round this?

- ways to run .wsf from ssrs
- ways to run or call a wsf file using xp_cmdshell

What I have:

Merger.bat

cd "D:\Documents and Settings\Lewis\Desktop\Report"
pdftk *.pdf cat output report.pdf
rename report.pdf report.ddf
del *.pdf
rename report.ddf report.pdf

Merger.wsf

<job id="main">
	<script language="VBScript">
	    '        
        ' First, set up the message
        '
        strText = "Are you sure you want to merge" & Chr(13)
        strText = strText & "all exported PDF files?"
        strTitle = "Merge Files"
        intType = vbYesNoCancel + vbQuestion + vbDefaultButton2
        '
        ' Now display it
        '
        Set objWshShell = WScript.CreateObject("WScript.Shell")
        intResult = objWshShell.Popup(strText, ,strTitle, intType)
        '
        ' Proccess the results
        '
        Select Case intResult
            Case vbYes
                ' Call Merger.bat
                sProgRun = Chr(34) & "D:\Merger.bat"
                objWshShell.Run sProgRun, 1, True
                WScript.Echo "PDF's have been merged"
            Case vbNo
                WScript.Echo "PDF's have not been merged"
            Case vbCancel
                ' Do nothing
        End Select
	</script>
</job>

 

[whitecrook]

Hmm. in merger.bat I would use cd /d rather than cd.

Not too sure on the second one.. you could do a simple menu/command interface in XP scripting avoiding using .wsf files. But won't be graphical i am guessing .wsf is a GUI thing.

 

[TheCrow]

I could try that in the .bat but that bit works fine (actually they both work fine, just not calling through xp_cmdshell)

Would like to keep it in a windows GUI style as anything else scares users and ruins the flow/look of my program.

Is there any other way of getting scripts or batch files to run from SSRS?

Iv tried the SHELL(...) command which works only locally, wont work once I have deployed it to the server.