Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-16 : 09:28:32
|
| Phil writes "I would like to create a trigger which will export an entry to a text file.Is there a standard function/method to do this?" |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-07-17 : 16:30:46
|
BCP is what ya want. entry from BOLquote:
bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -Ppassword
if you want it to export what was just entered into the table use: select * from inserted -----------------------Take my advice, I dare ya |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-17 : 16:34:35
|
quote:
if you want it to export what was just entered into the table use: select * from inserted
Um, that won't work inside a trigger. bcp creates its own connection and cannot see the inserted table. You'd have to copy the contents of inserted into a regular table in order for bcp to access it. |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-07-17 : 16:42:56
|
| Would putting it in a tablevar work?or for that matter.. a temp table?-----------------------Take my advice, I dare ya |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-17 : 17:12:36
|
| No, because each connection is isolated from the others, therefore a temp table or table variable is out of scope for the call to bcp. A global temp table might work though, but it's better to just use a regular table. Truthfully, it's kind of risky to call xp_cmdshell in a trigger like this if there is anything more than very light database activity. It would be better to have the data inserted into a "status" table, for example, and have a scheduled job export from the status table on a regular basis. |
|
|
|
|
|
|
Exporting to a txt file from a Trigger. SQL Server 7