0

I built a stored procedure to render a SSRS report and save it to a specified location by using a combination of SQL statement and batch cmd.
Below is the code:

CREATE PROCEDURE sp_renderSSRS_doc
    @pRsURL VARCHAR(1000),      -- Rerport URL
    @pDocno VARCHAR(100),       -- Doc no
    @pRsFormat VARCHAR(50),     -- Render report format
    @pRsExt VARCHAR(10),        -- Render report extention
    @pBatchFilePath VARCHAR(100),   -- Batch file include powershell command
    @pOutPutPath VARCHAR(500)   -- output location
AS
BEGIN

SET NOCOUNT ON;

DECLARE @pCommand VARCHAR(4000)
SET @pRsURL = @pRsURL  + ' ' + @pRsFormat + ' ' + @pDocno  
SET @pCommand = @pBatchfilePath + ' ' + @pRsURL + ' ' + @pOutputPath
EXEC xp_cmdshell @pCommand

END
GO

Batch File C:\Reports\RunSSRSDN.bat

@echo off
SETLOCAL ENABLEDELAYEDEXPANSION
:: --- Check for sufficient parameters ---
if "%~4"=="" (
    echo Insufficient parameters.
    echo Usage: %~nx0 ReportURL pDN OutputFile
    goto :EOF
)
:: --- Read passed parameters ---
set "ReportURL=%~1"
set "rsFormat=%~2"
set "pDN=%~3"
set "OutputFile=%~4"

:: --- Build the full URL step by step using CALL SET ---
set "FullURL=%ReportURL%"
call set "FullURL=%%FullURL%%&rs:Format=%rsFormat%"
call set "FullURL=%%FullURL%%&pDN=%pDN%"

:: --- Call PowerShell using default credentials ---
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -Command "Invoke-WebRequest -Uri \"!FullURL!\" -UseDefaultCredentials -OutFile \"!OutputFile!\""

ENDLOCAL

SQL Statement to execute

DECLARE @pRsURL VARCHAR(1000) = 'http://mySSRSServer/ReportServer?/Development/test_dynamic_report'
DECLARE @pDocno varchar(100) = 'DOC001'
DECLARE @pRsFormat VARCHAR(100) = 'PDF'
DECLARE @pRsExt VARCHAR(100) = 'pdf'
DECLARE @pBatchfilePath VARCHAR(100) = 'C:\Reports\RunSSRSDN.bat'
DECLARE @pOutputFolder VARCHAR(100) = 'C:\Reports\'
DECLARE @pOutputPath VARCHAR(100) = @pOutputFolder + @pDocno + '.' + LOWER(@pRsExt)

EXEC sp_renderSSRS_doc @pRsURL, @pDocno, @pRsFormat, @pRsExt, @pBatchfilePath, @pOutputPath

I want to move the batch file process into the stored procedure, because there is "&" inside the report URL. I tried to use the below hardcoded statement to try

DECLARE @pCommand VARCHAR(1000)
SET @pCommand = 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -Command "Invoke-WebRequest -Uri 
                \"http://MySSRSServer/ReportServer?/Development/test_dynamic_report&rs:Format=PDF&pDN=CN25030051\" -UseDefaultCredentials -OutFile \"C:\Reports\CN25030051.pdf\""'

EXEC xp_cmdshell @pCommand

and got below error

Msg 15121, Level 16, State 21, Procedure xp_cmdshell, Line 1 [Batch Start Line 8]
An error occurred during the execution of xp_cmdshell. A call to 'CreateProcess' failed with error code: '5'.```

I think this is related to the "&" is not allow use directly on PowerShell, but I have already use \"...\". Does anyone have idea on it?

I cannot run cmd with "&" by using xp_cmdshell.

15
  • As per How to Ask, please use meaningful titles; 3 tags is not an appropriate title for your question. Commented May 21 at 10:07
  • 1
    Why are you using SQL Server, to call xp_cmdshell to save an SSRS report? Why not use a subscription? This is definitely an XY Problem. Commented May 21 at 10:09
  • because use want to render report and specify the output file name base on the data. moreover, the report receiver sometime is dynamic and it is store in the database table. Commented May 21 at 10:12
  • Error 5 means access denied, so i'm not sure the problem is with the &. Does it help just running "powershell.exe"? Could also be that c:\reports ain't accessible, who knows Commented May 21 at 10:13
  • 1
    There are ways to do that that don't involve sysadmin privileges. Either by using data driven subscriptions, or (in standard edition) spoofing the behaviour; there are articles out there about how to achieve that. xp_cmdshell is really not the solution here, in my opinion. Commented May 21 at 10:14

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.