Conversations On tech, literature, art and business

SQL Send Email 4000 Char Limit

Use SQL to send emails with query results? Great for reporting purposes and useful with dynamic queries.

I won't go into all the little details, you can Google them as easily as I can. Well, ok, I'll make it easy, they are here.

The one issue I've had with sp_send_dbmail is the character limits for the @Query parameter. I can hear you saying, why are you passing in a query with more than 4000 characters? Well Dorothy, sometimes I need that many JOINs to make it work. But seriously, consider the poor person who comes behind you.

I'm an avid commenter in queries as well as code. Once I found myself removing all the line breaks, extra spaces, and fully qualified column names just to make my dynamic query fit into 4000 characters, I had to step back and re-think my approach.

Instead of passing in a normal select statement I decided to declare a new varchar variable and construct an EXEC statement. Ran a quick test and it worked!

Goodbye 4000 char limit. I can create sub-processes for any dynamic SQL I need.

Example:

Regular Query

Declare @SQL = 'Select top 1 * from myTable'

exec msdb..sp_send_dbmail @profile_name='sa', @recipients=N'jon@home.ca', @subject='TEST', @Query=@SQL

Updated to use Stored Procedure

Declare @SQL = 'EXEC mySproc ' + @myParam

exec msdb..sp_send_dbmail @profile_name='sa', @recipients=@recips, @subject=@Subj,  @Query=@SQL, @execute_query_database = 'myDB',@Body = 'Hey I'm adding a header to the email!'

I added a couple of extra parameters to sp_send_dbmail just for fun. The most import one is @execute_query_database, you need that to run EXEC commands.

Any questions, drop me a comment or @jonreid on Twitter.