May 6, 2014 at 8:43 am
DECLARE @tab char(1) = CHAR(9)
DECLARE @Localquery VARCHAR(MAX)
SET @Localquery = 'SELECT SOME STUFF'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'xxxx.yyyyyyy@zzzzz.qqqq',
@query = @Localquery,
@subject = 'Pending IBS Updates',
@attach_query_result_as_file = '1',
@query_attachment_filename='filename.csv',
@query_result_separator=@tab,
@query_result_no_padding=1;
Error formatting query, probably invalid parameters
How else can I pass a dynamic query in to the email proc??
Any help would be appreciated
Thanks
May 6, 2014 at 9:16 am
Just to add when I do exec(@LOCALQUERY) it runs as expected - no errors
May 6, 2014 at 9:57 am
The syntax you've posted works with a simple select query, so I think you'll need to post an example of something that actually produces the error...
May 6, 2014 at 10:14 am
The first thing I"d be concerned with is if you are executing the query in the right database. There is this parameter (from BOL):
[ @execute_query_database= ] 'execute_query_database'
Is the database context within which the stored procedure runs the query. The parameter is of type sysname, with a default of the current database. This parameter is only applicable if @query is specified.
That you should supply or use 3 part naming for all objects in your query to make sure you are executing your query in the right database context.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 9, 2014 at 2:24 am
I have found the answer I was using a temp table in the dynamic query and the email procedure couldn't see it. Upon changing it to a global temp table it was made visible and now it works.
Thanks
May 9, 2014 at 11:05 am
here is an example how to send an email and the result to be formated in HTML
create procedure stp_check_smth_and_send_email
as
declare
@table nvarchar(max)
,@cnt int
,@durration int
--format email that will be send if the duration will be bigger than 40
SET @table =N'<html>' +
N'<H1>title of email</H1>' +
N'<H2 style="background-color:red;">mesage tha we want to send</H2>' +
N'<table border=1>' +
N'<tr>' +
N'<th>col 1</th>' +
N'<th>col 2</th>' +
N'<th>col 3</th>' +
N'<th>col4</th>' +
N'<th>col5</th>' +
'</tr>' +
CAST (
(
SELECT
td = column1,'',
td = column2,'',
td = column3,'',
td = column4,'',
td = column5
FROM table_name
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)
) +
N'</table>
</html>' ;
print @table
-- set @table = replace (@table,'<H2></H2>','<td bgcolor = #ff022a>Please check!</td>') --red
EXEC msdb.dbo.sp_send_dbmail
@profile_name='profile_name from SQL',
@recipients='antonela.dan@yahoo.com',
@subject = 'tutorialesql.com',
@body = @table,
@body_format = 'HTML';
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply