I'm encountering a very wierd problem. I have the following table:
CREATE TABLE [MyTable] (
[RowID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemID] [int] NOT NULL ,
[Start] [datetime] NOT NULL ,
[OwnerID] [int] NULL ,
[End] [datetime] NOT NULL ,
[BitColumn] [bit] NULL
)
The primary key is on the ItemID and Start columns:
ALTER TABLE [dbo].[MyTable] WITH NOCHECK ADD
CONSTRAINT [PK_MyTableRef] PRIMARY KEY CLUSTERED
(
[ItemID],
[Start]
) ON [PRIMARY]
GO
Assuming I already have the following data in the table:
1 | 1 | 2/4/2004 10:00:00 | 1 | 12/31/9999 | 0
What my query basically does is:
1) Update the end field according to a parameter.
2) Insert a new record into the table:
ItemID = 1, Start = <Start_Parameter>, OwnerID = <New Owner>, End =
<End_Parameter>, BitColumn = 0
3) Insert a new record into the table:
ItemID = 1, Start = <End_Parameter>, OwnerID = <Original Owner>,
End = 12/31/9999, BitColumn = 0
Here's an example:
UPDATE MyTable SET [End] = '2/4/2004 15:00:00' WHERE ItemID = 1 AND
OwnerID = 1
INSERT MyTable (ItemID,[Start],OwnerID,[End]) VALUES (1,'2/4/2004
15:00:00',2,'2/4/2004 20:00:00')
INSERT MyTable (ItemID,[Start],OwnerID,[End]) VALUES (1, '2/4/2004
20:00:00',1,'12/31/9999')
When I run this code, I get the 2627 error, HOWEVER, the last insert
still executes, and the new row is inserted into the database...
One more strange behavior, some times the first set of 3 queries
works... but when I run it again (with different parameters - i.e.
advance the start/end parameters) I get the error on the second
execution...
I've read some posts that talk about a bug in SQL Server that causes
this problem, but I'm pretty sure that this is not the case.
Can someone please explain to me why I'm getting this error?!
Especially when I'm not inserting a duplicate value into BOTH columns,
or does the constraint check each column separately?
Any help would be appreciated.
Regards,
APSQL Server does not terminate a batch/stored procedure because of a constrai
nt failure. You need
error handling and possibly also transaction handling in your code...
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
<asaf.peleg@.mtsint.com> wrote in message news:bb3341b4.0402040258.1cb8b748@.posting.google.com...[Q
UOTE]
> Hi all,
> I'm encountering a very wierd problem. I have the following table:
> CREATE TABLE [MyTable] (
> [RowID] [int] IDENTITY (1, 1) NOT NULL ,
> [ItemID] [int] NOT NULL ,
> [Start] [datetime] NOT NULL ,
> [OwnerID] [int] NULL ,
> [End] [datetime] NOT NULL ,
> [BitColumn] [bit] NULL
> )
> The primary key is on the ItemID and Start columns:
> ALTER TABLE [dbo].[MyTable] WITH NOCHECK ADD
> CONSTRAINT [PK_MyTableRef] PRIMARY KEY CLUSTERED
> (
> [ItemID],
> [Start]
> ) ON [PRIMARY]
> GO
>
> Assuming I already have the following data in the table:
> 1 | 1 | 2/4/2004 10:00:00 | 1 | 12/31/9999 | 0
> What my query basically does is:
> 1) Update the end field according to a parameter.
> 2) Insert a new record into the table:
> ItemID = 1, Start = <Start_Parameter>, OwnerID = <New Owner>, End =
> <End_Parameter>, BitColumn = 0
> 3) Insert a new record into the table:
> ItemID = 1, Start = <End_Parameter>, OwnerID = <Original Owner>,
> End = 12/31/9999, BitColumn = 0
> Here's an example:
> UPDATE MyTable SET [End] = '2/4/2004 15:00:00' WHERE ItemID = 1 AND
> OwnerID = 1
> INSERT MyTable (ItemID,[Start],OwnerID,[End]) VALUES (1,'2/4/2004
> 15:00:00',2,'2/4/2004 20:00:00')
> INSERT MyTable (ItemID,[Start],OwnerID,[End]) VALUES (1, '2/4/2004
> 20:00:00',1,'12/31/9999')
>
> When I run this code, I get the 2627 error, HOWEVER, the last insert
> still executes, and the new row is inserted into the database...
> One more strange behavior, some times the first set of 3 queries
> works... but when I run it again (with different parameters - i.e.
> advance the start/end parameters) I get the error on the second
> execution...
> I've read some posts that talk about a bug in SQL Server that causes
> this problem, but I'm pretty sure that this is not the case.
> Can someone please explain to me why I'm getting this error?!
> Especially when I'm not inserting a duplicate value into BOTH columns,
> or does the constraint check each column separately?
> Any help would be appreciated.
> Regards,
> AP[/QUOTE]|||The error handling Tibor mentions might look like
declare @.error int
Begin tran
insert ...
select @.error = @.@.error --Save off the error
if @.error != 0
begin
raiserror('Tell someone about it, 16,1)
rollback tran
return --if you are in an sp
end
next insert ...
same kind of error handling
commit tran
<asaf.peleg@.mtsint.com> wrote in message
news:bb3341b4.0402040258.1cb8b748@.posting.google.com...
quote:
> Hi all,
> I'm encountering a very wierd problem. I have the following table:
> CREATE TABLE [MyTable] (
> [RowID] [int] IDENTITY (1, 1) NOT NULL ,
> [ItemID] [int] NOT NULL ,
> [Start] [datetime] NOT NULL ,
> [OwnerID] [int] NULL ,
> [End] [datetime] NOT NULL ,
> [BitColumn] [bit] NULL
> )
> The primary key is on the ItemID and Start columns:
> ALTER TABLE [dbo].[MyTable] WITH NOCHECK ADD
> CONSTRAINT [PK_MyTableRef] PRIMARY KEY CLUSTERED
> (
> [ItemID],
> [Start]
> ) ON [PRIMARY]
> GO
>
> Assuming I already have the following data in the table:
> 1 | 1 | 2/4/2004 10:00:00 | 1 | 12/31/9999 | 0
> What my query basically does is:
> 1) Update the end field according to a parameter.
> 2) Insert a new record into the table:
> ItemID = 1, Start = <Start_Parameter>, OwnerID = <New Owner>, End =
> <End_Parameter>, BitColumn = 0
> 3) Insert a new record into the table:
> ItemID = 1, Start = <End_Parameter>, OwnerID = <Original Owner>,
> End = 12/31/9999, BitColumn = 0
> Here's an example:
> UPDATE MyTable SET [End] = '2/4/2004 15:00:00' WHERE ItemID = 1 AND
> OwnerID = 1
> INSERT MyTable (ItemID,[Start],OwnerID,[End]) VALUES (1,'2/4/2004
> 15:00:00',2,'2/4/2004 20:00:00')
> INSERT MyTable (ItemID,[Start],OwnerID,[End]) VALUES (1, '2/4/2004
> 20:00:00',1,'12/31/9999')
>
> When I run this code, I get the 2627 error, HOWEVER, the last insert
> still executes, and the new row is inserted into the database...
> One more strange behavior, some times the first set of 3 queries
> works... but when I run it again (with different parameters - i.e.
> advance the start/end parameters) I get the error on the second
> execution...
> I've read some posts that talk about a bug in SQL Server that causes
> this problem, but I'm pretty sure that this is not the case.
> Can someone please explain to me why I'm getting this error?!
> Especially when I'm not inserting a duplicate value into BOTH columns,
> or does the constraint check each column separately?
> Any help would be appreciated.
> Regards,
> AP
No comments:
Post a Comment