SQLcmd Error – Failed to Initialize sqlcmd Library

sql servert-sql

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

Old T-SQL:

declare @indexQuery nvarchar(1000) 

select @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='someone@somewhere.co.uk', @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:

select @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?

Best Answer

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!

Related Question