Result in CSV format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Result in CSV format

Author

Topic

Babaksql
Starting Member

32 Posts

Posted - 2006-04-08 : 00:58:42

Hi,
I'd like to have my SQL query results in a CSV text file.
Can anybody give me a suggestion?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-08 : 01:17:09

If you are using Query Analyser, you can save the result in CSV.
Or to do it with BCP util.

Babaksql
Starting Member

32 Posts

Posted - 2006-04-08 : 08:28:55

Thanks,
But I want to pass a Sql statement from my aspx page and then generate a csv file at server side. Finally, I'd like to generate a link to open excel and show the csv file.
do you have any idea?

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-08 : 08:43:13

you can use xp_cmdshell to run BCP queryout to csv text file on your server side

Babaksql
Starting Member

32 Posts

Posted - 2006-04-08 : 10:08:33

Thanks a lot Khtan.
It realy helped me.

Babaksql
Starting Member

32 Posts

Posted - 2006-04-08 : 23:04:02

But there is a problem, when I generate the text file on server side.
and if it contains more than 64K records. So how can I open it with Excel on client side?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-09 : 01:36:52

"64K records"
That's Excel limitation. Even StarOffice calc is also 64K.
Limit the record size to < 64K ? Split to multiple files ?

Babaksql
Starting Member

32 Posts

Posted - 2006-04-09 : 07:39:16

Khtan, I hope you can find a solution.
I did the way you told me about BCP

I have a simple BCP command to read data and store them in a text file.
it works fine when I'm runing it in query analyzer. However, when I try to run it from my VB.Net application by the ExecuteNonQuery statement, nothing happens.

I ask this qustion as a new topic as well
Cheers Khtan.

X002548
Not Just a Number

15586 Posts

Posted - 2006-04-09 : 17:31:31

All of this should be placed in to Stored Procedures

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Babaksql
Starting Member

32 Posts

Posted - 2006-04-10 : 02:36:35

I tried stored procedure, no errors but nothing happens at all.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-10 : 03:10:20

Try this
Step 1 : try running the BCP from command prompt first. Make sure the BCP is able to generate the text file that you want
Step 2 : test with using Query Analyser exec master..xp_cmdshell @your_bcp_cmd
Step 3 : create a store procedure to perform above and test it from Query Analyser

Babaksql
Starting Member

32 Posts

Posted - 2006-04-10 : 23:59:10

Thanks,
Now it's working through the stored procedure.
However I have to give the administrator privilege to the ASPNET username and make some changes to (EM->Management->Sql Server Agent)

Do you thing giving administrator privilege to ASPNET user is ok ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources