Database mail in HTML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Database mail in HTML

Author

Topic

neo302
Starting Member

30 Posts

Posted - 2006-11-28 : 10:57:53

Hey,

When I run the following:
Set @tableHTML = N'<table border="1"><tr><th>AcctID</th><th>UserID</th></tr>' +
Cast ((Select td=AcctID,'',td=UserID,'' from tempdb.dbo.dbmailemail for xml path('tr'),
Type) AS NVARCHAR(MAX) ) + N'</table>'

EXECUTE msdb.dbo.sp_send_dbmail @Recipients = @iRecipient, @Body = @tablehtml, @profile_name = 'Admin', @body_format = 'HTML',
@Subject = @iSubject

---------------------------------------------------------
There is no problem. I have an SP that dynamically generates the statement. i.e.-@tablehtml = @Tablehtml + 'Type) AS NVARCHAR(MAX) ) + N'</table>'

When it's generated dynamically and run within an SP, it doesn't work. If I output the generated statement and set it like at the very top, it works. How can I force the DB to interpret it and run it properly?

Thanks!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-28 : 11:22:43

You've given the code that works, but not the code that doesn't work. Post the broken code from the SP and we might be able to help you.

neo302
Starting Member

30 Posts

Posted - 2006-11-28 : 13:06:04

quote:
Originally posted by snSQL

You've given the code that works, but not the code that doesn't work. Post the broken code from the SP and we might be able to help you.

Here is a stripped down version of the sp that doesn't work if the @tablehtml is auto generated. If you take the output of the print statement and hard code it for the @tablehtml it works.

ALTER Procedure [dbo].[p_Notify_Admin]
@iTran int,
@iDebug int,
@iProcess varchar(50),
@iRecipient varchar(200)='',
@iSubject varchar(200)=''
As
BEGIN
DECLARE @Error int,
@ErrMsg varchar(85),
@CNAME VARCHAR(30),
@Msg varchar(255),
@Process varchar(50),
@Time datetime,
@Recipient varchar(50),
@Subject varchar(50),
@tableHTML NVARCHAR(MAX),
@QUERYRESULTS NVARCHAR(MAX)
SET NOCOUNT ON
IF @iTran = 1
BEGIN TRANSACTION

SELECT @TABLEHTML = 'N''<table border="1"><tr>'

DECLARE DATA CURSOR
FOR SELECT C.NAME FROM TEMPDB.DBO.SYSCOLUMNS C, TEMPDB.DBO.SYSOBJECTS S
WHERE C.ID = S.ID
AND S.NAME = 'DBMAILEMAIL'
OPEN DATA
FETCH NEXT FROM DATA INTO @CNAME
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SELECT @TABLEHTML = @TableHTML + '<th>' + @CName + '</th>'
FETCH NEXT FROM DATA INTO @CNAME
END
DEALLOCATE DATA

SELECT @TABLEHTML = @TableHTML + '</tr>'' + Cast ((Select '

DECLARE DATA CURSOR
FOR SELECT C.NAME FROM TEMPDB.DBO.SYSCOLUMNS C, TEMPDB.DBO.SYSOBJECTS S
WHERE C.ID = S.ID
AND S.NAME = 'DBMAILEMAIL'
OPEN DATA
FETCH NEXT FROM DATA INTO @CNAME
WHILE (@@FETCH_STATUS <> -1)
BEGIN

SELECT @TABLEHTML = @TableHTML + 'td=' + @CName + ','''','

FETCH NEXT FROM DATA INTO @CNAME
END
DEALLOCATE DATA

Select @TableHTML = left(@tablehtml, len(@tablehtml) - 1)

SET @TABLEHTML = @TableHTML + ' from tempdb.dbo.DBMAILEMAIL for xml path(''tr''), Type) AS NVARCHAR(MAX) ) + N''</table>'''
-- Note how the @tablehtml statement is generated from the above code.
-- See the print below. If you take the output of the print and set it in the statement above instead of it being
-- generated, it works.
print @tablehtml

EXECUTE msdb.dbo.sp_send_dbmail @Recipients = @iRecipient, @Body = @tablehtml, @profile_name = 'Admin', @body_format = 'HTML',
@Subject = @iSubject

IF @iTran = 1
COMMIT TRANSACTION
RETURN 0
END

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-28 : 13:19:52

You're doing some weird things with the unicode syntax, this

SELECT @TABLEHTML = 'N''<table border="1"><tr>'

should be

SELECT @TABLEHTML = N'<table border="1"><tr>'

and you must put the uppercase N before all the strings to make them unicode, so this

SELECT @TABLEHTML = @TableHTML + '<th>' + @CName + '</th>'

should be

SELECT @TABLEHTML = @TableHTML + N'<th>' + @CName + N'</th>'

neo302
Starting Member

30 Posts

Posted - 2006-11-28 : 14:35:39

The output statement would look something like this: N'<table border="1"><tr><th>AcctID</th><th>UserID</th></tr>' + Cast ((Select td=AcctID,'',td=UserID,'' from tempdb.dbo.dbmailemail for xml path('tr'), Type) AS NVARCHAR(MAX) ) + N'</table>'

The problem is the: + Cast ((Select td=AcctID,'',td=UserID,'' from tempdb.dbo.dbmailemail for xml path('tr'), Type) AS NVARCHAR(MAX) ) + N'

is not being executed. if you set @tablehtml = N'<table border="1"><tr><th>AcctID</th><th>UserID</th></tr>' + Cast ((Select td=AcctID,'',td=UserID,'' from tempdb.dbo.dbmailemail for xml path('tr'), Type) AS NVARCHAR(MAX) ) + N'</table>'

it works right. The output is correct. Otherwise, it just shows the actual sql statement in the email.

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-28 : 15:27:21

I can't test this and create the code for you on 2005 right now, but I'm pretty sure your problem is that you need to run that line dynamically with sp_executesql inside your proc first. You'll need to use an output parameter for @tablehtml as described at
http://support.microsoft.com/default.aspx/kb/262499

neo302
Starting Member

30 Posts

Posted - 2006-11-29 : 09:45:48

quote:
Originally posted by snSQL

I can't test this and create the code for you on 2005 right now, but I'm pretty sure your problem is that you need to run that line dynamically with sp_executesql inside your proc first. You'll need to use an output parameter for @tablehtml as described at
http://support.microsoft.com/default.aspx/kb/262499

Thanks for your input.

Since the statement has to have multiple = in it, I can't set the query = to the variable that would be run by the sp_executesql.

i.e.-SET @SQLString = N'SELECT @LastlnameOUT = td=AcctID,'',td=UserID,'' from tempdb.dbo.dbmailemail for xml path('tr')'

Still can't get it to work.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-29 : 11:37:31

You want to run this whole SET in sp_executesql, it's just a sungle SET that grabs the entire chunk of XML that you want to be in the @tablehtml variable before you call sp_send_dbmail

Set @tableHTML = N'<table border="1"><tr><th>AcctID</th><th>UserID</th></tr>' + 
Cast ((Select td=AcctID,'',td=UserID,'' from tempdb.dbo.dbmailemail for xml path('tr'), 
Type) AS NVARCHAR(MAX) ) + N'</table>'

neo302
Starting Member

30 Posts

Posted - 2006-11-29 : 11:54:25

quote:
Originally posted by snSQL

You want to run this whole SET in sp_executesql, it's just a sungle SET that grabs the entire chunk of XML that you want to be in the @tablehtml variable before you call sp_send_dbmail
Set @tableHTML = N'<table border="1"><tr><th>AcctID</th><th>UserID</th></tr>' +
Cast ((Select td=AcctID,'',td=UserID,'' from tempdb.dbo.dbmailemail for xml path('tr'),
Type) AS NVARCHAR(MAX) ) + N'</table>'

It doesn't like the html:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '<'.

neo302
Starting Member

30 Posts

Posted - 2006-11-29 : 12:27:04

quote:
Originally posted by snSQL

You want to run this whole SET in sp_executesql, it's just a sungle SET that grabs the entire chunk of XML that you want to be in the @tablehtml variable before you call sp_send_dbmail
Set @tableHTML = N'<table border="1"><tr><th>AcctID</th><th>UserID</th></tr>' +
Cast ((Select td=AcctID,'',td=UserID,'' from tempdb.dbo.dbmailemail for xml path('tr'),
Type) AS NVARCHAR(MAX) ) + N'</table>'

I'll be able to get it to work now with implementing something similar to the following:

Declare @XML XML
Declare @XML2 nvarchar(max)

SET @XML = (Select td=AcctID,'',td=UserID,'' from tempdb.dbo.dbmailemail for xml path('tr'))

Select @xml2 = convert(nvarchar(max), @XML )
select @xml2

Thanks for your input!

neo302
Starting Member

30 Posts

Posted - 2006-11-29 : 13:24:53

quote:
Originally posted by snSQL

You want to run this whole SET in sp_executesql, it's just a sungle SET that grabs the entire chunk of XML that you want to be in the @tablehtml variable before you call sp_send_dbmail
Set @tableHTML = N'<table border="1"><tr><th>AcctID</th><th>UserID</th></tr>' +
Cast ((Select td=AcctID,'',td=UserID,'' from tempdb.dbo.dbmailemail for xml path('tr'),
Type) AS NVARCHAR(MAX) ) + N'</table>'

Actually, I need to execute a dynamically generated XML statement. How is that done?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-29 : 13:39:38

That's what I told you before - you need to execute it with sp_executesql.

neo302
Starting Member

30 Posts

Posted - 2006-11-29 : 13:59:29

quote:
Originally posted by snSQL

That's what I told you before - you need to execute it with sp_executesql.

Dam*. Finally:
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @xml2 xml;

SET @SQLString = N'set @xml = (Select td=AcctID,'',td=UserID,'' from tempdb.dbo.dbmailemail for xml path(''tr''))';
SET @ParmDefinition = N'@xml xml output';

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@xml = @xml2 OUTPUT;

SELECT @xml2

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources