I'm trying to control the accidental entry of special characters (carriage
return) from getting into my data. I thought I could write a check
constraint like what follows
[name] <> '%' + char(13) + '%'
but this doesn't work. Tried several permutations but the carriage return
is always accepted when I enter the data through an access database table
view (control + enter).
If I can get the expression working I think the best way to implement would
be either a user defined datatype or a rule bound to the column.
Has anyone done this sort of thing, or know how?
Thanks
David LHow about
CharIndex(Char(13), [Name], 1) = 0
Or
[Name] Not Like '%' + Char(13) + '%'
Thomas
"DavinciCoder" <dal@.rlpi.com> wrote in message
news:uqpvUvuaFHA.2664@.TK2MSFTNGP15.phx.gbl...
> I'm trying to control the accidental entry of special characters (carriage
> return) from getting into my data. I thought I could write a check constr
aint
> like what follows
> [name] <> '%' + char(13) + '%'
> but this doesn't work. Tried several permutations but the carriage return
is
> always accepted when I enter the data through an access database table vie
w
> (control + enter).
> If I can get the expression working I think the best way to implement woul
d be
> either a user defined datatype or a rule bound to the column.
> Has anyone done this sort of thing, or know how?
> Thanks
>
> --
> David L
>|||Ok, the second works great thanks
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:eh1KX0uaFHA.1152@.tk2msftngp13.phx.gbl...
> How about
> CharIndex(Char(13), [Name], 1) = 0
> Or
> [Name] Not Like '%' + Char(13) + '%'
>
> Thomas
>
> "DavinciCoder" <dal@.rlpi.com> wrote in message
> news:uqpvUvuaFHA.2664@.TK2MSFTNGP15.phx.gbl...
>|||David
You may want to check CHAR(10) as well.
"DavinciCoder" <dal@.rlpi.com> wrote in message
news:%23X6if6uaFHA.2884@.tk2msftngp13.phx.gbl...
> Ok, the second works great thanks
>
> "Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
> news:eh1KX0uaFHA.1152@.tk2msftngp13.phx.gbl...
>
Showing posts with label rule. Show all posts
Showing posts with label rule. Show all posts
Sunday, March 11, 2012
Thursday, March 8, 2012
Check Constraint
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 th
e
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-You've overcomplicated the solution IMHO:
1) create a computed column:
alter table dbo.ResRxDispensed
add DispDate as convert(char(8), DispDateTime, 112)
go
2) create the unique constraint:
alter table dbo.ResRxDispensed
add constraint <constraint name>
unique (ResID, RxDayID, RxTimeID, DispDate)
go
If this does not help, please post proper DDL and maybe sample data.
ML
http://milambda.blogspot.com/|||Experiment to see if the year(), month(), day() or datediff() functions
would work here.
"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-
>|||Thanks.
I tried your suggestion but it apparently doesn't work when you use a comput
ed
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],[RxDoseTime
ID],[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-|||
"JT" <someone@.microsoft.com> wrote in message
news:%23sosFSaWGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Experiment to see if the year(), month(), day() or datediff() functions wo
uld
> work here.
I'm not sure I understand. Experiment how? The function
DispRecordUnique_FN()works, but using it in a Check constraint only works on
Inserts, not updates.
-Steve-|||This worked fine for me on SQL 2000.
CREATE TABLE [dbo].[TestTable] (
[ResID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RxDayID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RxDoseTimeID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DispDateTime] [datetime] NOT NULL ,
[OtherData] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DispDate] AS (convert(char(8),[DispDateTime],112))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestTable] ADD
CONSTRAINT [IX_TestTable] UNIQUE NONCLUSTERED
(
[ResID],
[RxDoseTimeID],
[RxDayID],
[DispDate]
) ON [PRIMARY]
GO
"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.
>
> 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],[RxDoseTime
ID],[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-
>
>|||> 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-|||Is there a reason why you can't add this SET option to your stored procedure
s?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:%23Set$paWGHA.2080@.TK2MSFTNGP05.phx.gbl...
> 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-
>|||"Tibor Karaszi" wrote
> Is there a reason why you can't add this SET option to your stored procedures?[/co
lor]
I suppose not. I just wanted something that didn't require such special
handling for updates in case I needed to do some manual repair/entry outside
of
the application.
-Steve-|||"Tibor Karaszi" wrote:
> Is there a reason why you can't add this SET option to your stored procedures?[/co
lor]
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
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 th
e
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-You've overcomplicated the solution IMHO:
1) create a computed column:
alter table dbo.ResRxDispensed
add DispDate as convert(char(8), DispDateTime, 112)
go
2) create the unique constraint:
alter table dbo.ResRxDispensed
add constraint <constraint name>
unique (ResID, RxDayID, RxTimeID, DispDate)
go
If this does not help, please post proper DDL and maybe sample data.
ML
http://milambda.blogspot.com/|||Experiment to see if the year(), month(), day() or datediff() functions
would work here.
"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-
>|||Thanks.
I tried your suggestion but it apparently doesn't work when you use a comput
ed
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],[RxDoseTime
ID],[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-|||
"JT" <someone@.microsoft.com> wrote in message
news:%23sosFSaWGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Experiment to see if the year(), month(), day() or datediff() functions wo
uld
> work here.
I'm not sure I understand. Experiment how? The function
DispRecordUnique_FN()works, but using it in a Check constraint only works on
Inserts, not updates.
-Steve-|||This worked fine for me on SQL 2000.
CREATE TABLE [dbo].[TestTable] (
[ResID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RxDayID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RxDoseTimeID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DispDateTime] [datetime] NOT NULL ,
[OtherData] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DispDate] AS (convert(char(8),[DispDateTime],112))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestTable] ADD
CONSTRAINT [IX_TestTable] UNIQUE NONCLUSTERED
(
[ResID],
[RxDoseTimeID],
[RxDayID],
[DispDate]
) ON [PRIMARY]
GO
"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.
>
> 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],[RxDoseTime
ID],[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-
>
>|||> 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-|||Is there a reason why you can't add this SET option to your stored procedure
s?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:%23Set$paWGHA.2080@.TK2MSFTNGP05.phx.gbl...
> 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-
>|||"Tibor Karaszi" wrote
> Is there a reason why you can't add this SET option to your stored procedures?[/co
lor]
I suppose not. I just wanted something that didn't require such special
handling for updates in case I needed to do some manual repair/entry outside
of
the application.
-Steve-|||"Tibor Karaszi" wrote:
> Is there a reason why you can't add this SET option to your stored procedures?[/co
lor]
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
Tuesday, February 14, 2012
char(1) vs smallint
I'm having a disagreement with a fellow developer regarding flags. I prefer to declare flag columns as smallint and apply a rule restricting the values to 1 or 0. He prefers to use a char(1) with a rule restricting the values to 'Y' or 'N'. Can anyone give me ammunition against the char(1) or tell me if I'm wrong. Oh yeah, the flag is cast to a boolean in the app written in java, if that makes a difference.Y/N is convenient if users will be querying the database directly, but if you are performing any aggregations then 1/0 allows you to more easily sum the number of flagged records. The percentage flagged, for instance, is just Sum(Flag)/Count(*). (Note that the BIT type won't work with most aggregate functions, otherwise it would be the boolean type of choice.)
You do need to be careful with 1/0 to make sure other applications interpret it correctly. Under some systems TRUE = -1 and FALSE = 0, and other situations are possible.
blindman|||Originally posted by peterlemonjello
I'm having a disagreement with a fellow developer regarding flags. I prefer to declare flag columns as smallint and apply a rule restricting the values to 1 or 0. He prefers to use a char(1) with a rule restricting the values to 'Y' or 'N'. Can anyone give me ammunition against the char(1) or tell me if I'm wrong. Oh yeah, the flag is cast to a boolean in the app written in java, if that makes a difference.
Usually developers know much more than dbas ;). I am using tinyint for flags.|||Thanx blindman and snail! Unfortunately, I'm a developer that new way too much about databases and sql server so I'm a dba now too. As a developer I always said the only thing worse than a dba is a object oriented developer turned dba, guess I'm eating my own words... LOL!!!|||actually, the only thing worse than a dba is a data architect or data modeller like me with years (decades, actually) of modelling and sql language experience, who couldn't solve a performance problem to save his life other than perhaps declaring the obvious indexes...
performance issues aside, you have to look at the implications of your design on the sql to solve business problems
blindman had a superb example -- sum(flag)/count(*)
that's the type of thing a modeller knows, that a dba might not
tinyint (or smallint) is also good because it's a lot more portable across database platforms than boolean
rudy
http://r937.com/|||Those with experience in small shops that required both development and admin duties know best! :D
...but I also think my experience in object-oriented development has helped me develop modular database applications. There is no such thing as bad experience, just people who can't see beyond their own particular project scope.
blindman
You do need to be careful with 1/0 to make sure other applications interpret it correctly. Under some systems TRUE = -1 and FALSE = 0, and other situations are possible.
blindman|||Originally posted by peterlemonjello
I'm having a disagreement with a fellow developer regarding flags. I prefer to declare flag columns as smallint and apply a rule restricting the values to 1 or 0. He prefers to use a char(1) with a rule restricting the values to 'Y' or 'N'. Can anyone give me ammunition against the char(1) or tell me if I'm wrong. Oh yeah, the flag is cast to a boolean in the app written in java, if that makes a difference.
Usually developers know much more than dbas ;). I am using tinyint for flags.|||Thanx blindman and snail! Unfortunately, I'm a developer that new way too much about databases and sql server so I'm a dba now too. As a developer I always said the only thing worse than a dba is a object oriented developer turned dba, guess I'm eating my own words... LOL!!!|||actually, the only thing worse than a dba is a data architect or data modeller like me with years (decades, actually) of modelling and sql language experience, who couldn't solve a performance problem to save his life other than perhaps declaring the obvious indexes...
performance issues aside, you have to look at the implications of your design on the sql to solve business problems
blindman had a superb example -- sum(flag)/count(*)
that's the type of thing a modeller knows, that a dba might not
tinyint (or smallint) is also good because it's a lot more portable across database platforms than boolean
rudy
http://r937.com/|||Those with experience in small shops that required both development and admin duties know best! :D
...but I also think my experience in object-oriented development has helped me develop modular database applications. There is no such thing as bad experience, just people who can't see beyond their own particular project scope.
blindman
Subscribe to:
Posts (Atom)