October 20, 2015 at 9:34 am
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.
October 20, 2015 at 9:54 am
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
October 20, 2015 at 10:19 am
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.
October 30, 2015 at 9:14 am
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..
October 30, 2015 at 10:52 pm
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.
November 2, 2015 at 7:05 am
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.
November 2, 2015 at 8:39 am
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