0

I am using sql server 2008 but i just want create reports usin sql statements just sql without any other application like Visual studio is posible??

2
Contributors
1
Reply
4
Views
6 Years
Discussion Span
Last Post by buddylee17
0

I've created (and automated) many html email based reports using just T-SQL and Database Mail. I like using For XML PATH to turn a result set into an html formatted table. Try the following:

DECLARE @Table TABLE (
Column1 VARCHAR(9) NULL,
Column2 VARCHAR(9) NULL
)
INSERT @Table
SELECT 'Col1Value', 'Col2Value'

DECLARE @HTML VARCHAR(MAX)
DECLARE @CrLf CHAR(2) 
SET @CrLf = CHAR(13) + CHAR(10)

SET @HTML = '
<h1>Here is your report!</h1>
<table>
<tr><th>Column1</th><th>Column2</th></tr>' + @CrLf +
    CAST ( ( SELECT td = Column1, '',td = Column2,''
			   FROM @Table
              FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +
+ @CrLf + '</table>'

Print @HTML

This will print an html formatted table.

You can then use database mail to send the report to the recipients:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'insert your db mail profile name here', --you have to set this up before you run
    @recipients = 'insert a recipient email address here',
    @body = @HTML,
    @subject = 'Here is your report',
    @body_format ='HTML';

Edited by buddylee17: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.