Saving Query results as CSV file in SQL Server 2005

Question

Saving Query results as CSV file in SQL Server 2005

Viewing 5 posts - 31 through 34 (of 34 total)

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

mckinnj1 Mr or Mrs. 500 Points: 548 More actions · Answer 1

I got a solution to the collation issue.

SET @String = 'bcp "SELECT * FROM ULTIPRO_TMFHS.dbo.qry_Healthstream" QUERYOUT ' + @Path + @File + ' -c -CACP -S ' + @server + ' -T'

Now my problem is, the customer wants an ANSI tab delimited text file with CRLF. When I use CHAR(13) + CHAR(10) as the last column and the output file settings are like they are above, I get blank rows padding the report and they say they can't read the file in.

I've used -n as well and it adds unknown characters between the columns.

Is there a template for export types I can reference? Because the Microsoft version doesn't seem to be cutting it for me.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

The problem must be in the query you're exporting.

Must be a lot of functions which return NVARCHAR, not CHAR as you think.

After that find that guy who told you to add CHAR(13) + CHAR(10) and get your revenge.

He must want you get fired.

_____________
Code for TallyGenerator

mckinnj1 Mr or Mrs. 500 Points: 548 More actions · Answer 3

I ended up running the whole thing through Integration Services and just made a package.

I prefer to script things out because they require less system resources to run. But oh well. It's working now.

Thanks for the feedback!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

What type of object is qry_Healthstream?

--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)

gadilkarvishal Valued Member Points: 71 More actions · Answer 5

Hi,

I had same experience of comma separated csv not opned in Excel.

But then I realized that my MS office version was older(2000)and The same file opened fine in higher version(MS office 2003).

PLz try to open the same on the MS office higher version.