T-SQL Export to CSV help

Question

Post reply

T-SQL Export to CSV help

Viewing 7 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 1

are you replacing <SERVERNAME> with your server name right?

Can you please check the syntax try to print the @cmd and check.

nicolas.worth Old Hand Points: 311 More actions · Answer 2

Hi, Sorry yes I am replacing <SERVERNAME>. Thanks

So the cmd looks like -

sqlcmd -S MyServerName -E -h -1 -d AdventureWorks -Q "SET NOCOUNT ON; SELECT U.[UserId] ,U.[UserName] ,U.[DateCreated] FROM dbo.Users U" -o "C:\test_output_file.csv" -s","

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 3

Prassad Dabbada V R

Ten Centuries

Points: 1251

January 21, 2014 at 8:34 am

#1681963

Remove "SET NOCOUNT ON" and try to execute you command

nicolas.worth Old Hand Points: 311 More actions · Answer 4

Hi,

Thanks for your reply. The reason why I added that was to remove the row count on the output of the CSV. However I removed the section as you suggested and the output was with whitespace. See below -

CB8ECBE0-EDEB-41CB-B8B1-B15DC8F1A70E,Temp123 ,2014-01-21 10:03:32.750

66D53C8E-E543-4B55-983C-F0BE97936655,User123 ,2014-01-22 10:03:32.753

(2 rows affected)

paul.knibbs SSCoach Points: 15320 More actions · Answer 5

There's a -W parameter to SQLCMD that removes trailing spaces from fields--would that work for you?

nicolas.worth Old Hand Points: 311 More actions · Answer 6

paul.knibbs (1/22/2014)
There's a -W parameter to SQLCMD that removes trailing spaces from fields--would that work for you?

Thanks Paul, Passing the -W parameter did the trick.

Many Thanks

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 7

Instead of using SQLCMD for this, I'd recommend the use of BCP instead (with the understanding that it won't give you column headers).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)