SQLcmd error 'Failed to initialize sqlcmd library with error number -2147467259'

Question

I'm trying to update some maintenance plans to make use of Ola Hallengren's IndexOptimize,
but also mail the result of the operation using msdb.dbo.sp_send_dbmail.

Old T-SQL:

declare @indexQuery nvarchar(1000);

set @indexQuery = 'USE [MyDB];
    SELECT i.index_id, i.name, s.avg_fragmentation_in_percent 
    FROM sys.dm_db_index_physical_stats (
      DB_ID(), 
      OBJECT_ID(N''MyTable''),
      DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i
    WHERE s.object_id = i.object_id
      AND s.index_id = i.index_id;';

exec msdb.dbo.sp_send_dbmail
    @profile_name = 'Local Mail Profile'
   ,@recipients = '[email protected]'
   ,@subject = 'Re-index Started'
   ,@query = @indexQuery
   ,@attach_query_result_as_file = 1
   ,@query_attachment_filename = 'Reindex BEFORE.txt'

...and this works perfectly fine.

But if I modify @indexQuery thusly:

set @indexQuery = 'IndexOptimize ''MyDB''';

...to make use of the IndexOptimize procedure, the result is:

Failed to initialize sqlcmd library with error number -2147467259.

I can't seem to find anything related to the error number anywhere to work out what the problem is.
Can anyone please shed any light on it for me?

Answer 1

After hunting around a lot more, I found a few articles discussing security and granting permissions that sent me on a wild goose chase and ultimately made no difference to the outcome.

The script continued to work outside of sp_send_dbmail, but would not work with it.

I don't recall how, but I have subsequently discovered that stored procedures the indexing script allegedly uses had gone AWOL (ones created by the file CommandExecute.sql), even though the indexing script worked fine outside of sp_send_dbmail.

Once I created those stored procedures, everything seems to work as it should!

Answer 2

Please ensure the SQL Server Agent has rights on Database Mail and sp_send_mail option.

I faced this issue and by giving rights to SQL Server Agent for Database Mail and sp_send_mail, it got resolved.

Answer 3

Just sharing my experience... it might be useful for others.

I've tried to change the users running my query and different parameters into the sp_send_dbmail stored procedure but nothing worked.

I had an error in the query I was providing to the sp_send_dbmail stored procedure and it was returning exactly the same error number you folks mentioned here.

So if you, like me, ended up here at this stack question, double check the query you want to attach to in your email.

Answer 4

It is permission issue. Run SQL profiler and you see more detail error. Add to the top of exec sp_send_dbmail: EXECUTE AS LOGIN = N'sa'

You should be ok after that

Answer 5

You should remember that since sp_send_dbmail is in msdb, the query you supply to it as a parameter will be executed in the context of msdb.

So, your query will probably need either to USE anotherdatabase (the one that contains IndexOptimize) before calling the procedure or to qualify the procedure name with the database name (as in 'anotherdatabase.dbo.IndexOptimize ''MyDB'''). - Andriy M

Answer 6

When I choose the master database as the target database and test with query: SELECT @@SERVERNAM, things work fine.

When I used the same query on my target database, I got the failure. Although NT SERVICE\SQLSERVERAGENT has rights on the database it didn't work.

How I resolved it:

1. Create a login for the service account of my SQL Agent.

   CREATE LOGIN [MYDomainName\MyServiceAccount] FROM WINDOWS WITH 
   DEFAULT_DATABASE=[master]
   

   and
2. Give the appropriate rights on the database. (In my case READ).

   USE [MyDatabase]
   CREATE USER [MyDomainName\MyServiceAccount] FOR LOGIN 
   [MyDomainName\MyServiceAccount]
   ALTER ROLE [db_datareader] ADD MEMBER [MyDomainName\MyServiceAccount]
   

This did it for me.

Answer 7

I had the same problem and searching led me here. For the benefit of anyone else finding this, I found the answer in the additional parameters described here: sp_send_dbmail

Specifically: [ @execute_query_database = ] 'execute_query_database'

By specifying the database in which the query should run, I resolved the error.