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';
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
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
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.
Hi Ken, This is the URL to check the 'Memory Usage Alert' Question.
https://ask.sqlservercentral.com/questions/138464/memory-usage-alert.html
17 People are following this question.
