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!)

Michael Fredrickson · Accepted Answer · 2012-04-06 16:46:50Z

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 6, 2012 at 16:46

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user918967 Over a year ago

Hi Mike, Where do I put the :SETVAR SQLCMDHEADERS -1 ? In the SQL-Server interface ?

Michael Fredrickson Over a year ago

@user918967 Put it at the top of your .sql file... Examples here

user918967 Over a year ago

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 ?

Michael Fredrickson Over a year ago

@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.

user918967 Over a year ago

Oh man one last question. The ${indSubject) ? Do I use that in the SET statement or the DECLARE statement ?

|

user918967 · Accepted Answer · 2012-04-06 19:06:58Z

1

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)

answered Apr 6, 2012 at 19:06

user918967

2,1874 gold badges31 silver badges45 bronze badges

1 Comment

Michael Fredrickson Over a year ago

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...

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related