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?
IndexOptimize
stored procedure reside? You should remember that sincesp_send_dbmail
is inmsdb
, the query you supply to it as a parameter will be executed in the context ofmsdb
. So, your query will probably need either toUSE anotherdatabase
(the one that containsIndexOptimize
) before calling the procedure or to qualify the procedure name with the database name (as in'anotherdatabase.dbo.IndexOptimize ''MyDB'''
).