1

I have set a databasemail profile on SQL2014, created a job, run it and the job succeed but I don't receive any email with my attachment.

Here is my query:

declare @clientname as varchar(50)
set @clientname = 'ABCD'
declare @stagequery as varchar(max)
set @stagequery= 'select users.name, Organizations.Name from users
inner join Organizations on users.OrganizationId = organizations.OrganizationId
where Organizations.name = ' + @clientname

exec msdb.dbo.sp_send_dbmail
@recipients = 'name.surname@mydomain.com'
,@body = 'test'
,@body_format = 'HTML'
,@subject = 'test result'
,@query = @stagequery
,@attach_query_result_as_file = 1
,@query_result_header = 1
,@query_attachment_filename   = 'Results.csv'
,@query_result_separator      = ';'
,@query_result_no_padding     = 1
,@exclude_query_output        = 1
,@append_query_error          = 0
,@query_no_truncate = 0
,@execute_query_database = 'mydatabase'

I have tried to run the job manually - succeeded but no email;

Ruli
  • 2,592
  • 12
  • 30
  • 40
  • Have you checked `dbo.sysmail_faileditems` and `dbo.sysmail_log` in `msdb` at all? – Thom A Feb 13 '23 at 12:36
  • hi @Larnu, there is nothing from today on those two tables – Trying to be DBA Feb 13 '23 at 12:42
  • 1
    Then no emails have been even attempted to be sent today, at all, if `dbo.sysmail_log` is empty. I would suggest that the job was not successful as the call to `sp_send_dbmail` didn't even occur. – Thom A Feb 13 '23 at 12:44
  • Are you able to successfully send a test e-mail via `SSMS` > `Your Server` > `Management` > `Database Mail` > `right-click` > `Send Test E-mail...`? Until you can successfully send a test e-mail calls to `msdb.dbo.sp_send_dbmail` will not succeed either. – AlwaysLearning Feb 13 '23 at 21:42
  • Likely your SQL query is causing an error since you're attempting to use SQL Injection to append the client name to the end of your query. Have you tried `... + quotename(@clientname, char(39))` instead of `... + @clientname`? – AlwaysLearning Feb 13 '23 at 21:45
  • Hi @AlwaysLearning, the last suggestion worked. Thank you so much – Trying to be DBA Feb 14 '23 at 12:49

0 Answers0