Software

Ask something
Discussion Locked

SQL 2000

0 helpful
3 replies

by tim.williams

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi there
Can anyone tell me how to do the following

I have a stored proccedure in sql 2000 to send an email on a scheduled job I want to run, however I want to have the output of the job added into the email,I have used the BCP command in my batch job and the output is exported to excel, can this sheet be attached to the auto email? does anyone know how to do this?

Also, what is the code for todays date - 2 days, i.e 2 days ago?

3 replies

by tony hopkinson

Look up xp_sendmail and if you want non resultset ouput the Print statement.

I can see how you ended up with BCP, but I'm drawing a total blank for the use of excel.

by catadmin

The way we put attachments to emails in our workplace is via a DTS job. Granted, the email attachments are actually secondary to the DTS because we're using the DTS job to actually do data transformations. However, if it works for us, it should work for you.

You could put your stored procedure into an Execute SQL task. Then put all your path names for your spreadsheet, etc. into an .ini file that the DTS job grabs before it sends the email out. One thing I should warn you about, though, is that we've had a lot of trouble with xp_SendMail. We've had to switch over to the custom built xp_SMTP_SendMail SQL code that was publically published on the web by Gert E.R. Drapers @ SQLDev.Net. It's the only way we could get our email to consistantly send via SQL.

As far as code goes for getting the date, check out GetDate in Books Online and combine it with DateDiff or DateAdd. That should get you the dates you want.

Hope that helps!

Catadmin

by rnielsen

I agree that DTS is the way to go. After transforming data from the db stored procedure to Excel, use a Send eMail Task to email the spreadsheet. You may need to deal with archiving the previous copy of the spreadsheet as the Send eMail Task needs a static name for the attachment. This method requires an email client on the SQL Server box-- but all DTS can be modified in code and you could change how it sends.

This discussion is closed to new replies.