?
Solved

ASP -> CSV Export Recordset Alternate Options

Posted on 2004-04-07
6
Medium Priority
?
327 Views
Last Modified: 2012-08-13
Hello All

Is there an alternate (better) way to create a CSV file from a recordset  other than:

1. Using Filesystem Object (Don't like because of security issues)
2. Setting content type and asking the users to download a text file from the client browser (don't like because I need to name the text files uniquely and send it to a remote FTP server downstream)
3. Using OSQL or ISQL from SQL Server (tried it but the CSV format seems jumbled)

Regards...
0
Comment
Question by:geoenvi
  • 3
  • 2
6 Comments
 
LVL 10

Accepted Solution

by:
Shailesh15 earned 2000 total points
ID: 10783063
Since you are using SQl server use BCP utility to export the data into csv file...
Creat stroed procedure in sql server which exposrts query to csv file....
*******************************************************
IF (OBJECT_ID ('dbo.tblBalanceJobCSV') IS NOT NULL)
      DROP PROCEDURE dbo.tblBalanceJobCSV
GO

CREATE PROCEDURE dbo.tblBalanceJobCSV AS
BEGIN
      DECLARE @bcpCommand VARCHAR(8000)

      SET @bcpCommand = 'bcp "SELECT * FROM dbo.tblBalanceJob" queryout'
      SET @bcpCommand = @bcpCommand + ' C:\authors.csv -c -t"," -SSERVERNAME -UUSERNAME -PPASSWORD'
      EXEC master..xp_cmdshell @bcpCommand
            
END
******************************************

Then in ASP call this SP..

conn.Execute ("execute tblBalanceJobCSV")
0
 

Author Comment

by:geoenvi
ID: 10783357
Can you pass  parameters this way?  I used OSQL instead of bcp to export to CSV but the format is not what youy expect.  I postes that particular problem in the DB/SQL area.

0
 
LVL 10

Expert Comment

by:Shailesh15
ID: 10783445
You can pass parameters into Stored procedure. & Change the @bcpCommand  string to match.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:geoenvi
ID: 10783523
Yeah tried that too.. Same problem as osql.  the csv format appears jumbled.  Does not work
0
 
LVL 10

Expert Comment

by:Shailesh15
ID: 10783671
Either its a problem with Settings or data problem. BCP may give problem if special characters are present. Try exporting a simple table with simple fields as a test.
or
Try to export manualy through DTS then you will know if your sql server settings are OK.


0
 
LVL 7

Expert Comment

by:brgivens
ID: 10792692
What security issues in reagrds to FileSystemObject are you concerned about?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…

621 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