Creating a report on a file share from a stored procedure

Hi,
I've seen one or two postings on this, and in particular 2 approaches:
1) running rs.exe from a stored procedure in conjunction with xp_cmdshell.
I have successfully managed to create and test a stored procedure that
renders a report to a file share using this approach - but for some reason
it doesn't work when integrated into the enterprise app I need it too work
with. When the sp is initiated from the app, the report appears to be
generated successfully (according to the entries in the ExecutionLog table
in the ReportServer database), but isn't being written to file on the server
(although it did in testing). No return errors, nothing in the EventLog,
nothing, nothing, nothing. Even when rs.exe is run with the -t parameter.
Very frustrating.
2) So, if I can do this quickly, I thought I'd look at a second approach i.e
accessing the web service directly from the stored procedure via the
sp_OACreate stored procedure. From a posting I've seen from Teo Lachev, he
indicates that the object to reference is MSXML2.ServerXMLHttp, with an
example provided. However what isn't clear to me is how this can be used to
render and write the report to a file share, in a similar manner to the .rss
file I used in conjunction with point 1 above. Is this approach possible?
or can the web service only be used to create an on-demand report back to a
browser?
Anyone with some examples/tips on this?
Thanks
GregWhat user security context is used when run from the app? Try (as a test)
setting Everyone Full Control to the directory where the file is being
written.
Jeff
"Greg Clark" <gclarkmail@.yahoo.com> wrote in message
news:#9IMhzamEHA.648@.tk2msftngp13.phx.gbl...
> Hi,
> I've seen one or two postings on this, and in particular 2 approaches:
> 1) running rs.exe from a stored procedure in conjunction with xp_cmdshell.
> I have successfully managed to create and test a stored procedure that
> renders a report to a file share using this approach - but for some reason
> it doesn't work when integrated into the enterprise app I need it too work
> with. When the sp is initiated from the app, the report appears to be
> generated successfully (according to the entries in the ExecutionLog table
> in the ReportServer database), but isn't being written to file on the
server
> (although it did in testing). No return errors, nothing in the EventLog,
> nothing, nothing, nothing. Even when rs.exe is run with the -t parameter.
> Very frustrating.
> 2) So, if I can do this quickly, I thought I'd look at a second approach
i.e
> accessing the web service directly from the stored procedure via the
> sp_OACreate stored procedure. From a posting I've seen from Teo Lachev,
he
> indicates that the object to reference is MSXML2.ServerXMLHttp, with an
> example provided. However what isn't clear to me is how this can be used
to
> render and write the report to a file share, in a similar manner to the
.rss
> file I used in conjunction with point 1 above. Is this approach possible?
> or can the web service only be used to create an on-demand report back to
a
> browser?
> Anyone with some examples/tips on this?
> Thanks
> Greg
>|||Hi Jeff,
The directory already has Everyone Full Control applied.
What I find so bizarre is that in theory there should be absolutely no
difference as to whether the stored procedure is called from the app
(doesn't create the file) or whether I initiate the stored procedure with a
test in SQL Query Analyzer (creates the file). The rss file createing and
rendering the report is called from within the sp, and the same credentials
are being used irrespective of how it is initiated.
I'm working in a test environment on vmware, and I have the app (which uses
sql server) installed on the same server as reporting services, and the only
clue I have is that in the TimeDataRetrieval field in the ExecutionLog
table, the value is 1000 times higher (e.g. 61315 vs 53) when the stored
procedure is initiated from the app as opposed to query analyzer. As the
Status field holds the value of rsSuccess, perhaps some timeout on the
stream.write() function is occuring' Not sure, which is why I thought I'd
try another approach..
My rss file is as follows:
Public Sub Main()
Dim format as string = "PDF"
Dim fileName as String = "C:\Reportd\yyyy.pdf"
Dim reportPath as String = "/CNSOfficeSystem/Quote"
' 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
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
' Report Parameters
Dim parameters(2) As ParameterValue
parameters(0) = New ParameterValue()
parameters(0).Name = "QuoteFolderID"
parameters(0).Value = QFID
' parameters(0).Value = "VMBP01$VMBP01$00000242"
parameters(1) = New ParameterValue()
parameters(1).Name = "CustomerID"
parameters(1).Value = CUID
' parameters(1).Value = "12809"
parameters(2) = New ParameterValue()
parameters(2).Name = "ContactID"
parameters(2).Value = COID
' parameters(2).Value = "245"
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
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:ePkHhBdmEHA.3336@.TK2MSFTNGP10.phx.gbl...
> What user security context is used when run from the app? Try (as a test)
> setting Everyone Full Control to the directory where the file is being
> written.
> Jeff
> "Greg Clark" <gclarkmail@.yahoo.com> wrote in message
> news:#9IMhzamEHA.648@.tk2msftngp13.phx.gbl...
> > Hi,
> > I've seen one or two postings on this, and in particular 2 approaches:
> >
> > 1) running rs.exe from a stored procedure in conjunction with
xp_cmdshell.
> > I have successfully managed to create and test a stored procedure that
> > renders a report to a file share using this approach - but for some
reason
> > it doesn't work when integrated into the enterprise app I need it too
work
> > with. When the sp is initiated from the app, the report appears to be
> > generated successfully (according to the entries in the ExecutionLog
table
> > in the ReportServer database), but isn't being written to file on the
> server
> > (although it did in testing). No return errors, nothing in the
EventLog,
> > nothing, nothing, nothing. Even when rs.exe is run with the -t
parameter.
> > Very frustrating.
> >
> > 2) So, if I can do this quickly, I thought I'd look at a second approach
> i.e
> > accessing the web service directly from the stored procedure via the
> > sp_OACreate stored procedure. From a posting I've seen from Teo Lachev,
> he
> > indicates that the object to reference is MSXML2.ServerXMLHttp, with an
> > example provided. However what isn't clear to me is how this can be
used
> to
> > render and write the report to a file share, in a similar manner to the
> .rss
> > file I used in conjunction with point 1 above. Is this approach
possible?
> > or can the web service only be used to create an on-demand report back
to
> a
> > browser?
> >
> > Anyone with some examples/tips on this?
> >
> > Thanks
> > Greg
> >
> >
>