September 11, 2013 at 5:38 am
Hi All,
I am in the process of starting a project and wanted to understand the best way to automate the creation of a CSV file after a process has completed.
I could get the user to run a Crystal Report which would create a CSV - but wanted to automate and remove the extra task from the user.
So basically I need to create a CSV file from an INSERT transaction.
any help would be appreciated.
Thanks
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 11, 2013 at 5:53 am
Here's a way you can run a query and write the results to a text file.
EXECUTE master..xp_cmdshell 'bcp "SELECT output_field FROM table_name ORDER BY ID;" queryout "z:\filename.txt" -C RAW -R -c -w -T'
Here are the caveats before you get started:
1. It does require that xp_cmdshell is turned on and runnable by the database user.
2. The drive/directory where you create the file must be visible to the server.
3. The text file is created on the server.
I'm not sure what your requirements for the user are, but you'd then have to figure out a way to get the text file to the user.
HTH
September 11, 2013 at 9:32 am
Thanks for the advice.
I will spec. it out a little further - just the concept of ... can it be done via a trigger on a table ... answer so far is yes. As for getting eth file to the user or to a location to send ... phase 2 !
Thanks
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 11, 2013 at 9:53 am
I have to say that a trigger that is creating a txt file throws up a big red flag to me. That sounds like the wrong tool for the job to me.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2013 at 10:34 am
I have to agree with Sean - this sounds like a bad idea. Depending on when the trigger fires, you're going to end up with an awful lot of text files accumulating on the database server.
Is this an audit situation where you can do something with a table instead? If you're going to be using the data in these text files for anything, shouldn't you be storing the database data inside the database?
September 11, 2013 at 10:42 am
If the process loading the data is a scheduled job, I'd put the csv creation into the job as another step. If it's a user-initiated process, you could have the trigger write a row into a "requests" table and have a job that wakes up at predictable intervals and polls the table for work to do as a first step.
Writing out a csv from a trigger is not a good way to accomplish your goal. There are too many possible errors in too many different environments to code around in that scenario. Just my 2 cents.
September 11, 2013 at 10:52 am
I wouldn't do it that way. My way would be to use the trigger (if i had to) to write the export data to another table, with a SQL Agent job scheduled to check it periodically and if data exists, to write that data out to the requested text file and then truncate the export data table.
September 11, 2013 at 11:02 am
A point to consider is that if a trigger is writing out text files, the transaction itself will wait for the file to be created in the file system. Like David pointed out, there are a lot of error possibilities there. Then there's the question of what happens if the transaction is rolled back...
Then again, we still don't know what the intent is for capturing this data in text files in the first place. I know I'm personally biased against triggers because of performance. They can be done if they must be, but we don't know if it's a necessity here. The question of what is to be done with the data inside the files is paramount because a database is meant to hold data. If the files are just going to be read in again, then there's no point to creating the text file at all.
September 12, 2013 at 1:06 am
Well that has certainly opened up the can of worms -
OK - The process I need to complete is as follows;
When a Sales Order is Dispatched - I need to create a CSV file to send to our distribution warehouse so that they can then pick the stock and send to the end customer. If they have a CSV file then they can import into their system.
There are a few options -
1. I can have a trigger on a table - so when there is an INSERT - export to a CSV file and then that is sent to our "Warehouse Depot" - File is then deleted/archived from that location.
2. Using Crystal Reports and put on the Users menu - so that they can run the report - creating a CSV file and then manually send the file.
What we are trying to achieve is an automated process - without the users having to think ....
Might have to do the crystal report as the first option to start it off...
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 12, 2013 at 7:23 am
SteveEClarke (9/12/2013)
Well that has certainly opened up the can of worms -OK - The process I need to complete is as follows;
When a Sales Order is Dispatched - I need to create a CSV file to send to our distribution warehouse so that they can then pick the stock and send to the end customer. If they have a CSV file then they can import into their system.
There are a few options -
1. I can have a trigger on a table - so when there is an INSERT - export to a CSV file and then that is sent to our "Warehouse Depot" - File is then deleted/archived from that location.
2. Using Crystal Reports and put on the Users menu - so that they can run the report - creating a CSV file and then manually send the file.
What we are trying to achieve is an automated process - without the users having to think ....
Might have to do the crystal report as the first option to start it off...
Since this is your distribution warehouse can't you just put the data in that system directly? Maybe SSIS or a webservice? Creating files in a trigger will cause you nothing but headaches.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 12, 2013 at 7:28 am
Unfortunately it is a 3rd party warehouse - so therefore a different system and we have to provide a CSV file for them to import.
I have been toying with the ideas and after the lack of "controlled disciplines" it is going to be a Crystal Report that the user can run and therefore be in control of the selection.
Would have been nice to automate but feel that may be a step too far at the moment.
Thanks for all your inputs
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
September 12, 2013 at 7:35 am
No problem at all - glad to help. At least you're going to be able to avoid the pain associated with having a trigger create text files. 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply