How do I save the html output of a query to a text file, using a variable?

I have a query that is more complicated, but basically creates an HTML page for me.

DECLARE @indSubject varchar(7)
SET @indSubject = '61-0036'

--  LOTS of query stuff here that makes a page.  For simplicity 
--  just use this:

SELECT '<html><body>' + @indSubject + '</body></html>'

I want to do two things:

  1. Have SQL Server save this on my machine somewhere with the name of @indSubject.html (e.g. C:\TEMP\HTMLOUTPUT\61-0036.html)
  2. Be able to pass different values to the @indSubject (e.g. 91-0003). Something command line-ish ?

Can I do this with BCP? Some other way ?

Please note, this is all my personal stuff and this is not being deployed on a web server so I don't have to really worry to much about security, SQL Injection etc (at least I hope I don't!)

Answers


You can use SQLCMD to accomplish this fairly easily.

If you save your existing query as a .sql file, you can execute this using SQLCMD.

Additionally, SQLCMD will let you specify a variable inside of your .sql file ${indSubject} which you could then provide from the command line.

Finally, using the -o switch, you can output the results directly to a file, and you can suppress the headers on your result set using the following environment setting:

:SETVAR SQLCMDHEADERS -1


Need Your Help

How to use/configure Unity Container IOC in my situation

.net dependency-injection ioc-container

I have some trouble implementing the Unity IOC into my project reading from config file.

Trouble adding a custom object to NSMutableArray inside a block

ios nsmutablearray objective-c-blocks nsobject

I'm sending some custom objects to a server from the app with ASIFormDataRequest inside a dispatch_async block. And when I receive OK from the server I need to put the object in a NSMutableArray. But

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.