Auto Email Statistics from DB Table

Hi,
I have a user database 'MyDatabase'. There are various
Tables in this db. After executing a complex query I get
some statistics , which I have to send to my boss on daily
basis. I am failing to send statistics manually by
preparing it as email most of the times coz of other
prioity issues. I am looking for a automatic mailing
solution with SQL Server Machine ( the machine also does
have MS SMTP). I have the prototype in mind...
1. I need to get the data into a temporary table as soon
as the clock moves to 12:00 AM
2. Do Email Operations
3. Clear the Contents of the table
I am not at all sure with what has to be done
programatically. Any guidance will be highly appreciated.
Sincere Regards
ChipYou could use SQL Jobs to do these along with the mailing.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...

quote:

---

> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>

---

|||Create a job, in the job do xp_sendmail (see BOL for detail), in the next
job step clear the table.
hth
Quentin
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...

quote:

---

> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>

---

|||sincere regards for the inputs.
Chip

quote:

---

>--Original Message--
>Create a job, in the job do xp_sendmail (see BOL for

---

detail), in the next

quote:

---

>job step clear the table.
>hth
>Quentin
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
daily[QUOTE]
appreciated.[QUOTE]
>
>.
>

---

|||In case you don't have xp_sendmail configured (can be a real pain to setup),
you might want to check out xp_smtp_sendmail from www.sqldev.net.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:131b01c3df78$96d273b0$a501280a@.phx.gbl...[QUOTE]
> sincere regards for the inputs.
> Chip
> detail), in the next
> message
> daily
> appreciated.|||Hi Karaszi,
Thank you very much for the resource. You saved me for the
time being.
Regards
Chip

quote:

---

>--Original Message--
>In case you don't have xp_sendmail configured (can be a

---

real pain to setup),

quote:

---

>you might want to check out xp_smtp_sendmail from

---

www.sqldev.net.

quote:

---

>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?

---

oi=djq&as_ugroup=microsoft.public.sqlserver

quote:

---

>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:131b01c3df78$96d273b0$a501280a@.phx.gbl...
various[QUOTE]
get[QUOTE]
does[QUOTE]
soon[QUOTE]
>
>.
>

---

sql

Auto Email Statistics from DB Table

Hi,
I have a user database 'MyDatabase'. There are various
Tables in this db. After executing a complex query I get
some statistics , which I have to send to my boss on daily
basis. I am failing to send statistics manually by
preparing it as email most of the times coz of other
prioity issues. I am looking for a automatic mailing
solution with SQL Server Machine ( the machine also does
have MS SMTP). I have the prototype in mind...
1. I need to get the data into a temporary table as soon
as the clock moves to 12:00 AM
2. Do Email Operations
3. Clear the Contents of the table
I am not at all sure with what has to be done
programatically. Any guidance will be highly appreciated.
Sincere Regards
ChipYou could use SQL Jobs to do these along with the mailing.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>|||Create a job, in the job do xp_sendmail (see BOL for detail), in the next
job step clear the table.
hth
Quentin
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
> Hi,
> I have a user database 'MyDatabase'. There are various
> Tables in this db. After executing a complex query I get
> some statistics , which I have to send to my boss on daily
> basis. I am failing to send statistics manually by
> preparing it as email most of the times coz of other
> prioity issues. I am looking for a automatic mailing
> solution with SQL Server Machine ( the machine also does
> have MS SMTP). I have the prototype in mind...
> 1. I need to get the data into a temporary table as soon
> as the clock moves to 12:00 AM
> 2. Do Email Operations
> 3. Clear the Contents of the table
> I am not at all sure with what has to be done
> programatically. Any guidance will be highly appreciated.
> Sincere Regards
> Chip
>|||sincere regards for the inputs.
Chip
>--Original Message--
>Create a job, in the job do xp_sendmail (see BOL for
detail), in the next
>job step clear the table.
>hth
>Quentin
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
>> Hi,
>> I have a user database 'MyDatabase'. There are various
>> Tables in this db. After executing a complex query I get
>> some statistics , which I have to send to my boss on
daily
>> basis. I am failing to send statistics manually by
>> preparing it as email most of the times coz of other
>> prioity issues. I am looking for a automatic mailing
>> solution with SQL Server Machine ( the machine also does
>> have MS SMTP). I have the prototype in mind...
>> 1. I need to get the data into a temporary table as soon
>> as the clock moves to 12:00 AM
>> 2. Do Email Operations
>> 3. Clear the Contents of the table
>> I am not at all sure with what has to be done
>> programatically. Any guidance will be highly
appreciated.
>> Sincere Regards
>> Chip
>
>.
>|||In case you don't have xp_sendmail configured (can be a real pain to setup),
you might want to check out xp_smtp_sendmail from www.sqldev.net.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:131b01c3df78$96d273b0$a501280a@.phx.gbl...
> sincere regards for the inputs.
> Chip
> >--Original Message--
> >Create a job, in the job do xp_sendmail (see BOL for
> detail), in the next
> >job step clear the table.
> >
> >hth
> >
> >Quentin
> >
> >
> >"Chip" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
> >> Hi,
> >>
> >> I have a user database 'MyDatabase'. There are various
> >> Tables in this db. After executing a complex query I get
> >> some statistics , which I have to send to my boss on
> daily
> >> basis. I am failing to send statistics manually by
> >> preparing it as email most of the times coz of other
> >> prioity issues. I am looking for a automatic mailing
> >> solution with SQL Server Machine ( the machine also does
> >> have MS SMTP). I have the prototype in mind...
> >>
> >> 1. I need to get the data into a temporary table as soon
> >> as the clock moves to 12:00 AM
> >>
> >> 2. Do Email Operations
> >> 3. Clear the Contents of the table
> >>
> >> I am not at all sure with what has to be done
> >> programatically. Any guidance will be highly
> appreciated.
> >>
> >> Sincere Regards
> >>
> >> Chip
> >>
> >
> >
> >.
> >|||Hi Karaszi,
Thank you very much for the resource. You saved me for the
time being.
Regards
Chip
>--Original Message--
>In case you don't have xp_sendmail configured (can be a
real pain to setup),
>you might want to check out xp_smtp_sendmail from
www.sqldev.net.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:131b01c3df78$96d273b0$a501280a@.phx.gbl...
>> sincere regards for the inputs.
>> Chip
>> >--Original Message--
>> >Create a job, in the job do xp_sendmail (see BOL for
>> detail), in the next
>> >job step clear the table.
>> >
>> >hth
>> >
>> >Quentin
>> >
>> >
>> >"Chip" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0f3e01c3df42$a80d5ef0$a501280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> I have a user database 'MyDatabase'. There are
various
>> >> Tables in this db. After executing a complex query I
get
>> >> some statistics , which I have to send to my boss on
>> daily
>> >> basis. I am failing to send statistics manually by
>> >> preparing it as email most of the times coz of other
>> >> prioity issues. I am looking for a automatic mailing
>> >> solution with SQL Server Machine ( the machine also
does
>> >> have MS SMTP). I have the prototype in mind...
>> >>
>> >> 1. I need to get the data into a temporary table as
soon
>> >> as the clock moves to 12:00 AM
>> >>
>> >> 2. Do Email Operations
>> >> 3. Clear the Contents of the table
>> >>
>> >> I am not at all sure with what has to be done
>> >> programatically. Any guidance will be highly
>> appreciated.
>> >>
>> >> Sincere Regards
>> >>
>> >> Chip
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

Attaching data at differnet levels in a heirchy

I don't think this is too complex, but I can't figure out what what the correct solution is.

I have 6 tables: Customer, Project, Rate, Hours, EquipmentType, Equipment

All projects belong to a customer, so customers have a PK CustomerId and Project has a PK {CustomerId, ProjectId} where CustomerId is a FK to Customer.

A rate can be defined as being at a project level or a Customer level. A project must specify if it is using the customer level rates or its own rates.

So what I wanted to do at first was creat the key in Rate as {CustomerId, ProjectId NULLABLE, EquipmentTypeId}. The idea being that a NULL ProjectId would mean that the Rate was Customer level since it didn't point to a project.

But of course that won't work. In addition to SQL Server not allowing a PK with a NULLABLE it would also mess up my relationship between Rate and Hours.

I wanted to specify in Hours the Rate, Project (and Customer), and Equipment and Equipment type.

I didn't want to create 2 rate tables, one for Customer level rates and one for Project level rates, but is that what I should have done?

I hope this is a clear explanation.

Does anyone have a better idea how to create what I want.It is hard to give you advice without seeing any data.
But I don't think Project table should have CustormerID at all.
Usually people create Customer table with CustormerID PK and
Project table with ProjectID PK and in between for multi-to-multi relationships you should have CustomerProgect table with CustomerID, ProjectID PK and CustomerID FK and ProjectID FK.
The same is with a Rate. Just create Rate table with column RateType where you can store C or P meaning Customer or Project type rate.
And in between just create Customer_Rate table with CustormerID, RateID PK and corresponding foreign keys.

Hope it helps.