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?

Improve this question
0

8 Answers 8

Reset to default
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.

Improve this answer
2

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.

Improve this 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!

Improve this answer
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.

Improve this answer
0
1

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.

Improve this answer
0

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

Improve this answer
0

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

Improve this answer
0

I too found that using this line of code after the @Recipients line: "@execute_query_database = 'Reporting', " wound up resolving the issue.

Not sure why for other similar queries, this line isn't needed, but as long as the fix works....

[Note: The "Reporting" Database Name is a convention that I use in order to separate any reporting activity and supplemental tables in my Multi-DB environment.]

Improve this answer
1
  • Hi, please read the help. This site expects each answer to add something new. Commented Oct 24 at 9:17

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.