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.
xp_cmdshellto save an SSRS report? Why not use a subscription? This is definitely an XY Problem.sysadminprivileges. Either by using data driven subscriptions, or (in standard edition) spoofing the behaviour; there are articles out there about how to achieve that.xp_cmdshellis really not the solution here, in my opinion.