Error when trying to use @query parameter with sp_send_dbmail

  • 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.

  • 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!

  • 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.

  • 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..

  • 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.

  • 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.

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

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

    @JayMunnangi

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

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