August 24, 2011 at 4:54 pm
Our reporting team has created a reporting stored-procedure to measure sales performance.
When running the procedure itself from Management Studio, it executes, and returns all the data that's needed (so far so good!)
Ex:
exec [dbo].[rpt_SalesPerformance] '', '2011-08-01', '', '', '', ''
However, when attempting to render the report from rs.exe at the command line (using xp_cmdshell), we see the error below:
DECLARE @cmdrun varchar(500)
SET @cmdrun = 'rs.exe -e Exec2005 -s http://OURSERVER/reportserver -i "C:\FTPFILES\RSSFile\SSRSReportsCSV.rss" -v vFilename=\\MyCompany_data\Shared\Internal_Reports\Sales_Performance\Sales_Performance.csv -v vReportPath=/_ReportMaster/Sales_Performance -v vParam1=3 -v vParam2="2011-08-01 12:01:59.000" -v vParam3=* -v vParam4=* -v vParam5=* -v vParam6=* -t -l0'
EXEC Master.dbo.xp_cmdshell @cmdrun
OUTPUT
Unhandled exception: Insufficient system resources exist to complete the requested service
A few things to note:
1. There are 22,000+ reports that run just fine using the method above
2. This "used" to work a few weeks ago (leading me to believe this is a resource issue)
3. Permissions/security isn't an issue
4. The average run time for the stored-procedure (above) is about 5.5 minutes (we have other reports that run for 20) - so the length of time to render should not be an issue?
Does anyone know why this is happening or have run into the same issue before?
Is there a limit on the amount of data (i.e. # of rows) that can be returned/rendered by rs.exe?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 24, 2011 at 5:25 pm
I have noticed that I do NOT get the error when writing the file out to a local drive...
Ex:
SET @cmdrun = 'rs.exe -e Exec2005 -s http://OURSERVER/reportserver -i "C:\FTPFILES\RSSFile\SSRSReportsCSV.rss" -v vFilename=D:\Sales_Performance.csv -v vReportPath=/_ReportMaster/Sales_Performance -v vParam1=3 -v vParam2="2011-08-01 12:01:59.000" -v vParam3=* -v vParam4=* -v vParam5=* -v vParam6=* -t -l0'
So my thoughts on thinking there was a limitation on the rs.exe may not be valid...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 27, 2012 at 8:03 am
Hello there,
Were you able to resolve the above issue? We're having the same issue rendering one of our reports in MHTML.
It works just fine when you run it (rs.exe) from the command line, But it fails when the enterprise scheduling tool issues the same command with the error message
"Unhandled exception:
Insufficient system resources exist to complete the requested service."
Cheers!
~N
ps:The reason we're rendering it in MHTML is because excel(2003) can't handle more than 65,536 rows on one worksheet.
February 27, 2012 at 9:41 am
Yes. It's definitely a limitation with rendering a large report across the network using the rs.exe application (you'd think there'd be some documentation on that somewhere on BOL - and if there is, I couldn't find it).
The only way around it is to render the report locally on the server creating it, then copy it across the network to desired location.
What we did was create an additional table in our reporting database system "LocallyRenderedReports" and something like this:
/* Check to see if this is a Report that needs to get generated locally */
IF (@ReportID = (SELECT ReportID FROM dbo.LocallyRenderedReports WHERE ReportID = @ReportID AND ReportTypeID = 1))
BEGIN
SET @FileName = 'C:\' + @ReportName + @Renderer
END
ELSE
BEGIN
SET @FileName = ISNULL(@path,'\\Normal\Network\Location\Reports\'+ @Folder) + @ReportName + @Renderer
END
SET @CmdRun = 'rs.exe -e Exec2005 -s http://REPORTSERVER/reportserver -i ' +
'"C:\FTPFILES\RSSFile\' + @RssFile + '"' + ' -v vFilename=' + @FileName +
' -v vReportPath='+ @ReportPath +
' -v vTimePeriod='+@TimePeriod +
' -v vrptDate="'+ @rptDate +
'" -v vfrptGroup='+ @frptGroup +
' -v vVenNumber='+@VenNumber +
' -v vdatestart='+@datestart +
' -v vdateend='+@dateend +
' -t' + ' -l0'
/* Move the reports to proper network location */
IF (@ReportID = (SELECT ReportID FROM dbo.LocallyRenderedReports WHERE ReportID = @ReportID AND ReportTypeID = 1))
BEGIN
SET @Cmdrun = 'MOVE /Y ' + RTRIM(@FileName) + ISNULL(' ' + @path,' \\Normal\Network\Location\Reports\'+ @Folder)
BEGIN TRY
EXEC Master.dbo.xp_cmdshell @cmdrun
SET @FileName = ISNULL(@path,'\\Normal\Network\Location\Reports\'+ @Folder) + @ReportName + @Renderer
END TRY
BEGIN CATCH
SELECT @ErrNo = ERROR_NUMBER(), @ErrMsg = ERROR_MESSAGE()
SET @ErrMsg = 'ERROR :: ' + @ErrNo + CHAR(13) + @ErrMsg
SET @Body = @ErrMsg + CHAR(13) + CHAR(13) + 'Command attempted: ' + CHAR(13) + @cmdRun
SET @Subject = 'ERROR :: There was a problem copying the ' + RTRIM(@ReportName) + ' Report to ' + @FileName
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'someone@whocares.com',
@blind_copy_recipients = 'someoneelse@whocares.com',
@subject = @Subject,
@body = @Body,
@importance = 'High'
END CATCH
END
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 27, 2012 at 11:36 am
Nice workaround there!!
I doubt if they'll let us store files on their drive even if it was meant to be temporary!
(Enterprise policies 🙁 ..don't ask!)
We were about to modify the boot.ini file to allocate more space to applications and check if that worked!
Thanks for bunch for the workaround and the quick response!!
Cheers!
~N
February 27, 2012 at 12:37 pm
No problem! Remember, the creation of the files on the local folder is very temporary (milliseconds to seconds) while they are MOVED to the proper location...suggest using "c:\temp". 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply