msdb..sp_send_dbmail does not work with @query only with @body

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 ServiceMSSQLSERVER] to db_owner and [DatabaseMailUserRole]

Same as above with [NT SERVICESQLSERVERAGENT]

asked Jul 23 '18 at 22:03

MotKohn

1115

bumped to the homepage by Community♦ 7 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

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

add a comment |

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 ServiceMSSQLSERVER] to db_owner and [DatabaseMailUserRole]

Same as above with [NT SERVICESQLSERVERAGENT]

asked Jul 23 '18 at 22:03

MotKohn

1115

bumped to the homepage by Community♦ 7 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

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

add a comment |

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 ServiceMSSQLSERVER] to db_owner and [DatabaseMailUserRole]

Same as above with [NT SERVICESQLSERVERAGENT]

asked Jul 23 '18 at 22:03

MotKohn

1115

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 ServiceMSSQLSERVER] to db_owner and [DatabaseMailUserRole]

Same as above with [NT SERVICESQLSERVERAGENT]

sql-server database-mail

asked Jul 23 '18 at 22:03

MotKohn

1115

asked Jul 23 '18 at 22:03

MotKohn

1115

asked Jul 23 '18 at 22:03

MotKohn

1115

asked Jul 23 '18 at 22:03

MotKohn

1115

asked Jul 23 '18 at 22:03

MotKohn

1115

bumped to the homepage by Community♦ 7 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

bumped to the homepage by Community♦ 7 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

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

add a comment |

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

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

add a comment |

2 Answers
2

active

oldest

votes

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'

answered Jul 23 '18 at 22:27

Goforebroke

1537

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

add a comment |

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' ;

answered Jul 24 '18 at 7:02

CR241

785319

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f213011%2fmsdb-sp-send-dbmail-does-not-work-with-query-only-with-body%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

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'

answered Jul 23 '18 at 22:27

Goforebroke

1537

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

add a comment |

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'

answered Jul 23 '18 at 22:27

Goforebroke

1537

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

add a comment |

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'

answered Jul 23 '18 at 22:27

Goforebroke

1537

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'

answered Jul 23 '18 at 22:27

Goforebroke

1537

answered Jul 23 '18 at 22:27

Goforebroke

1537

answered Jul 23 '18 at 22:27

Goforebroke

1537

answered Jul 23 '18 at 22:27

Goforebroke

1537

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

add a comment |

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

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

add a comment |

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' ;

answered Jul 24 '18 at 7:02

CR241

785319

add a comment |

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' ;

answered Jul 24 '18 at 7:02

CR241

785319

add a comment |

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' ;

answered Jul 24 '18 at 7:02

CR241

785319

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' ;

answered Jul 24 '18 at 7:02

CR241

785319

answered Jul 24 '18 at 7:02

CR241

785319

answered Jul 24 '18 at 7:02

CR241

785319

answered Jul 24 '18 at 7:02

CR241

785319

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Database Administrators Stack Exchange!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Xrhrft