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

Question

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

neotokyo

SSCertifiable

Points: 7239
More actions
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?
_________________________

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

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

David.Poole SSC Guru Points: 76035 More actions · Answer 1

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

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>

<%

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

neotokyo SSCertifiable Points: 7239 More actions · Answer 3

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?

_________________________

neotokyo SSCertifiable Points: 7239 More actions · Answer 4

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.

_________________________