Write to Text File
-
I have a routine that currently runs as VB Script. It sends email using the results of a select statement. I've rebuild the email portions use TSql and stored procs. There is one piece I cannot seem to emulate. In the VB code, I log each email address to a text file that has embedded html. The end result is I've built an HTML page that shows who got which email. It gives us a nice snapshot of the history of our emails. I cannot figure out a way to write to a text file from within TSql.
TIA, Thom
-
There is no easy way. One option is to use the file scripting object and the sp_oa~~~ procs. Another is to use the output option of xp_cmdshell to redirect stuff to a file. Another would be DTS, though this is really just a variant of the fso technique, though easier than the sp_oa stuff. I'd suggest you write all your info to a file, then just build your web page in one chunk - or maybe consider creating the page as asp that reads from the table, avoiding the write to file step altogether. One other possible option would be the webtask stuff, gives you the ability to create fairly decent html output from a table/proc.
Andy
-
Duh, why I couldn't think of this I don't know. Thanks for pointing out the obvious. The ASP method of writing a file was really a work around to avoid additional SQL I/O.
quote:
or maybe consider creating the page as asp that reads from the table, avoiding the write to file step altogether. One other possible option would be the webtask stuff, gives you the ability to create fairly decent html output from a table/proc. -
IO isnt necessarily bad. If it was going to be hit a LOT, maybe would be worth building as a static page. Have to weigh the cost of building your solution against the load that would be applied.
Andy
-
Other ways of creating a text file from sql server are osql, bcp, sp_makewebtask.
Usually the easiest is to create a global temp table and then use bcp to output it.
Cursors never.
DTS - only when needed and never to control.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply