Solved

ASP -> CSV Export Recordset Alternate Options

Posted on 2004-04-07
6
319 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 500 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
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/…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now