Generate .txt file and send as email attachment

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Generate .txt file and send as email attachment

Author

Topic

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-25 : 06:08:38

I am trying to automate a process that I am currently performing manually but I am stuck so any help would be brill!

I have a simple a table made up of 4 columns :

PersonId, EmailAddress, DBId,CreationDate

The idea is to generate a weekly file that contains any new editions to this table and send it as an email attachment. Sounds simple enough doesn't it?! The SQL to generate the desired result set is basic stuff but I am not sure of how to:

1) Output the file as a .txt using TSQL
2) Where the file should reside
3) How to send it as an email attachment.

Any help with any of the 3 mentioned points would be much appreciated.

Look forward to your replies.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-25 : 06:15:22

You can create an SSIS package which does all of the three tasks mentioned by you quite easily and effectively.

The file can be placed in the shared folder on either server or your local machine.

For sending mail, you can use script task as shown here:
http://blogs.conchango.com/jamiethomson/archive/2006/07/03/SSIS_3A00_-Sending-SMTP-mail-from-the-Script-Task.aspx

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-02-25 : 06:15:56

Well, what format do you want your .txt file? Do you want csv/fixed space/pipe delimited etc?

You can use SSIS to get the output desired.

The file can reside anywhere you want, but your SQL Service Account needs to have access to this directory.

Use either xp_sendmail or db_send_dbmail to send the mail, db_send_dbmail has a parameter of @file_attachments and xp_sendmail has @Attachments.

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-25 : 06:26:35

I can't use SSIS for this exercise as we do not currently use SSIS in our live system. I can perform this in SSIS on my desktop for sure but I want this task to run weekly so I plan to have a stored procedure that generates the recordset and outputs it as a .txt file. The file format will be delimited with just one column made up of PersonID-DatabaseID using double quotes as my text qualifier.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-25 : 06:32:08

You can use osql utility to generate the file.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources