msdb.dbo.sp_send_dbmail Proc Will Not Send Results

[cghoga]

Hello everyone,

I have a question related to emailing in SQL Server.

I am using SQL 2005.

I am using msdb.dbo.sp_send_dbmail.

I have a script that works beautifully for tables that already exist, but I recently got permissions on the db to create tables, and when I created a test table and then tried to enail the query results using this table SQL says the mailed queued, but the email never goes out.

Here is the code I am using. This is a simple example template taken from the MS website.

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =
N'<H1>TEST</H1>' +
N'<table border="1">' +
N'<tr>TEST</tr>' +
CAST ( ( SELECT test
FROM CORPDB07.OptimalHealthReporting.dbo.test
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'corpdb07',
@recipients='john.doe@acme.com',
@subject = 'TEST',
@body = @tableHTML,
@body_format = 'HTML' ;

Jon Doe is an example for this post.

Thanks for your help!

 

[chigley]

I know this is daft, but is the owner of the new tables different to the owner of the old tables? What is the owner of the old tables, and what is the new owner?

If you replace the @body with something like TESTING 123 it works right?

And finally, how much data is there in the new table as compared with the old table?

Charlie Benger-Stevenson
Hart Hill IT Ltd

http://www.harthill-it.co.uk

 

[cghoga]

Thanks Charlie,

Yes, the owners are different. I have recently become the administrator of the db and the tables that are working ok were created by someone else.

There is one record in the test table I created.

Thanks for your help.