Error when trying to use @query parameter with sp_send_dbmail

Question

Error when trying to use @query parameter with sp_send_dbmail

lmacdonald

Hall of Fame

Points: 3763
More actions
October 20, 2015 at 9:34 am

#321564

Hi,
I don't understand why I am getting an error when I try to run the following statement. I am a SysAdmin and so is the service account the agent runs under. I have tried selecting another table and column and splat and I got the same error.
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'DBA TEAM',
@subject = 'DBCC Results',
@recipients = 'lmacdonald@mywebgrocer.com',
@query = 'SELECT MessageText FROM MyWebGrocerUtility.dbo.dbcc_history',
@body_format = 'HTML';
Msg 22050, Level 16, State 1, Line 0
Failed to initialize sqlcmd library with error number -2147467259.
The actual query I am trying to run is below. However I have made it much simpler hoping to slowly to work my way towards it to pin point the issue but it seems any select statement fails.
SELECT TimeStamp, DB_NAME(dbid) + ' ' + MessageText FROM MyWebGrocerUtility.dbo.dbcc_history WHERE error = 8989 and timestamp >= dateadd(dd,-3,getdate())
Thanks for any help, it's getting me very frustrated.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

what's the datatype for MessageText? if it's TEXT and not varchar(max) or a varchar(8000) or less, i'd think you might get an error under the covers for "The data types varchar and text are incompatible in the add operator.", which might be supressed with the error you currently see.

also, does your table have a column dbid what is DB_NAME(dbid) going to return?

since you are using a three part call(MyWebGrocerUtility.dbo.dbcc_history), why not hardcode it to MyWebGrocerUtility?

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!

lmacdonald Hall of Fame Points: 3763 More actions · Answer 2

Thanks for the response. MessageText is nvarchar(2048) and dbid is int so DB_NAME(dbid) returns the name of the database. The statement runs fine on it's own. Even if I put in @query = 'select 1' I get the same error, but it works with an empty string. If I run the same code on another server it works.

I'm not sure what you mean by hardcode it to MyWebGrocerUtility. However because the send_dbmail procedure executes in msdb we put in the full object path otherwise we have found that it can't find the references.

RPSQL Hall of Fame Points: 3229 More actions · Answer 3

Is your account (account through which you are running this query) has access?

Check this out.

http://harshbiplus.blogspot.in/2015/01/failed-to-initialize-sqlcmd-library.html..

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 4

EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'DBA TEAM',
@subject = 'DBCC Results',
@recipients = 'lmacdonald@mywebgrocer.com',
@query = 'SELECT MessageText FROM MyWebGrocerUtility.dbo.dbcc_history',
@body_format = 'HTML';
Msg 22050, Level 16, State 1, Line 0
Failed to initialize sqlcmd library with error number -2147467259.

The account that the task is running needs to be a member of MSDB. And also granted it rights on the Agent roles, and DBMailUser role, as well as Read, Insert, Connect, Execute, etc.

It will also need permissions on the database that you’re trying to connect to query.

lmacdonald Hall of Fame Points: 3763 More actions · Answer 5

The account being a SysAdmin doesn't cover all those permissions?

I was able to get it to work using the @body tag instead of the @query. I created it in HTML format.

JakDBA SSCommitted Points: 1610 More actions · Answer 6

Refer to the link .. might be helpful understanding the @query parameter and alternative.

http://www.sqlservercentral.com/Forums/Topic1004043-338-1.aspx

@JayMunnangi