sp_send_dbmail truncates when attaching query results.
-
Installed SQL Server 2014 CU1.
While testing sp_send_dbmail I noticed the query results, when attached are cut off or truncated.
Max file size has been 64k -65k.
I set the max file size to 104857600 and set @query_no_truncate = 1.
Is this a new bug?
Thank you.
Francis S. Mazeika
MS SQL DBA
609-707-5207
francis.mazeika@gmail.com -
Quick question, what is the result of the following query in msdb?
😎
select *,CAST(paramvalue AS FLOAT) / POWER(2.0,20) AS MX_IN_MB
FROM msdb.dbo.sysmail_configuration
where paramname = 'MaxFileSize'
-
paramname paramvalue description last_mod_datetime MX_IN_MB
MaxFileSize 1000000 Default maximum file size 2014-06-26 19:21:58.037 0.95367431640625
Thank you.
Francis S. Mazeika
MS SQL DBA
609-707-5207
francis.mazeika@gmail.com -
Not certain what is going on here, the attachment has a schema limitation of varbinary(max) and is consistently handled as such all the way through the process. You could try query msdb.dbo.sysmail_mailattachments
or run the following query if the sending user is different from yours to inspect the actual attachment.
😎
SELECT attachment_id,sa.mailitem_id,
filename,
filesize,
attachment,
sa.last_mod_date,
sa.last_mod_user
FROM msdb.dbo.sysmail_attachments sa
JOIN msdb.dbo.sysmail_mailitems sm ON sa.mailitem_id = sm.mailitem_id
If it is all there, then the truncation is happening somewhere else.
-
I ran the query, the attachement data is truncated at 65k, no attachement size is larger than that.
I run the same query\jobs using sp_send_dbmail in 2008 and there are no issues with email attachments upto and over 1mb.
It may be a bug in SQL 2014 dbmail attachment algorithm.
The data in the attachments is cut off, mid field meaning, half a word is in the column, like it was cut off.
Thank you.
Francis S. Mazeika
MS SQL DBA
609-707-5207
francis.mazeika@gmail.com -
Francis S. Mazeika (7/8/2014)
I ran the query, the attachement data is truncated at 65k, no attachement size is larger than that.I run the same query\jobs using sp_send_dbmail in 2008 and there are no issues with email attachments upto and over 1mb.
It may be a bug in SQL 2014 dbmail attachment algorithm.
The data in the attachments is cut off, mid field meaning, half a word is in the column, like it was cut off.
At least we are getting somewhere, could you post the output of the following query?
😎
select @@version -
Microsoft SQL Server 2014 - 12.0.2370.0 (X64)
Jun 21 2014 15:21:00
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Thank you.
Francis S. Mazeika
MS SQL DBA
609-707-5207
francis.mazeika@gmail.com -
I'm starting to find more web posts about this issue, looks like I am not the only one.
But it does not fix the problem.
Thank you.
Francis S. Mazeika
MS SQL DBA
609-707-5207
francis.mazeika@gmail.com -
I have 12 SQL 2014 instances in 3 different domains (dev, test, stage).
Each SQL instance has 3 distinct email profiles and each domain has its own mail server.
I tested different queries on each instance, all truncate the result set when attaching the query.
Time to open a call with MS.
Thank you.
Francis S. Mazeika
MS SQL DBA
609-707-5207
francis.mazeika@gmail.com -
Francis S. Mazeika (7/13/2014)
I have 12 SQL 2014 instances in 3 different domains (dev, test, stage).Each SQL instance has 3 distinct email profiles and each domain has its own mail server.
I tested different queries on each instance, all truncate the result set when attaching the query.
Time to open a call with MS.
Quick question, what are the versions of XPStar.DLL on those systems (used by xp_sysmail_attachment_load)?
😎
-
I see a connect item has been logged
-
I did some investigation and found the problem, XPStar.dll contains a bug.
File Version 2014.120.2000.8
Product Version 12.0.2000.8
Size 409 KB
As a workaround, and the method I used to prove my findings, was to copy XPStar.dll from my 2012 instance and replace the file on my 2014 instance (after backing up of course). After restarting SQL Server the issue was solved.
By default the 2012 location is:
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
and the 2014 location is:
C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Binn
-
Sean Pearce (7/16/2014)
I did some investigation and found the problem, XPStar.dll contains a bug.File Version 2014.120.2000.8
Product Version 12.0.2000.8
Size 409 KB
As a workaround, and the method I used to prove my findings, was to copy XPStar.dll from my 2012 instance and replace the file on my 2014 instance (after backing up of course). After restarting SQL Server the issue was solved.
By default the 2012 location is:
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
and the 2014 location is:
C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Binn
Good stuff, thanks for this.
XPStar.dll was my main suspect, other changes between the two versions looked irrelevant to the problem (discarding openxml, defaulting return values etc..)
😎
-
file version 2014.120.2000.8
product version 12.0.2000.8
size 409k
Date modified 2/21/2014 5:20am
Thank you.
Francis S. Mazeika
MS SQL DBA
609-707-5207
francis.mazeika@gmail.com -
Francis S. Mazeika (7/22/2014)
file version 2014.120.2000.8product version 12.0.2000.8
size 409k
Date modified 2/21/2014 5:20am
That is the flawed/bugged version.
😎
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply