I am running this sql which creates a csv file and sends the output via email. One of the columns in the output. is a datetime field. When it is formatted in the csv file, a portion of the datetime column is missing when using the tab separator. I changed the separator to be a comma and it no longer loses the data in the datetime field. It formats correctly. Now the issue with the comma as the separator causes the file when you open it in excel to show the commas and each row is treated as one field. However, if I view the file instead of opening it the format is correct. I'm just not sure how to make the file look correct (without seeing the commas) if the user opens the attachment in email,
DECLARE @tab VARCHAR(1)
SET @tab = CHAR(9)
DECLARE @sub VARCHAR(100)
DECLARE @qry VARCHAR(1000)
DECLARE @msg VARCHAR(250)
DECLARE @query NVARCHAR(1000)
DECLARE @query_attachment_filename NVARCHAR(520)
SELECT @sub = 'CxDB NYTimes Report'
SELECT @msg = 'Please refer to the attached spread sheet for the report.'
SELECT @query = ' SET NOCOUNT ON;
select * from cxdb.dbo.NewYorkTimesView '
SELECT @query_attachment_filename = 'CxDB NYTimes Report.xls'
@profile_name = 'ms sql dba',
@recipients = 'email@example.com',
@body = @msg,
@subject = @sub,
@query = @query,
@query_attachment_filename = @query_attachment_filename,
@attach_query_result_as_file = 1,
@query_result_header = 1,
@query_result_width = 32767,
@query_result_separator = @tab,
@query_no_truncate = 0,
@exclude_query_output = 0,