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
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

Auditing Users on various SQL Servers

We have about 50 SQL Servers (most 2000 but some 2005) in different domains and behind firewalls and we implementing the PCI rules for the use of credit card data. One thing we need to do is to audit users and their rights. I have a stored proc that can run each night and record users and permissions.

My main problem is trying to bring all that data together on a central box and then parse through it to see if I have any offenders. The parsing is not the issue, but the polling is. I could use the SA account or create a audit account with SA rights but both of those solutions go against the PCI mantra.

Can someone who has done this give me some guidance as to what you did. I know that there are some third party tools that would probably do this, but my department is on a shallow budget and my overtime is free.

Any suggestions would be greatly appreciated. Thank you!

Jim Youmans

St. Louis

An ideea is to write an event log in operating system ( you can view it with EventViewer) when the user use credit card data then collect that data from all 50 SQL Servers in a SQL database and analyze it (see).|||Bets thing would be the other way around. Start an application / job on the server themselves and push the data to a collecting server. Rather than providing your application with password of each server you can just provide the application with the password of the collecting server assuming you run the application in a trusted context on the remote servers.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

You may be best served to explore some of the third party auditing products. They are robust and well vetted. Some may be PCI 'approved'.

My experience with Luminigents and ApexSQL's products leads me to belief that they are very cost-effective.

Audit Tools
ApexSQL Audit http://www.apexsql.com/sql_tools_audit.asp
AuditDatabase (Free Web based trigger generation) http://www.auditdatabase.com/
Lumigent Audit DB http://www.lumigent.com/products/auditdb.html
OmniAudit http://www.krell-software.com/omniaudit/index.asp
SQLLog http://www.rlpsoftware.com/mainframe.asp?contents=SQLLog.asp&mainmenu=SQLLog&submenu=Info
Upscene SQL Log Manager http://www.upscene.com/index.htm?./products/audit/mssqllm_main.htm
DB Audit Expert http://www.softtreetech.com/dbaudit/

Auditing Config

I need to query system tables for various config options. Anyone know if the auditing options(login-success/fail/all) are stored in a table and what that table might be?
Jeffexec sp_configure is the supported way to query this, or you can look at
master.dbo.spt_values
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Jeff Ericson" <jeff.ericson@.mbna.com> wrote in message
news:FB99D88D-62D5-44D2-8D01-9AC00EF28731@.microsoft.com...
> I need to query system tables for various config options. Anyone know if
the auditing options(login-success/fail/all) are stored in a table and what
that table might be?
> Jeff|||Couple of options
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff Ericson" <jeff.ericson@.mbna.com> wrote in message
news:FB99D88D-62D5-44D2-8D01-9AC00EF28731@.microsoft.com...
I need to query system tables for various config options. Anyone know if
the auditing options(login-success/fail/all) are stored in a table and what
that table might be?
Jeff|||Oops sent that last post a bit quick
Easiest way would be
exec xp_loginconfig 'audit level'
Details are in BOL
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff Ericson" <jeff.ericson@.mbna.com> wrote in message
news:FB99D88D-62D5-44D2-8D01-9AC00EF28731@.microsoft.com...
I need to query system tables for various config options. Anyone know if
the auditing options(login-success/fail/all) are stored in a table and what
that table might be?
Jeff|||the detail actually is stored in registry...
>--Original Message--
>I need to query system tables for various config
options. Anyone know if the auditing options(login-
success/fail/all) are stored in a table and what that
table might be?
>Jeff
>.
>|||Hi Jeff
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
You can get the Audit Level in the database security option from the
registry table or by extensive stored procedure 'xp_loginconfig'.
From the 'Start' menu of you Windows system, click 'Run', and run
'regedit'. You will get the a registration key 'AuditLevel' at the location
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
I should explain more about this key.
If your Audit Level is set to:
'None', the value would be 0x00000000(0);
'Success', the value would be 0x00000001(1);
'Failure', the value would be 0x00000002(2);
'All', the value would be 0x00000003(3).
You can also use the following code to retrieve the value of the Audit
Level:
Use master
exec xp_loginconfig 'audit level'
go
You will get
name config_value
----
audit_level success
I hope this would answer your question. If you still have questions, please
feel free to post any new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for all the replies, I'll thry the extended SP, we don't have access to the registry(or the server (&%*(&()!!!!).

Audit Table - Design Question

I am looking for feedback on how to best design an auditing database for
tracking the progress of a file through various applications and vendors.
We need to be able to track delays in processing, as well as lost or missing
files.
We have a process where we send voice files out to a third party vendor to
have them transcribed, then returned to us as text files. I need to audit
the following events:
1. When was the voice file sent to the vendor
2. When was the voice file received by the vendor
3. When did the vendor return the transcribed text file to us
Each of these events could be tracked in a separate table, and if we apply
strict rules of normalization they probably should. For auditing reports I
would probably create a view with full outer joins on all three tables, or
many views with various joins for various purposes. It is expected that
these tables would contain up to one million rows each, with one and only
one row in each table corresponding to a row in each other table. In
theory, there could be errors at any stage of the process that results in
one event never occurring, effectively creating orphan records.
However, since this is strictly for auditing the progress of a particular
file through the systems, it seems to me that I could use a single table,
and update an existing row for each event, or create a new row if one does
not exist. This leaves me with questions regarding how to apply a primary
key and unique constraints. It also, however, would greatly speed up our
ability to run reports.
Here is a description of the events and data we will be tracking:
EVENT 1
On our end the voice data file names are unique, and we have the following
information available:
1.1. file name (strSentFileName)
1.2. IP address of the PC that submitted the file (strPC_ID)
1.3. ID of the person who dictated the voice file (strProviderID)
1.4. Date and time the file was submitted to the vendor (datSent)
EVENT 2
When the vendor receives the file, they send back to us a confirmation and w
e enter the following data:
2.1. Original File Name (strSentFileName)
2.2. New file name if applicable (strReturnFileName)
2.3. Unique identifier from their system (strJobID)
2.4. Date and time the confirmation was received (datConfirmed)
EVENT 3
When we receive the transcribed text file back from the vendor, we enter the
following data:
3.1. Unique identifier from their system (strJobID)
3.2. Date and time the final text file was received (datReturned)
Below is the DDL that I have thus far, but I am still mulling over how best
to design this. I originally thought of a rather denormalized approach to
this design, then rethought it and came up with a more normalized design.
Because we are auditing a process and could have errors that need to be
tracked, we expect orphans (or nulls) that complicate each approach. Any
advice as to how best design this would be much appreciated, as would
criticism of each of the approaches below.
Please excuse the camel case naming convention. I don't care for it myself,
but it is a standard within our environment.
--********************************
--Script to create a single, denormalized table for auditing
--********************************
-- No keys are defined, since any of the 3 events described above could fail
to occur
-- strSentFileName and strJobID should be either unique, or NULL
CREATE TABLE [dbo].[tblTranscriptionAudit] (
[strSentFileName] [varchar] (50) NULL ,
[strJobID] [varchar] (10) NULL ,
[strReturnFileName] [varchar] (50) NULL ,
[datSent] [smalldatetime] NULL ,
[datConfirmed] [smalldatetime] NULL ,
[datReturned] [smalldatetime] NULL ,
[strPC_IP] [varchar] (15) NULL ,
[strProviderID] [varchar] (10) NULL
) ON [PRIMARY]
GO
--********************************
--Script to create 3 normalized tables for auditing
--********************************
CREATE TABLE [dbo].[tblVoiceFilesSent] (
[strSentFileName] [varchar] (50) NOT NULL ,
[datSent] [smalldatetime] NOT NULL ,
[strPC_IP] [varchar] (15) NOT NULL ,
[strProviderID] [varchar] (50) NOT NULL
CONSTRAINT [PK_tblVoiceFilesSent] PRIMARY KEY CLUSTERED
([strSentFileName])
) ON [PRIMARY]
GO
-- note that the FK constraint here may have to be removed.
-- We could (due to errors) get a confirmation on a file that was never
submitted
CREATE TABLE [dbo].[tblReceiptConfirmed] (
[strSentFileName] [varchar] (50) NOT NULL ,
[strJobID] [varchar] (10) NOT NULL ,
[strReturnFileName] [varchar] (50) NOT NULL ,
[datConfirmed] [smalldatetime] NOT NULL
CONSTRAINT [PK_tblReceiptConfirmed] PRIMARY KEY CLUSTERED
([strJobID]),
CONSTRAINT [FK_tblReceiptConfirmed_tblVoiceFilesSen
t] FOREIGN KEY
([strSentFileName])
REFERENCES [tblVoiceFilesSent] ([strSentFileName])
ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
-- note that the FK constraint here may have to be removed.
-- We could (due to errors) get a returned text file that was never
submitted, or never confirmed
CREATE TABLE [dbo].[tblTextFileReturned] (
[strJobID] [varchar] (10) NOT NULL ,
[datReturned] [smalldatetime] NOT NULL
CONSTRAINT [PK_tblTextFileReturned] PRIMARY KEY CLUSTERED
([strJobID]),
CONSTRAINT [FK_tblTextFileReturned_tblReceiptConfir
med] FOREIGN KEY
([strJobID])
REFERENCES [tblReceiptConfirmed] ([strJobID])
ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GOOn Thu, 16 Mar 2006 14:36:28 -0500, Jim Underwood wrote:

>I am looking for feedback on how to best design an auditing database for
>tracking the progress of a file through various applications and vendors.
>We need to be able to track delays in processing, as well as lost or missin
g
>files.
(snip description)
>Here is a description of the events and data we will be tracking:
>EVENT 1
>On our end the voice data file names are unique, and we have the following
>information available:
>1.1. file name (strSentFileName)
>1.2. IP address of the PC that submitted the file (strPC_ID)
>1.3. ID of the person who dictated the voice file (strProviderID)
>1.4. Date and time the file was submitted to the vendor (datSent)
>EVENT 2
>When the vendor receives the file, they send back to us a confirmation and
w
>e enter the following data:
>2.1. Original File Name (strSentFileName)
>2.2. New file name if applicable (strReturnFileName)
>2.3. Unique identifier from their system (strJobID)
>2.4. Date and time the confirmation was received (datConfirmed)
>EVENT 3
>When we receive the transcribed text file back from the vendor, we enter th
e
>following data:
>3.1. Unique identifier from their system (strJobID)
>3.2. Date and time the final text file was received (datReturned)
Hi Jim,
Is it also possible that any of the above attributes are changed later
(other than for the obvious reason of correcting an input error)? And if
they are ever changed, do you need to keep a history of the previously
entered data, and when and by who the data was changed?
If the answer is no, then I don't think you need a seperate audit table.
In fact, I wouldn't use the term auditing at all, since people associate
that term with a full logging of who changed what data when.
All the above are, in my eyes, attributes of your voice files. Some of
those attributes are related to events (e.g. DateSent, DateConfirmed,
DateReturned), some are not directly related to events (though you
happen to receive that bit of information exactly when an event
happens).
I think you can add all these attrbitues to your existing VoiceFiles
table (assuming that you have any).
Here's how I would design the table (I took the liberty of losing the
silly str-, dat-, and tbl-prefixes, adding primary key constraints and
NOT NULL constraints and improving some column names. I also added some
check constraints to ensure the sanity of the column contents).
Note: If the ID of the person who dictated the file is already known
when the VoiceFiule is entered in the DB (which I can imagine), then
change the DEFAULT NULL to NOT NULL for the relevant column.
CREATE TABLE VoiceFiles
(VoiceFileName varchar(50) NOT NULL,
-- NOTE: Replaces "strSentFileName" column
-- Columns not mentioned in this message but already present
-- in your current VoiceFiles table.
Sender_PC_IPAddr varchar(15) DEFAULT NULL,
Provider_ID varchar(10) DEFAULT NULL,
-- NOTE: I dislike the name, but the obvious
-- alternative ("dictator") is no good either
DateSentToVendor smalldatetime DEFAULT NULL,
FileNameFromVendor varchar(50) DEFAULT NULL,
VendorJobID varchar(10) DEFAULT NULL,
DateConfirmedByVendor smalldatetime DEFAULT NULL,
DateReturnedFromVendor smalldatetime DEFAULT NULL,
PRIMARY KEY (VoiceFileName),
FOREIGN KEY (Sender_PC_IPAddr) REFERENCES PCs,
FOREIGN KEY (Provider_D) REFERENCES Persons,
CHECK (CASE WHEN DateSentToVendor IS NULL
THEN CASE WHEN Sender_PC_IPAddr IS NULL
AND ProviderID IS NULL
AND DateConfirmedByVendor IS NOT NULL
THEN 'Good'
ELSE 'Bad'
END
ELSE CASE WHEN Sender_PC_IPAddr IS NOT NULL
AND ProviderID IS NOT NULL
THEN 'Good'
ELSE 'Bad'
END
END = 'Good')
CHECK (CASE WHEN DateConfirmedByVendor IS NULL
THEN CASE WHEN FileNameFromVendor IS NULL
AND VendorJobID IS NULL
AND DateReturnedFromVendor IS NOT NULL
THEN 'Good'
ELSE 'Bad'
END
ELSE CASE WHEN FileNameFromVendor IS NOT NULL
AND DateReturnedFromVendor IS NOT NULL
THEN 'Good'
ELSE 'Bad'
END
END = 'Good')
CHECK (DateConfirmedByVendor <= DateSentToVendor)
CHECK (DateReturnedFromVendor <= DateSentToVendor)
CHECK (DateReturnedFromVendor <= DateConfirmedByVendor)
)
I didn't add a UNIQUE constraint for VendorJobID, since SQL Server
insists on ignoring the standard and rejecting a second NULL entry in a
nullable column with UNIQUE constraint. There are workaround taht allow
you to create a constraint to mimic the way UNIQUE should behave; google
for them or ask in a follow-up message.
(snip)
>-- note that the FK constraint here may have to be removed.
>-- We could (due to errors) get a confirmation on a file that was never
>submitted
(snip)
>-- note that the FK constraint here may have to be removed.
>-- We could (due to errors) get a returned text file that was never
>submitted, or never confirmed
I recommend putting those confirmations and those returned text files in
a seperate table. After all, I expect (and hope!) that your company
won't handle these confirmations and returned text files the same way
that other confirmations and returned text files are handled.
Hugo Kornelis, SQL Server MVP|||Hugo,
Thanks for all your feedback. I think I will go with a separate table
(probably two) for any later transactions that occur without the presence of
an originating transaction. You are absolutely correct that these will not
be handled in the same way as the other (non-orphaned) transactions. Doing
this also alleviates my concerns about defining a primary key on
VoiceFileName, which was my main stumbling block with this setup.
I know what you are saying about the unique constraint with nulls. I
bookmarked a page or two regarding "nullbusters" when I first saw it, and I
think I may use a similar approach here.
The silly prefixes I am stuck with, unfortunately (internal development
standards) but I do find your column names to be more intuitive than what I
had scribbled down.
All in all your feedback has been a big help.
Thank you!
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:i3nr12hbps3nj374sof83ab758q0pc07be@.
4ax.com...
> On Thu, 16 Mar 2006 14:36:28 -0500, Jim Underwood wrote:
> Hi Jim,
> Is it also possible that any of the above attributes are changed later
> (other than for the obvious reason of correcting an input error)? And if
> they are ever changed, do you need to keep a history of the previously
> entered data, and when and by who the data was changed?
> If the answer is no, then I don't think you need a seperate audit table.
> In fact, I wouldn't use the term auditing at all, since people associate
> that term with a full logging of who changed what data when.
> All the above are, in my eyes, attributes of your voice files. Some of
> those attributes are related to events (e.g. DateSent, DateConfirmed,
> DateReturned), some are not directly related to events (though you
> happen to receive that bit of information exactly when an event
> happens).
> I think you can add all these attrbitues to your existing VoiceFiles
> table (assuming that you have any).
> Here's how I would design the table (I took the liberty of losing the
> silly str-, dat-, and tbl-prefixes, adding primary key constraints and
> NOT NULL constraints and improving some column names. I also added some
> check constraints to ensure the sanity of the column contents).
> Note: If the ID of the person who dictated the file is already known
> when the VoiceFiule is entered in the DB (which I can imagine), then
> change the DEFAULT NULL to NOT NULL for the relevant column.
> CREATE TABLE VoiceFiles
> (VoiceFileName varchar(50) NOT NULL,
> -- NOTE: Replaces "strSentFileName" column
> -- Columns not mentioned in this message but already present
> -- in your current VoiceFiles table.
> Sender_PC_IPAddr varchar(15) DEFAULT NULL,
> Provider_ID varchar(10) DEFAULT NULL,
> -- NOTE: I dislike the name, but the obvious
> -- alternative ("dictator") is no good either
> DateSentToVendor smalldatetime DEFAULT NULL,
> FileNameFromVendor varchar(50) DEFAULT NULL,
> VendorJobID varchar(10) DEFAULT NULL,
> DateConfirmedByVendor smalldatetime DEFAULT NULL,
> DateReturnedFromVendor smalldatetime DEFAULT NULL,
> PRIMARY KEY (VoiceFileName),
> FOREIGN KEY (Sender_PC_IPAddr) REFERENCES PCs,
> FOREIGN KEY (Provider_D) REFERENCES Persons,
> CHECK (CASE WHEN DateSentToVendor IS NULL
> THEN CASE WHEN Sender_PC_IPAddr IS NULL
> AND ProviderID IS NULL
> AND DateConfirmedByVendor IS NOT NULL
> THEN 'Good'
> ELSE 'Bad'
> END
> ELSE CASE WHEN Sender_PC_IPAddr IS NOT NULL
> AND ProviderID IS NOT NULL
> THEN 'Good'
> ELSE 'Bad'
> END
> END = 'Good')
> CHECK (CASE WHEN DateConfirmedByVendor IS NULL
> THEN CASE WHEN FileNameFromVendor IS NULL
> AND VendorJobID IS NULL
> AND DateReturnedFromVendor IS NOT NULL
> THEN 'Good'
> ELSE 'Bad'
> END
> ELSE CASE WHEN FileNameFromVendor IS NOT NULL
> AND DateReturnedFromVendor IS NOT NULL
> THEN 'Good'
> ELSE 'Bad'
> END
> END = 'Good')
> CHECK (DateConfirmedByVendor <= DateSentToVendor)
> CHECK (DateReturnedFromVendor <= DateSentToVendor)
> CHECK (DateReturnedFromVendor <= DateConfirmedByVendor)
> )
> I didn't add a UNIQUE constraint for VendorJobID, since SQL Server
> insists on ignoring the standard and rejecting a second NULL entry in a
> nullable column with UNIQUE constraint. There are workaround taht allow
> you to create a constraint to mimic the way UNIQUE should behave; google
> for them or ask in a follow-up message.
> (snip)
> (snip)
> I recommend putting those confirmations and those returned text files in
> a seperate table. After all, I expect (and hope!) that your company
> won't handle these confirmations and returned text files the same way
> that other confirmations and returned text files are handled.
> --
> Hugo Kornelis, SQL Server MVP