Introduction

My assumption in this blog post is that you know how to use the sp_send_db_mail procedure. You also have DBMail configured with a default email profile, account, port, and SMTP. Your Send Test E-Mail was successful (Fig #1).

Fig #1

You have called sp_send_db_mail in the Agent job step in the past, it used to be working, but you or somebody else made a change, and now you get the “-2147467259” error. The error that tells you absolutely nothing useful.

This blog post is going to explain what it is and how to fix it. TL;DR, disregard the error and check your query.

Problem

The following code examples (see Fig #2) will demonstrate how a relatively simple code can generate a not-simple generic “-2147467259” error.

Fig #2

As you rightfully guessed, the “-2147467259” error tells you absolutely nothing useful (see Fig #3), nothing useful to help you fix it.

Fig #3

Solution

If you paid special attention to the query code, you would quickly notice that both queries would fail to run. If you were to run both in SSMS, you would be greeted with a syntax error (Fig #4).

Fig #4

Obviously, there is no “master.sys.database” and there is no ” master” database. Normally, query execution is going through a full query processing cycle and would be stopped in a parsing process if there is a problem. Unfortunately, sp_send_db_mail is not sophisticated enough to parse/translate/validate @query/@execute_query_database variables, so everything fails in a runtime. Everything fails in a runtime, and it generates a nonsensical error. This generic error will occur even if you use a query that you know is going to fail, like a “divide-by-zero” (Fig #5).

Fig #5

Check your query. Your query is not runnable as-is. The easiest way to fix it is to copy and paste your query into a new query SSMS window. When you are doing it, abandon @execute_query_database and move that database reference to the query itself (example: database.schema.table).

Now run it, and have SSMS (via a database engine query processor) tell you what the problem is. Once it’s fixed, come back with a valid query, and paste it back to your @query variable, so everything can run again (see Fig #6).

Fig #6

The better solution of course is to just don’t use ad-hoc queries in @query variable and use a stored procedure instead. Obviously, my code example was really simplistic (SELECT * FROM master.sys.databases) for a stored procedure, but a stored procedure is virtually always a good idea. A stored procedure can be validated and tested separately, ensuring that any syntax problems are fixed before the runtime. As an extra bonus, a stored procedure is usually better performance-wise due to execution plan reusability.