May 8, 2007 at 1:16 pm
I am writing a script to import data from a client’s onsite server to our production server. The source and destination db schemas are the same. In the case of one table I found that only 1 of 20 of the source records imported (no error message tho). When I look at those 20 records, the only difference I see in the one record that imported correctly is that it has a value in the ‘UpdatedBy’ field and the other 19 are null (both source and destination tables allow null in this field).
To verify that this was related to the problem, I manually tried to replace the null in one of the other records with some other value, which gave me the error “The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.“
This led me to discover that the table has no primary key. The table does have a field essentially used as a primary key (integer, manually incremented) which makes the rows unique so I don’t see how nulls in an unrelated field would matter.
Can anyone explain this behavior and give me a tip on how to force all the records to insert? To further complicate things, the table has an ‘INSTEAD OF INSERT Not For Replication’ trigger, but it’s very simple and I don’t see anything there that would cause a problem.
Let me know if anyone wants to see the code, it’s pretty simple and straightforward.
May 8, 2007 at 2:47 pm
is there a unique index on the table?
May 8, 2007 at 2:56 pm
Yes, on a uniqueidentifier column named 'rowguid'. Default value is newid()
AgeIDsmallint
Hosp_IDvarchar(20)
Age_Unitvarchar(6)null
Activebit null
UpdateTimeStampdatetimenull
UpdatedByvarchar(10)null
rowguiduniqueidentifier
May 9, 2007 at 6:43 am
Sure sounds like the trigger may be affecting the data. Can you, without violating business integrity, post the table definition and trigger? Also, you mentioned the incrementing id value? Is that run from the trigger?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 9, 2007 at 6:55 am
The table def is in above post (let me know if you need more). And yes, the incrementing id is done from the trigger if the value comes in as 0, which it does not in my case. Here is the trigger:
ALTER TRIGGER [hpl].[TI_Age_Unit_Insert_Trigger] on [hpl].[Age_Unit]
INSTEAD OF INSERT Not For Replication
AS
BEGIN
declare
@AgeID smallint,
@Hosp_IDvarchar(20),
@Age_Unit varchar(6),
@Active bit,
@UpdateTimeStamp datetime,
@UpdatedBy varchar(10)
select
@AgeID = ins.AgeID ,
@Hosp_ID= ins.Hosp_ID ,
@Age_Unit = ins.Age_Unit ,
@Active = ins.Active ,
@UpdateTimeStamp = DATEADD(hour,-6,GETUTCDATE()),
@UpdatedBy = ins.UpdatedBy
from inserted ins
if @AgeID = 0
EXECUTE hpl.get_ID_Number 'Age_Unit', @Next_ID_Num = @AgeID OUTPUT
insert into hpl.Age_Unit
(
AgeID ,
Hosp_ID ,
Age_Unit ,
Active ,
UpdateTimeStamp ,
UpdatedBy )
values
( @AgeID ,
@Hosp_ID ,
@Age_Unit ,
@Active ,
@UpdateTimeStamp ,
@UpdatedBy )
END
May 9, 2007 at 9:24 am
Are you sure there is not an update trigger in there somewhere? Nothing posted accounts for the error message you got when updating the old record.
Additionally, how are you moving the data (DTS, Insert/Select, etc)? Your selection criteria could be the problem and it's not pulling the null data (not an insert problem at all).
Post complete DDL (if available) for the table to include Triggers/constraints. Also if possible post the SQL being used to move the data.
James.
May 9, 2007 at 9:28 am
Total agreement. There's a constraint you're not seeing or there's a trigger you're not seeing or, probably, both.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 9, 2007 at 10:15 am
There is an Update trigger, but it just updates the timestamp.
Here is the DDL- (see following post for my insert code)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [hpl].[Age_Unit](
[AgeID] [smallint] NOT NULL,
[Hosp_ID] [varchar](20) NOT NULL CONSTRAINT [DF_Age_Unit_Hosp_ID] DEFAULT ('0002'),
[Age_Unit] [varchar](6) NULL,
[Active] [bit] NULL,
[UpdateTimeStamp] [datetime] NULL CONSTRAINT [DF__Age_Unit__Update__04EFA97D] DEFAULT (dateadd(hour,(-6),getutcdate())),
[UpdatedBy] [varchar](10) NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF__Age_Unit__rowgui__0D3C6D69] DEFAULT (newid())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE CLUSTERED INDEX [ix_HospID] ON [hpl].[Age_Unit]
(
[Hosp_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [index_415340544] ON [hpl].[Age_Unit]
(
[rowguid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Trigger [TU_Age_Unit_Update_Trigger] Script Date: 05/09/2007 11:08:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/************* Age_Unit Update trigger *********/
CREATE TRIGGER [hpl].[TU_Age_Unit_Update_Trigger] on [hpl].[Age_Unit]
For Update Not For Replication
AS
BEGIN
declare
@AgeID smallint
select
@AgeID = ins.AgeID
from inserted ins
update hpl.Age_Unit
set UpdateTimeStamp = DATEADD(hour,-6,GETUTCDATE())
where AgeID = @AgeID
END
GO
/****** Object: Trigger [TI_Age_Unit_Insert_Trigger] Script Date: 05/09/2007 11:08:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******** Age_Unit Insert trigger *********/
CREATE TRIGGER [hpl].[TI_Age_Unit_Insert_Trigger] on [hpl].[Age_Unit]
INSTEAD OF INSERT Not For Replication
AS
BEGIN
declare
@AgeID smallint,
@Hosp_IDvarchar(20),
@Age_Unit varchar(6),
@Active bit,
@UpdateTimeStamp datetime,
@UpdatedBy varchar(10)
select
@AgeID = ins.AgeID ,
@Hosp_ID= ins.Hosp_ID ,
@Age_Unit = ins.Age_Unit ,
@Active = ins.Active ,
@UpdateTimeStamp = DATEADD(hour,-6,GETUTCDATE()),
@UpdatedBy = ins.UpdatedBy
from inserted ins
if @AgeID = 0
EXECUTE hpl.get_ID_Number 'Age_Unit', @Next_ID_Num = @AgeID OUTPUT
insert into hpl.Age_Unit
(
AgeID ,
Hosp_ID ,
Age_Unit ,
Active ,
UpdateTimeStamp ,
UpdatedBy )
values
( @AgeID ,
@Hosp_ID ,
@Age_Unit ,
@Active ,
@UpdateTimeStamp ,
@UpdatedBy )
END
GO
May 9, 2007 at 10:22 am
Here is my SQL to do the insert-
DECLARE @maxNum int
DECLARE @increment smallint
DECLARE @IdsAgeUnit table (idOld int, idNew int)
SELECT@maxNum = max_num, @increment = increment
FROM StefHpl_prod.hpl.Table_List WHERE Table_Name = 'Age_Unit'
INSERT INTO @IdsAgeUnit (idOld) SELECT AgeId FROM StefScripps.hpl.Age_Unit ORDER BY AgeId
UPDATE @IdsAgeUnit SET @maxNum = idNew = @maxNum + @increment--too bad IDENTITY doesn't allow variables as the seed and increment...
INSERT INTO [StefHpl_prod].[hpl].[Age_Unit]
([AgeID]
,[Hosp_ID]
,[Age_Unit]
,[Active]
,[UpdateTimeStamp]
,[UpdatedBy])
SELECT I.idNew AS AgeID
,[Hosp_ID]
,[Age_Unit]
,[Active]
,[UpdateTimeStamp]
,[UpdatedBy]
FROM [StefScripps].[hpl].Age_Unit A
JOIN @IdsAgeUnit AS I ON A.AgeId = I.idOld
UPDATE StefHpl_prod.hpl.Table_List SET max_num = @maxNum where Table_Name = 'Age_Unit'
May 9, 2007 at 10:49 am
I don't know. I copied down all your code and walked through it. It's working fine. No issues. No errors. I was able to insert values using your script (with a couple of changes areound the Table_list & id generation proc) with null values in updatedby. I then updated the UpdateBy column that was null. No problem. I updated UpdatedBy columns that were not null and set them NULL. Again, no issue. I still think there's a trigger somewhere, maybe on the Table_list table, I don't know, that is causing this problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 9, 2007 at 10:58 am
Hmmm, weird. I am going to go back and try to figure out why i can't update the UpdatedBy column. This db was created by consultants who are long gone, and it is generally acknowledged now that they didn't know what they were doing (and the design phase for the re-write is nearing completion- yeah!).
Here is the DDL for table Table_list, but i don't see anything that would cause this.
CREATE TABLE [hpl].[Table_List](
[Table_List_ID] [smallint] IDENTITY(1,1) NOT NULL,
[Table_Name] [varchar](30) NULL,
[Max_Num] [int] NULL,
[increment] [int] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()),
PRIMARY KEY CLUSTERED
(
[Table_List_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE UNIQUE NONCLUSTERED INDEX [index_1273771595] ON [hpl].[Table_List]
(
[rowguid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
May 9, 2007 at 11:03 am
Also, I wrote the code to determine the next AgeId before I realized that there was a trigger that would do it. So my SQL to insert can be simplified to the following, with the same result (only the 1 record (of 20) with a non-null value in UpdatedBy field inserts).
INSERT INTO [StefHpl_prod].[hpl].[Age_Unit]
([AgeID]
,[Hosp_ID]
,[Age_Unit]
,[Active]
,[UpdateTimeStamp]
,[UpdatedBy])
SELECT 0
,[Hosp_ID]
,[Age_Unit]
,[Active]
,[UpdateTimeStamp]
,[UpdatedBy]
FROM [StefScripps].[hpl].Age_Unit A
May 9, 2007 at 11:15 am
That is odd. Try running Profiler against the table as you do the work. Use the statement starting and statement completion events in addition to the normal ones. That way you can catch triggers, & other stuff as it fires.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 9, 2007 at 11:29 am
I haven't used profiler before but no time like the present, i'll figure it out.
Interestingly, I can update the UpdatedBy values by issuing single UPDATE stmts, and when i do replace some of the nulls with valid values, I still only get the one original record to insert. ???
May 9, 2007 at 11:57 am
Ok, I had a chance to look through your code and I see several problems:
1. Your instead of insert trigger will cause problems when more than one row is inserted during a batch (i.e. your insert into xxxx select from yyyy) because it will a take ONLY the first record and transfer it based on the code:
select
@AgeID = ins.AgeID ,
@Hosp_ID = ins.Hosp_ID ,
@Age_Unit = ins.Age_Unit ,
@Active = ins.Active ,
@UpdateTimeStamp = DATEADD(hour,-6,GETUTCDATE()),
@UpdatedBy = ins.UpdatedBy
from inserted ins
That is why you only get one row!
2. You have a similiar problem in you UPDATE trigger, see the code:
select
@AgeID = ins.AgeID
from inserted ins
update hpl.Age_Unit
set UpdateTimeStamp = DATEADD(hour,-6,GETUTCDATE())
where AgeID = @AgeID
You will only get the values for the FIRST record updated, all others will be lost and/or updated with the wrong values (probably where the "unique" value problem during multiple updates is comming from, though I still can't figure out that "exact" error message).
There for this code NEVER worked for multiple row inserts/updates!!!!
if you need help reworking those triggers let me know, though unless you have a particular need to increment by "random" intervals during the insert, why not just use and IDENTITY column.
James.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply