Automatically export data to a csv file

[charlise]

Hi Everyone,
Is there anyway to have data from a table be automatically exported after another action takes place. For example I have a trigger that updates one table when another table is inserted into. After the table is updated, I want that data to automatically be exported to a csv file. Is that possible? Does anyone have any suggestions?

 

[JayKusch]

Here is an idea to start with.

Create a table called tblSendEmail.
In this table create and ID field of some sort as an Integer value.

Now, create the On Insert trigger on the needed table and have it write ONLY the ID of the newly created record. You may need to create this ID field if a unquie field does not exists.

Now set up a SQL Server job that:

1. Reads from the tblSendEmail to see if any new IDs are in there

2. If there are, a stored procedure that you can pass the ID into.

3. In the SP, have a SELECT statement that pulls the needed information from the record in the tblSendEmail. From there build your email message and then send it out using xp_sendmail.

4. Once the email is sent off ... delete the record in the tblSendEmail table

5. Schedule the job to run every 5 minutes or so.

You will of course need to set up mail for the SQL Server account to leverage the xp_sendmail command.

That should get you going!


Thanks

J. Kusch