"Steve Zimmelman" <skz@charter.nospam.net> wrote in message
news:ep7wCVaWGHA.1220@TK2MSFTNGP02.phx.gbl...
> Thanks.
>
> I tried your suggestion but it apparently doesn't work when you use a
computed
> column in a constraint.  When I attempt to add a record I get the errror:
> [INSERT failed because the following SET options have incorrect settings:
> 'ARITHABORT']
>
> Using SET ARITHABORT ON before the insert/update works, but it seems it
must be
> issued before each update/insert statement.
>
> > If this does not help, please post proper DDL and maybe sample data.
>
> CREATE TABLE [dbo].[ResRxDispensed] (
>  [ID] [int] IDENTITY (1, 1) NOT NULL ,
>  [ResID] [int] NULL ,
>  [ResRxID] [int] NULL ,
>  [RxDayID] [int] NULL ,
>  [RxDoseTimeID] [int] NULL ,
>  [DispDateTime] [datetime] NULL ,
>  [Created] [datetime] NULL ,
>  [StaffID] [int] NULL ,
>  [DispDate] AS (convert(char(8),[DispDateTime],112))
> ) ON [PRIMARY]
>
>
> Insert Into ResRxDispensed
>    ([ResID],[ResRxID],[RxDayID],[RxDoseTimeID],[DispDateTime])
>  Values
>    (1,1,1,1,{ts '2006-04-06 09:30:00'}) ;
>
> The insert should fail if another record has
>    ResID = 1
>    RxDayID = 1
>    RxDoseTimeID = 1
>    DispDateTime = 2006-04-06 (with any time factor)
>
> Updates should also fail if the update changes the record to match another
> record with the criteria.
>
> -Steve-
>
>
>

"Steve Zimmelman" <skz@charter.nospam.net> wrote in message
news:%23Set$paWGHA.2080@TK2MSFTNGP05.phx.gbl...
>> This worked fine for me on SQL 2000.
>
> Hi Jim,
>
> Thanks. I'm using SQL 2000, but I'm still getting the same error.
>
> Here's the scripts for creating everything I'm using.
>
> CREATE TABLE [dbo].[ResRxDispensed] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ResID] [int] NULL ,
> [ResRxID] [int] NULL ,
> [RxDayID] [int] NULL ,
> [RxDoseTimeID] [int] NULL ,
> [DispDateTime] [datetime] NOT NULL ,
> [Created] [datetime] NULL ,
> [StaffID] [int] NULL ,
> [DispDate] AS (convert(char(8),[DispDateTime],112))
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[ResRxDispensed] WITH NOCHECK ADD
> CONSTRAINT [PK_ResRxDispensed] PRIMARY KEY CLUSTERED
> (
>  [ID]
> )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[ResRxDispensed] ADD
> CONSTRAINT [DF_ResRxDispensed_DispDateTime] DEFAULT (getdate()) FOR [DispDateTime],
> CONSTRAINT [DF_ResRxDispensed_Created] DEFAULT (getdate()) FOR [Created],
> CONSTRAINT [IX_ResRxDispensed] UNIQUE NONCLUSTERED
> (
>  [ResID],
>  [RxDayID],
>  [RxDoseTimeID],
>  [DispDate]
> )  ON [PRIMARY]
> GO
>
> CREATE  INDEX [ResRxDispensed_ResID] ON [dbo].[ResRxDispensed]([ResID]) ON [PRIMARY]
> GO
>
> CREATE  INDEX [ResRxDispensed_ResRxID] ON [dbo].[ResRxDispensed]([ResRxID]) ON [PRIMARY]
> GO
>
> CREATE  INDEX [ResRxDispensed_RxDayID] ON [dbo].[ResRxDispensed]([RxDayID]) ON [PRIMARY]
> GO
>
> CREATE  INDEX [ResRxDispensed_RxDoseTimeID] ON [dbo].[ResRxDispensed]([RxDoseTimeID]) ON [PRIMARY]
> GO
>
>
> -Steve-
>

"Steve Zimmelman" <skz@charter.nospam.net> wrote in message
news:eGzuDCbWGHA.1196@TK2MSFTNGP03.phx.gbl...
> "Tibor Karaszi" wrote:
>> Is there a reason why you can't add this SET option to your stored procedures?
>
> This procedure, when used, produces the same error.
>  [INSERT failed because the following SET options have incorrect settings: 'ARITHABORT']
>
> I'm at a loss how to proceed...
>
> -Steve-
>
> Exec NewResRxDispensed_SP 1,1,1,1,{ts '2006-05-13 09:30:00'},24
>
> Create Procedure [dbo].[NewResRxDispensed_SP]
>  @ResID int,
>  @ResRxID int,
>  @RxDayID int,
>  @RxDoseTimeID int,
>  @DispDateTime datetime,
>  @StaffID int
> As
> SET ARITHABORT ON
> Insert Into [ResRxDispensed]
> ([ResID],
>  [ResRxID],
>  [RxDayID],
>  [RxDoseTimeID],
>  [DispDateTime],
>  [StaffID])
> Values
> (@ResID,
>  @ResRxID,
>  @RxDayID,
>  @RxDoseTimeID,
>  @DispDateTime,
>  @StaffID)
> /*** Return New Int ID [ID] ***/
> Select SCOPE_IDENTITY() As NewID
>
>
>
>

"Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in
message news:%23bq0wnhWGHA.4324@TK2MSFTNGP03.phx.gbl...
> That is strange. I executed the DDL posted a few posts ago, and then created
> the procedure you posted and then executed the procedure. No error message. I
> did this on both SQL2K sp3 and SQL2005. Perhaps you can try the same (in
> tempdb or a new database) just to rule out other factors?
>
> --
> Tibor Karaszi, SQL Server MVP

"Steve Zimmelman" <skz@charter.nospam.net> wrote in message
news:%23kFjHVZWGHA.2180@TK2MSFTNGP02.phx.gbl...
> I'm having a little trouble enforcing a unique record rule.  The problem
> is a DateTime field is used in the unique value, but I only need to the
> DatePart (not the time), otherwise I would use a standard unique
> constraint.
>
> I need the combination of ResID,RxDayID,RxTimeID, and Just The Date of
> DispDateTime to be unique.
>
> The following works for inserts but not updates if a record already
> exists.  It does work if there are 2 matching records for an update.  I'd
> rather have the back-end enforce the rules, but if I must I'll build it
> into the application. Is there a function or a method I'm over-looking?
>
> ALTER TABLE [dbo].[ResRxDispensed] ADD
> CONSTRAINT [CK_ResRxDispensed_Unique]
>  CHECK ([dbo].[DispRecordUnique_FN](
>         [ResID],
>         [RxDayID],
>         [RxDoseTimeID],
>         [DispDateTime]) = 'Y')
>
>
> Create Function [dbo].[DispRecordUnique_FN](
>              @ResID Int,
>              @RxDayID Int,
>              @RxTimeID Int,
>              @ADate DateTime)
> Returns VarChar(1)
> Begin
>   Declare @TheDate DateTime, @Rtn Varchar(1),@Cnt Int
>   Set @TheDate = Cast(Cast(@ADate as Char(11)) As DateTime)
>   Set @Rtn = 'N'
>   Select @Cnt = Count(*)
>       From ResRxDispensed
>       Where ResID = @ResID
>         And RxDayID = @RxDayID
>         And RxDoseTimeID = @RxTimeID
>         And (DispDateTime >= @TheDate And DispDateTime <= (@TheDate+1))
>   If (@Cnt <= 1)
>   Begin
>      Set @Rtn = 'Y'
>   End
>   Return @Rtn
> End
>
> TIA,
> -Steve-
>