Msg 8145, Level 16, State 1, Procedure sp_send_dbmail, Line 0

I came accross the existance of Key Lookups in the plan cache of SQL Server. Most of the time it’s better to change existing indexes or add new ones in order to do an index only search.

But I did not only want to do this investigative work manually but automatically.

So I created a SQL Server Agent Job to execute Kendal van Dykes script Find Key Lookups in Cached Plans v1.00 (2010-07-27) to tell me if any Key Lookups exist in the Plan Cache.

It executed with success, but as most of our objects where we have stored the business logic in are Stored Procedures that have very long comments before the real TSQL starts, the CSV-file with the result of my query just contained the first 256 characters of the Stored Procedure text.

So i had a look at the parameters for sp_send_dbmail and found that

[ @query_no_truncate= ] query_no_truncate

Specifies whether to execute the query with the option that avoids truncation of large variable length data types (varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, and user-defined data types). When set, query results do not include column headers. The query_no_truncate value is of type bit. When the value is 0 or not specified, columns in the query truncate to 256 characters. When the value is 1, columns in the query are not truncated. This parameter defaults to 0.

would fit my needs.

But as i added the parameter to my SQL Server Agents Job-step, i got the following error:

Msg 22050, Level 16, State 1, Line 0
Failed to execute the query because the @query_result_no_append and @query_no_truncate options are mutually exclusive.

So i had a closer look at the parameter “@query_result_no_append”.

Problem is, that the parameter “@query_result_no_append” does not exist in SQL Server, (there are only 3 hits if you google it).
You have to omit the parameter “@query_result_no_padding”, that is mutually exclusive with the use of “@query_no_truncate”.

That’s it.

This entry was posted in SQL Server and tagged . Bookmark the permalink.

2 Responses to Msg 8145, Level 16, State 1, Procedure sp_send_dbmail, Line 0

  1. Pawian says:

    Thank you !!! For posting this article šŸ™‚ Really helped šŸ™‚

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s