4

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?

2
  • 2
    Where (in which database) does the IndexOptimize stored procedure reside? 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
    Jun 17, 2015 at 9:15
  • I had exactly the same message. -2147467259. For anyone else struggling with the same - I suggest to focus on the query itself; that is the query passed to @Query parameter. In my case I had a "CONVERT(CHAR(5), cntr_value)". But, yesterday cntr_value length grew longer than 5 characters and the job started failing. CONVERT(CHAR(15) fixed the issue.
    – MojoDBA
    May 20, 2023 at 10:13

5 Answers 5

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!

1

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.

1

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

1

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.

0
0

In my case, I was logged into SSMS with Windows Authentication... reading your comments I realized that. After loged with the sa user, it worked fine. Thanks!

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.