export to csv formatting

simply need to send the output of a query as an email attachment.  i can send it just fine (sp_send_dbmail), but the csv format is terrible. see here --

The values per record are like this:  AA   100  -200   200  -2000  -400  400   2000   AAA  9

The export to csv mushes them all into one value, separating each with a '?', like this:    
    AA?100?-200?200?-2000?-400?400?2000?AAA?9

I've got numerous other jobs running just like this, sending csv attachments daily, without problem.  I can't find the error in this.  

Also, when trying to define the file with the datestamp, like this:
   SET @file = 'D:\Reports\filename_'+convert(char(8),getdate(),112)+'.csv'

It fails with this:
  Msg 22051, Level 16, State 1, Line 0
  Attachment file D:\Reports\filename.csv_20100923.csv is invalid.

Yet if I pull out the date, I write the file in just fine.   (except that the output blows)

See my post below.  The @query is formatted like that to allow for a header line in the output.  When run in SSMS, the output is perfect, EXCEPT that I really need to order the symbols underneath the headerline.  any ideas on how to do that?

Please and thank you, in advance.
DECLARE @file VARCHAR(100),
        @query VARCHAR(5000),
        @subject NVARCHAR(1000)

SET @file = 'D:\Reports\filename.csv'
SET @query = '
bcp "SELECT 
Symbol,MaxPos,MinVega,MaxVega,MinDelta,AutoMinDelta,AutoMaxDelta,MaxDelta,EndPoint,stdSize
FROM view WHERE maxpos = ''maxpos'' 
UNION 
SELECT Symbol,MaxPos,MinVega,MaxVega,MinDelta,AutoMinDelta,AutoMaxDelta,MaxDelta,EndPoint,stdSize
FROM view WHERE maxpos <> ''maxpos'' 
ORDER BY ''maxpos'' DESC" 
queryout "'+@file+'" -w -Sservername -T'

SET @subject = N'my subject - ' + CONVERT(NVARCHAR(11),GetDate(),121) 

EXEC msdb.dbo.sp_send_dbmail @recipients ='me.com',
@Subject=@subject,
@body_format='HTML',
@file_attachments=@file

EXEC master..xp_cmdshell @query

Open in new window

LVL 17
dbaSQLAsked:
Who is Participating?
 
carsRSTConnect With a Mentor Commented:
See here for a basic example.  Compare to your BCP ouput.

http://www32.brinkster.com/srisamp/sqlArticles/article_4.htm
0
 
carsRSTCommented:
0
 
dbaSQLAuthor Commented:
This piece is nearly there, it's just a matter of the formatting, and my other two issues with the date in the filename, and the ordering of the values.

Any other EE ideas?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
carsRSTCommented:
On CSV file - you may need to add the options: -c and -t to your BCP output
0
 
carsRSTCommented:
Few things on file attachment....

it could a permissions issue while trying to attach.  You may try to make a copy of the file and attach the copy.

You also may try UNC vs "d:\..."

Or could be the process under which your sql is running (on the server) doesn't have permissions to the file.
0
 
dbaSQLAuthor Commented:
If it were a permissions issue, I wouldn't be able to write the file at all.  I"m writing it just fine... the format is not working. All values per record are smushed into one cell in the csv.
0
 
carsRSTCommented:
Adjust your BCP to include the options -t -c

Also, try copying the file out to see if the ouput process still has a hold of the file.
0
 
carsRSTCommented:
>>Also, try copying the file out to see if the ouput process still has a hold of the file.

Expand....copy the file after creating.  Attach the copied file, as opposed to the original.

0
 
dbaSQLAuthor Commented:
I've already tried the -t and -c.  Even changing the query to simply exec a proc, or just a raw select -- the output from all of them is the same.

this is a cut/paste from the csv:
AA      100      -200      200      -2000      -400      400      2000      AAA      9

but, that came out of one cell.   just the first cell, all the way down, every record is formatted like that, just looking at the csv, it looks like this:

AA?100?-200?200?-2000?-400?400?2000?AAA?9

it's not really a '?'.  it's a '?' inside of a tiny little box, in between each value
0
 
dbaSQLAuthor Commented:
I don't just want to generate, carsRST.  I need to email it, as an attachment.  
0
 
dbaSQLAuthor Commented:
I got it working, finally, using the example from this post:  ID: 33744104

The file is formatted correctly, I should say.  Now I am working on the email attachment.
0
 
dbaSQLAuthor Commented:
I got it.  Thank you for the input.  
0
 
carsRSTCommented:
Thank you for the points, although I'm sure your trial and error played a huge part in it.  :)

Do you mind, for my knowledge, what you did to get the email working?
0
 
dbaSQLAuthor Commented:
Sure, I declared @date and @filename, and set @filename = 'D:\......_'+@date+'.csv'.
Then my @bcpCommand referenced @filename with -c -t"," -Sservername -T

And after the EXEC of @bcpCommand, I executed sp_send_dbmail with @filie_attachments = @filename.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.