Post reply

sp & select results to html out put? how is it done?

  • January 23, 2006 at 1:02 pm

    #111215

    i've seen the (comprehensive..) script in sqlservercentral,

    which takes server/database information, and creates an output

    script in HTML.

    question is... what can i use to generate that html output?

    i would like to take sp's like sp_helpdb and selects such as

    select * from sys..., and display the results in HTML, but

    don't know what tool to use to generate the HTML output.

    thoughts?

    _________________________

  • January 23, 2006 at 2:46 pm

    #616537

    Start reading up about the sp_makewebtask and sp_runwebtask.

    In summary you can design a simple text template of what you want with tags where your source query should insert its fields.

    sp_makewebtask matches up the source from your stored proc with the text in your template file and generates a resulting output file.

    Because the template file is plain text you could use it to generate almost any file you want

    • An XML file
    • A CSV file
    • A correctly formatted SMTP message to drop into the SMTP service folder to autosend message
    • A form letter
    • ...etc

    LinkedIn Profile

  • January 23, 2006 at 2:48 pm

    #616539

    Hi,

    depends what you want.

    1. If you want to create HTML code from the query results then you have to do something like that. Example is for Pubs database Employee Table

    Use Pubs

    Set NoCount On

    select '<HTML>' + char(10)+ Char(13)

     + '<HEAD>'  + char(10)+ Char(13)

    Select  FNAME From Employee

    select '</HEAD>' + char(10)+ Char(13)

     +  '</HTML>' + char(10)+ Char(13)

    2. If you want to output your query results in ASP page create a Data Source named Test21 (for example) to Pubs database, change Default Database to Pubs in the Data Source, Enable Integrated Autentication in IIS (if you would like to use Windows authentication for connection string) and create a following file PubsTest.asp:

    <html>

    <head>

    <title>

    Pubs Employee Query Demonstration

    </title>

    </head>

    <body bgcolor=#ffffff>

    <font face='Tahoma, Arial, Helvetica'>

    <%

    set rs = Server.CreateObject("ADODB.Recordset")

    rs.ActiveConnection = "DSN=Test21;"

    rs.Open "select * from employee"

    Do Until rs.EOF

       Response.Write (rs.Fields("FName") & " ")

       Response.Write (rs.Fields("LName") & " ")

       Response.Write (rs.Fields("Emp_id") & "<br>")

       rs.MoveNext

    Loop

    %>

    </font>

    </body>

    </html>

     

    Regards,Yelena Varsha

  • January 23, 2006 at 2:53 pm

    #616540

    hey... cool.

    thats every thing i need to know. i'll be messing with this for a while.

    i'm also looking into sp_makewebtask

    cool stuff.

    thanks bro!!

    but what about sp's? for example getting the

    resulst from sp_helpdb into html?

    _________________________

  • January 24, 2006 at 7:22 am

    #616656

    well.. perhaps i spoke too soon.

    the first batch is a head-ache to mod into a useful format. it can be done, but you better have an extra html editor to help you out.

    not really into asp... so would probably go

    with some thing different.

    thanks for the post though... good information.

    _________________________

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply