Returning TSQL Results straight to file.

Hi All
I'm creating a stored procedure to pull a report every morning for my
manager. The thing is she just wants to double click and open a file. I
want to run a schedule every morning before she gets in, I want the
stored procedure to write it directly to the file. Is this possible or
is there another way to do it?
Thanks
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!
You could possibly do something with xp_cmdshell, but it probably wouldn't
be a great idea (for all sorts of reasons relating to security,
maintainability, etc.)
How are you currently calling the SProc - presumably there's some sort of
client application? Could you adapt that?
If not, your best bet might be to create a simple client app (even just a
simple VB Script app) that connects to the database, runs the SProc, and
writes the results to a file.
Here's a simple example that executes a SPROC called GetReport (which
returns a FOR XML query):
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Const adCmdStoredProc = 4
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=(local);DATABASE=northwind;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command
cmd.CommandText = "GetReport"
cmd.CommandType = adCmdStoredProc
'Specify the root tag
cmd.Properties("xml root") = "ReportData"
'Execute the command returning the data to the DOM
cmd.Execute, , adExecuteStream
'Save the report
xmlDoc.Save "C:\Report.xml"
MsgBox "Report Saved!"
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Da Olive" <doliver@.mailbox.co.za> wrote in message
news:eTM2xElEEHA.3748@.TK2MSFTNGP11.phx.gbl...
> Hi All
> I'm creating a stored procedure to pull a report every morning for my
> manager. The thing is she just wants to double click and open a file. I
> want to run a schedule every morning before she gets in, I want the
> stored procedure to write it directly to the file. Is this possible or
> is there another way to do it?
> Thanks
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
|||"Da Olive" <doliver@.mailbox.co.za> wrote in message
news:eTM2xElEEHA.3748@.TK2MSFTNGP11.phx.gbl...
> Hi All
> I'm creating a stored procedure to pull a report every morning for my
> manager. The thing is she just wants to double click and open a file. I
> want to run a schedule every morning before she gets in, I want the
> stored procedure to write it directly to the file. Is this possible or
> is there another way to do it?
Write a DTS script and schedule it to run every morning:
http://www.sqlxml.org/faqs.aspx?faq=10
Bryant