XP_send mail fail

Question

Post reply

XP_send mail fail

ramyours2003

SSChampion

Points: 12266
More actions
November 19, 2019 at 10:13 am

#3698776

We migrated from sql 2005 to sql 2016 recently
in one of the job we are getting error
Executed as user: ' 'Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed.
SQL agent has sysadmin and database reader role access on msdb
how to solve the issue

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

typically that error is invalid parameters, i think, and might be related to the @query paremeter

since you didn't provide any details or sample code, lets start at the top:

does sp_send_dbmail work sometimes, but not in this case? did you make sure to install the 3.5 .net framework, since it is required?

does your query assume running in the master database, and you did three part names for the query like execute [CustomerDB].dbo.[GetInvoices]?

Are you using a default profile? was it made public?

here's a code example i was hoping to see your equivalent of it.

declare @body1 varchar(4000)
set @body1 = 'Sample Email from SQL2008 ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) + 
' '

EXEC msdb.dbo.sp_send_dbmail 
    @profile_name='stormrage as scripts',
    @recipients='lowell@fake.net',
    @subject = 'SQl 2008 email test',
    @body = @body1,
    @body_format = 'HTML',
    @query = 'SELECT top 3 * from DBA_Utilities.sysobjects where xtype=''U''',
    @query_result_header = 0,
    @exclude_query_output = 1,
    @append_query_error = 1,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'qry.txt',
    @query_result_no_padding = 1

--in body of html:
declare @body1 varchar(4000)
set @body1 = 'Sample Email from SQL2008 ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) + 
'<BR>'

EXEC msdb.dbo.sp_send_dbmail 
    @profile_name='stormrage as scripts',
    @recipients='fake.net',
    @subject = 'SQl 2008 email test',
    @body = @body1,
    @body_format = 'HTML',
    @query = 'SELECT name AS [<BR>name],type_desc,create_date,''<BR>'' As [Filler<BR>] from DBA.sys.objects where type=''U''',
    @query_result_header = 1,
    @exclude_query_output = 1,
    @append_query_error = 1,
    @attach_query_result_as_file = 0,
    @query_result_no_padding = 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!