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

Question

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:

Have SQL Server save this on my machine somewhere with the name of @indSubject.html (e.g. C:\TEMP\HTMLOUTPUT\61-0036.html)
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!)

score 2 · Accepted Answer · answered Apr 06 '12 at 16:46

2

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

answered Apr 06 '12 at 16:46

Michael Fredrickson

36,839
5
92
109

Hi Mike, Where do I put the :SETVAR SQLCMDHEADERS -1 ? In the SQL-Server interface ? – user918967 Apr 06 '12 at 17:33
@user918967 Put it at the top of your .sql file... [Examples here](http://msdn.microsoft.com/en-us/library/ms188714.aspx) – Michael Fredrickson Apr 06 '12 at 17:38
Great, Thanks ! One last question, the output is truncated at 255 characters. The option -y allows me to change it only to 8000 characters. My final HTML page is bigger than that, any ideas ? – user918967 Apr 06 '12 at 17:44
@user918967 What about `-y 0`? If it's still truncated, it could be because of how your data is being selected... [there's a few work-arounds here that could be applied inside of your .sql file](http://stackoverflow.com/questions/952247/sql-server-truncation-and-8192-limitation). – Michael Fredrickson Apr 06 '12 at 17:48
Oh man one last question. The ${indSubject) ? Do I use that in the SET statement or the DECLARE statement ? – user918967 Apr 06 '12 at 18:12
@user918967 You can pass it in from the command line using `-v indSubject="Your Subject Here"` – Michael Fredrickson Apr 06 '12 at 18:17
I must be really dense today. I have tried putting the $(indSubject) in my SQL file in a couple of places and does not access it. Can you post my code snippet above so that it is altered in the correct way ? I really appreciate it ! – user918967 Apr 06 '12 at 18:24

score 1 · Answer 2 · answered Apr 06 '12 at 19:06

For the sake of my sanity (and Michael's) here is the code that seems to work:

    :SETVAR SQLCMDHEADERS -1
    DECLARE @indSubject varchar(7)
    SET @indSubject='$(indSubject)'

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


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

When the command sqlcmd -E -S(Local)\SQLExpress -v indSubject = "61-0022" -dmyDatabase -y 0 -iC:\temp\test4.sql -oC:\temp\out.html is used, then the file out.html looks like this:

    <html><body>61-0022</body></html>
    (1 rows affected)

That looks good... if you want to get rid of that `(1 rows affected)`, I think you can add `SET NOCOUNT ON` to the top of your .sql file to get rid of it... — Michael Fredrickson, Apr 06 '12 at 19:18

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

2 Answers2