Re: Returning TSQL Results straight to file.

From: Graeme Malcolm \(Content Master Ltd.\) (graemem_cm_at_hotmail.com)
Date: 03/25/04 

Date: Thu, 25 Mar 2004 11:20:39 -0000

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.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Sort Multi Time Fields
    ... <MS ACCESS MVP> ... When it sorts now, it sorts numerically (ex: ... I do have the report sorting and grouping by date ... Save the query. ...
    (microsoft.public.access.reports)
  • Re: Weird Report Problem - Access 2003
    ... You saved the report with some other RecordSource. ... The field you are sorting on is a calculated field. ... > statement and assign it to the recordsource in the Open event, ... The user can choose up to 3 sorts ...
    (comp.databases.ms-access)
  • Re: [Full-disclosure] Re: kiddie porn warning [was: Fwd: Re: montspace -- child porn (si
    ... This is an example of politicos gone wild. ... I remember that after September 11th 2001 for several years the local FBI office was fielding many phone calls from people who had claimed to have overheard all sorts of diabolical plans while standing in the line at the local post office. ... remote computing services to the public obtains knowledge of facts or circumstances concerning an apparent violation of Federal child pornography statutes designated by 42 U.S.C. 13032, it shall, as soon as reasonably possible, report all such facts or circumstances to the "Cyber Tipline" at the National Center for Missing and Exploited Children Web site, which contains a reporting form for use by providers. ... Charter: http://lists.grok.org.uk/full-disclosure-charter.html ...
    (Full-Disclosure)
  • Re: Weird Report Problem - Access 2003
    ... Sorting Records in a Report at runtime ... The SQL statement combines 2 tables. ... >> a) You saved the report with some other RecordSource. ... The user can choose up to 3 sorts ...
    (comp.databases.ms-access)
  • Re: Weird Report Problem - Access 2003
    ... No, Record Source is blank. ... calculated fields; they're all text. ... When I eliminated the 2nd table from the statement, then the report ... The user can choose up to 3 sorts ...
    (comp.databases.ms-access)
Loading