0

I'm trying to execute the following:

msdb.dbo.sp_send_dbmail
--@body='Hi from sql server in a sproc'
@query='select 1'
,@recipients='mkohn@***.com'
,@subject = 'Test'

When I uncomment @body line, and comment the query... line it works. With the query it returns:

Msg 22050, Level 16, State 1, Line 0 Failed to initialize sqlcmd library with error number -2147467259.

I have a developer edition on my own workstation and it all works no problem

I tried:

  • Installing .Net 3.5
  • Adding myself to db_owner and [DatabaseMailUserRole]
  • Adding [NT Service\MSSQLSERVER] to db_owner and [DatabaseMailUserRole]
  • Same as above with [NT SERVICE\SQLSERVERAGENT]
  • Are you using sqlcmd to run this query or some other tool ? Have you tried a different tool and does it suffer from the same error ? – Błażej Ciesielski Jul 23 '18 at 22:46
  • @BłażejCiesielski I'm using the built in Database Mail. No external tools – MotKohn Jul 23 '18 at 22:48
  • I'm asking about the software you are using to run the stored procedure against your database. Is it SQLCMD ? SQL Management Studio ? SQL Operations Studio ? Something else ? – Błażej Ciesielski Jul 23 '18 at 23:02
  • @BłażejCiesielski SQL Management Studio – MotKohn Jul 24 '18 at 14:16
  • 1. In Management Studio go to Query menu and try enabling/disabling "SQLCMD Mode" option - i suspect this might be turned on and you might some corrupt instalation of Management Studio that raises errors when SQLCMD mode enabled. 2. Can you try a different tool to execute the procedure and see if it works e.g. SQL Operations Studio or event the windows command line tool SQLCMD ? – Błażej Ciesielski Jul 24 '18 at 18:04
0

Have you created a mail profile and assigned an account to the profile? See the how to by Pinal Dave @ SQL Authority

First create a mail profile, assign the profile a valid account, and enable Database Mail XP's

Once those steps are done and tested, test your script

    msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileName'
    --@body='Hi from sql server in a sproc'
    @query='select 1'
    ,@recipients='mkohn@***.com'
   ,@subject = 'Test'
  • My profile and account works for mail that does not have a @query parameter - so it has nothing to do with my profile and account setup. – MotKohn Jul 23 '18 at 22:33
0

The Database Mail Profile that is used for the SQL server agent need to be a member of DatabaseMailUserRole in the msdb database.

The stored procedure sysmail_add_account_sp is in the msdb database and is owned by the dbo schema. The procedure must be executed with a three-part name if the current database is not msdb.

Permissions: Execute permissions for this procedure default to members of the sysadmin fixed server role.

You should start stored procedure with Execute

Example:

EXECUTE msdb.dbo.sysmail_add_account_sp  
@account_name = 'AdventureWorks Administrator',  
@description = 'Mail account for administrative e-mail.',  
@email_address = 'dba@Adventure-Works.com',  
@display_name = 'AdventureWorks Automated Mailer',  
@mailserver_name = 'smtp.Adventure-Works.com' ;  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.