ASP -> CSV Export Recordset Alternate Options

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...
geoenviAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Shailesh15Connect With a Mentor Commented:
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
 
geoenviAuthor Commented:
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
 
Shailesh15Commented:
You can pass parameters into Stored procedure. & Change the @bcpCommand  string to match.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
geoenviAuthor Commented:
Yeah tried that too.. Same problem as osql.  the csv format appears jumbled.  Does not work
0
 
Shailesh15Commented:
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
 
brgivensCommented:
What security issues in reagrds to FileSystemObject are you concerned about?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.