Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 execute reports using rs.exe

Author  Topic 

lols
Posting Yak Master

174 Posts

Posted - 2007-08-28 : 13:47:12
Hi,

I run reports using rs.exe from inside a stored procedure like this :

set @cmd =  'rs -i "C:\rep.rss" -s http://localhost/reportserver -v custparam=70 -v orderparam=20 -l 0 -e mgmt2000 -t -b'

EXEC xp_cmdshell @cmd


The rep.rss file looks like this :

Dim format as string = "PDF"
Dim filePath as String = "C:\Reports\"
Dim fileN as String = "MyReport"
Dim fileName as String = filePath + fileN + ".pdf"

Dim reportPath as String = "/MyReports/Report1"

Public Sub Main()

' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string = "application/pdf"
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

' Report Parameters
Dim parameters(1) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "cust_no"
parameters(0).Value = custparam
parameters(1) = New ParameterValue()
parameters(1).Name = "order_no"
parameters(1).Value = orderparam
results = rs.Render(reportPath, format, _
Nothing, Nothing, parameters, _
Nothing, Nothing, encoding, mimeType, _
reportHistoryParameters, warnings, streamIDs)

' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()

End Sub


The problem is that the command hangs the stored proc for a long time. At times when I use the same command on a command line, it executes and generates a pdf at times. Sometimes it just keeps on executing endlessly.

How do I troubleshoot this? I am using Sql 2005 with sp2 on Vista.
Has it got to do something with xp_cmdshell? I enabled it using sql server surface area configuration.

Thanks.
   

- Advertisement -