Solved

export to csv formatting

Posted on 2010-09-23
14
468 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:dbaSQL
  • 7
  • 7
14 Comments
 
LVL 16

Expert Comment

by:carsRST
ID: 33743543
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 33743655
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
 
LVL 16

Expert Comment

by:carsRST
ID: 33743824
On CSV file - you may need to add the options: -c and -t to your BCP output
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Expert Comment

by:carsRST
ID: 33744010
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 33744061
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
 
LVL 16

Expert Comment

by:carsRST
ID: 33744071
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
 
LVL 16

Accepted Solution

by:
carsRST earned 500 total points
ID: 33744104
See here for a basic example.  Compare to your BCP ouput.

http://www32.brinkster.com/srisamp/sqlArticles/article_4.htm
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33744121
>>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
 
LVL 17

Author Comment

by:dbaSQL
ID: 33744162
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 33744201
I don't just want to generate, carsRST.  I need to email it, as an attachment.  
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 33744304
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
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 33745102
I got it.  Thank you for the input.  
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33745478
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 33745527
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Why do I get the message "Message has been thrown by target of an invocation"? 22 49
CDC and AOG on MS SQL 2012 13 23
SQL trigger 5 19
SSMS Opening Mode 9 18
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question