emailing an attachment from DTS

I have a DTS package that runs 4x a day and generates an excel spreadsheet, renames that spreadsheet with a datetime stamp and then places it into a folder on our network. I have been asked to email that spreadsheet to someone everytime the package runs.

My question is what would be the best way to handle this and how do I ensure that whatever process I define grabs the correct file? The folder that the Excel file is being placed into has multiple files in it. Is there a way to tell SQL Server which file to grab and email?

Any thoughts or suggestions would be greatly appreciated! Thanks!

Frank

You can use Gert's xp:

http://sqldev.net/xp/xpsmtp.htmsql

EM and QA displays different format for a date field

Hi,
I have a datetime field with a value '1/1/02'. EM display it as 1/1/02 But
Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT * FROM
Table1)
So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
PC does the same.
Thanks.You have a problem under standing of what a data value is and how it is
stored. It is actually just stored as numeric value, denoting offsets from
some time in the reasonably distant past. Format is based on the user. QA
uses a standard value, mostly because when you are using QA the goal is
usually to see ALL of the data.
Enterprise manager is probably using your system settings. When you
actually use the data, you can format it as you wish.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:OqYzX0yUFHA.2136@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a datetime field with a value '1/1/02'. EM display it as 1/1/02
> But Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT *
> FROM Table1)
> So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
> PC does the same.
> Thanks.
>|||Chrissi,
Different clients display things differently, with the possible
excepting of strings made up of printable ASCII with codes
from 32 to 127.
If you want the datetime displayed in a particular format, I'd
suggest you select CONVERT(varchar(40),yourDatetimeColumn,X
XXX),
where XXXX is the appropriate SQL Server format code (see the
Books Online article CAST and CONVERT).
If you do this, you won't be able to "edit" the datetime values
in Enterprise Manager, but that's not a good idea anyway. Better
to make changes programmatically with UPDATE or INSERT
statements.
Steve Kass
Drew University
Chrissi wrote:

>Hi,
>I have a datetime field with a value '1/1/02'. EM display it as 1/1/02 Bu
t
>Query Analyzer displays it as 2002-01-01 00:00:00.000 (I used SELECT * FROM
>Table1)
>So in Query Analyzer, I can't tell the fomat of it. I just wonder if your
>PC does the same.
>Thanks.
>
>

Efficient way of purging old data?

I have a table with no primary key.

The are 6 columns. The first four columns represent one object. There is then a status and a datetime (Updated).

A new record is added into the table whenever a status changes (I need to keep the old status as well).

The problem I have is how to efficiently purge old data.

I need to keep all records with a datetime within the last week.

I also need to ensure that I at least one status is kept for each combination of the first four columns.

The table contains over 150,000 records. About 1,000 statuses change per day.

I want to purge the data once a week - delete about 7,000 records.

This is the query I wrote

delete from table1 where Updated <
(select max(Updated) from table1 t1 where table1.col1 = t1.col1 and table1.col2 = t1.col2
and table1.col3 = t1.col3 and table1.col4 = t1.col4 group by col1, col2, col3, col4)
and Updated < GetDate() - 7

This seems to be fairly fast - records only change a couple of times a week, so the subquery only returns 3 or 4 records. The subquery will be called on almost all the records in the table though.

Can anyone see a more efficient way of doing this?

Well, I'd use a dereived table myself:

delete from table1 t1
inner join
(select col1, col2, col3, col4, max(Updated) as MaxUpdated
from table1
group by col1, col2, col3, col4) t2 on t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col3 = t2.col3 and t1.col4 = t2.col4 and t1.Updated = t2.MaxUpdated
where t1.Updated < GetDate() - 7

That usually seems to perform faster then a subquery, but you'd have to try it for yourself to know for sure.