Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sp_send_dbmail truncates when attaching query results. Expand / Collapse
Author
Message
Posted Sunday, July 6, 2014 8:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 56, Visits: 334
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
Human Interface.
Post #1589664
Posted Sunday, July 6, 2014 9:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:46 AM
Points: 1,558, Visits: 4,297
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'

Post #1589671
Posted Sunday, July 6, 2014 10:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 56, Visits: 334
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
Human Interface.
Post #1589674
Posted Sunday, July 6, 2014 12:23 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:46 AM
Points: 1,558, Visits: 4,297
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.
Post #1589686
Posted Tuesday, July 8, 2014 12:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 56, Visits: 334
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
Human Interface.
Post #1590474
Posted Tuesday, July 8, 2014 1:05 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:46 AM
Points: 1,558, Visits: 4,297
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

Post #1590478
Posted Tuesday, July 8, 2014 1:33 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 56, Visits: 334
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
Human Interface.
Post #1590486
Posted Wednesday, July 9, 2014 9:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 56, Visits: 334
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
Human Interface.
Post #1590823
Posted Sunday, July 13, 2014 12:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 8:43 AM
Points: 56, Visits: 334
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
Human Interface.
Post #1592025
Posted Tuesday, July 15, 2014 2:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:46 AM
Points: 1,558, Visits: 4,297
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)?
Post #1592439
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse