question

narendba avatar image
narendba asked

query result to excel or csv issue through dbmail

Hi, I have used the below code to send the query result to csv file but due to excel sheet field limit not exceeded to 256 charectors... the output result truncated. Can you suggest how we can overcome this situation. Script: declare @var1 nvarchar(max) set @var1=' SELECT date_time,spid,blocked,dbname,hostname,status,login_time,last_batch,last_waittype,program_name,dbid,replace(replace(sql_text, char(10),'+''' '''+'), char(13),'+''' '''+') as sql_text FROM nmssuper.dbo.blocking_queries' EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'SQLSERVER_Email_ProfileName' ,@recipients = 'visna05@ca.com' ,@subject = 'Blocking queries on Local' ,@body = 'Attached are the head blocker queries running for more than 1 minute, please check.' ,@body_format = 'TEXT' ,@query_result_no_padding=1 ,@query = @var1 ,@attach_query_result_as_file=1 ,@query_no_truncate =0 ,@query_attachment_filename='blocking_queries.csv' ,@query_result_separator=',' ,@query_result_width =32767 ,@importance = 'High';
tsqlsql server 2008 r2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
It looks like you need to change @query_no_truncate = 0 to use 1 instead. 0 is the default behavior and truncates long columns to 256 characters. From MSDN - [ https://msdn.microsoft.com/en-us/library/ms190307.aspx][1] [ @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. [1]: https://msdn.microsoft.com/en-us/library/ms190307.aspx
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

um... I just did in the previous comment. Do you want me to type 'dbname', 'hostname' and all the rest when you already have the list and just need to add some single quotes? Here is the link to how to use Cast and Convert on MSDN - https://msdn.microsoft.com/en-us/library/ms187928.aspx
1 Like 1 ·
I forgot to appreciate Kenj as my alert scripts working as expected and thanks lot for all your help Ken.
1 Like 1 ·
Since they are mutually exclusive, you have to pick one. If you absolutely must have both, you will need to generate the csv file using SSIS, or something similar, then attach it to an email from there. Just had a quick idea for a workaround. Include another query via UNION ALL that selects the column names as literal string values. For example: SELECT 'date_time', 'spid', 'blocked', ..., 'sql_text' UNION ALL _your_existing_query_ You would then have to use cast() or convert() on the columns in your existing query to convert all of the current results to strings so they can be UNIONed with the column names.
0 Likes 0 ·
what data type is date_time? You may not have to convert it if it's already stored as a character data type. If it's a date, try `convert(varchar(30), date_time, 121)` as a generic conversion.
0 Likes 0 ·
This comment chain seem strangely one-sided with all the OP's comments removed :)
0 Likes 0 ·
Show more comments
narendba avatar image
narendba answered
Hi Ken, This is the URL to check the 'Memory Usage Alert' Question. https://ask.sqlservercentral.com/questions/138464/memory-usage-alert.html
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.