I have some files stored in a SQL Server 2005 table in a varbinary(max) column. I need to email these files to users on a schedule, and though I can write a c# service to extract and send the email sp_send_dbmail would seem to be perfect for my purpose. The code I'm using looks like this:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Mail Profile',
@recipients = 'emailaddress',
@query = 'set nocount on; select CAST(content AS varchar(MAX)) from files where fileid=''xxx''' ,
@subject = 'Random File',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'random file.pdf',
@query_result_no_padding = 1,
@query_result_header = 0,
@query_no_truncate = 1;
When I use the query directly I get the text representation of the PDF file out, but when I use the code above I get a truncated version of the file (always 792 bytes). This would seem to suggest that there is a setting somewhere that controls this, but although I can find settings that control document size they are all way bigger than the file I'm trying to mail!
Anyone seen this before, or have any ideas where to look?