Hi Guys,
I am getting below error when i am trying to send the CSV report thru database mail. The same thing executing fine when I ran from SSMS.
Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050).
My query is below :
DECLARE @msg VARCHAR(250);
DECLARE @query varchar(2048);
SET @msg = 'Please refer to the attached spread sheet for the report.';
SET @query='
QUERY;';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseMail'
, @recipients = 'mail ids;'
, @subject = 'User Stats'
, @body= @msg
, @body_format = 'TEXT'
, @query = @query
, @execute_query_database = 'AAAA'
, @attach_query_result_as_file = 1
, @query_result_header = 1
,@query_result_width = 256
,@query_result_separator = 0x09
,@query_result_no_padding = 1
,@query_attachment_filename=' User Stats.CSV';
I tried changing parameters etc but it didn't work . But When I grant db owner permission to SQLserver agent service account It is working fine. My question is without granting any permissions on DB to my agent service account will it work ??? why i am asking this question is without granting any access on DB to my service account , all other reports are working fine (HTML reports ). The only difference is my query is attaching file and sending.