Question:
How can you export data (new unique file for each record) to a formatted flat file from SQL? Any ideas? I think that this is something that Perl excels at, but I was wondering if anything can be accomplished by using triggers and a stored procedure.

Note- the data is being collected from a decision making application on the web.

What database? MySQL has the SELECT INTO OUTFILE command to dump a table to a delimited text file. See:

http://www.mysql.com/doc/en/SELECT.html

for more info..

Cheers,

Alex
--
Gossamer Threads Inc.

>>
(new unique file for each record)
<<

I am running from a SQL 7 server.

The unique number (generated from a session ID) needs to be the file name for that particular record.

Also, the trigger should run once a query parameter is met on final update, meaning that once the final field has a certain value, then a flat file must be created.

SQL Server's Data Transformation Service Packages(DTS) can easily be used to export data to a text file, or Excel spreadsheet, etc etc. However, this doesn't automatically dump out a new file for each data record.

If getting the information into a tab/comma-separated format or Excel spreadsheet is helpful en route to your final file, then you can use the Enterprise Manager management tool provided with SQL 7. Here's how to do that for a text file.

Open Enterprise Manager and expand the server tree, expand the database tree, and right-click on the name of the database you want to export out of. Select the pop-up menu "All Tasks", then select "Export Data", and the wizard will walk you through it. When it asks you about your destination, select from the SOURCE dropdown the driver simply titled "Text File" (not the ODBC Microsoft Text drivers), and follow instructions.

That's it. before you execute and exit the wizard, check the "Save DTS Package" to save the package locally to the SQL Server. IT will show up under the Enterprise Manager "Data Transformation Services" tree under Packages, and you can right-click to run again, or to edit the design.

Hope this is partly useful...

-Dan