July 1, 2011 at 9:30 pm
I have a trigger that updates product tables across more than 80 databases. Somehow the trigger hits a timeout error after about 50 databases.
We are speculating that this could be and error with SQL2005. We have a similar trigger running on SQL2000 and this error does not occur.
When we run the trigger script interactively, it runs thru wo a hitch but not inside the trigger.
We tried begin-commit transactions and it did not work. Thinking that maybe the updates were taking too long.
Do we need to tweak parameters to lengthen the time period?
Can any1 shed light on the matter? Whether this is a 2005 issue and if there is a way around this.
July 1, 2011 at 9:39 pm
alhayalara (7/1/2011)
I have a trigger that updates product tables across more than 80 databases. Somehow the trigger hits a timeout error after about 50 databases.We are speculating that this could be and error with SQL2005. We have a similar trigger running on SQL2000 and this error does not occur.
When we run the trigger script interactively, it runs thru wo a hitch but not inside the trigger.
We tried begin-commit transactions and it did not work. Thinking that maybe the updates were taking too long.
Do we need to tweak parameters to lengthen the time period?
Can any1 shed light on the matter? Whether this is a 2005 issue and if there is a way around this.
Hard to shed any light when everything is dark. We can't see what you see, so hard to provide some advice. It would help if you could show use the code causing the problem.
July 1, 2011 at 10:27 pm
Hi Lynn, thank for your attention.
Pasted the code
set @ID = (SELECT TOP 1 ID FROM TempTable WHERE Name = 'Products')
set @NewMD = (SELECT TOP 1 NewCol2 FROM TempTable WHERE Name = 'Products' AND ID = @ID)
set @NewMDDate = (SELECT TOP 1 NewCol3 FROM TempTable WHERE Name = 'Products' AND ID = @ID)
set @pcode = (SELECT TOP 1 NewCol1 FROM TempTable WHERE Name = 'Products' AND ID = @ID)
declare cur cursor fast_forward for
SELECT ConcessionCode FROM SkiesMaster.dbo.ConcessionDBs WHERE ConcessionCode <> 'SkiesHO'
open cur
fetch next from cur into @strCon
while @@fetch_status = 0
BEGIN
-- Apply markdown price to all databases that contains the product modified, except SKIESHO
--
if not db_id(@strCon) is null
Print @strCon
BEGIN
exec ('Update ['+ @strCon + '].dbo.Products SET [MarkDownDate] = '''+ @NewMDDate + ''' WHERE [ProductCode] = ''' + @pcode + '''' )
exec ('Update ['+ @strCon + '].dbo.Products SET [MarkDown] = '+ @NewMD + ' WHERE [ProductCode] = ''' + @pcode + '''' )
END
fetch next from cur into @strCon
END
close cur
deallocate cur
[/size]
July 1, 2011 at 10:44 pm
alhayalara (7/1/2011)
Hi Lynn, thank for your attention.Pasted the code
set @ID = (SELECT TOP 1 ID FROM TempTable WHERE Name = 'Products')
set @NewMD = (SELECT TOP 1 NewCol2 FROM TempTable WHERE Name = 'Products' AND ID = @ID)
set @NewMDDate = (SELECT TOP 1 NewCol3 FROM TempTable WHERE Name = 'Products' AND ID = @ID)
set @pcode = (SELECT TOP 1 NewCol1 FROM TempTable WHERE Name = 'Products' AND ID = @ID)
declare cur cursor fast_forward for
SELECT ConcessionCode FROM SkiesMaster.dbo.ConcessionDBs WHERE ConcessionCode <> 'SkiesHO'
open cur
fetch next from cur into @strCon
while @@fetch_status = 0
BEGIN
-- Apply markdown price to all databases that contains the product modified, except SKIESHO
--
if not db_id(@strCon) is null
Print @strCon
BEGIN
exec ('Update ['+ @strCon + '].dbo.Products SET [MarkDownDate] = '''+ @NewMDDate + ''' WHERE [ProductCode] = ''' + @pcode + '''' )
exec ('Update ['+ @strCon + '].dbo.Products SET [MarkDown] = '+ @NewMD + ' WHERE [ProductCode] = ''' + @pcode + '''' )
END
fetch next from cur into @strCon
END
close cur
deallocate cur
[/size]
This is the entire trigger? I am wondering because of the numerous select top(1) queries in the code.
July 1, 2011 at 10:58 pm
Yes this is almost the entire code.
- The trigger is in the table TempTable.
- First select is to retieved the ID of the 1st record in the temptable.
- 2nd is the MarkDdown (MD) price noted on this record
- 3rd is the date of the MD
- 4th is the product code.
This is done so that whn the upodate is propagated across, the specifc record is delete. There could be more than 1 record inserted into the temptable.
When a record is posted into the temptable, the insert trigger posts the changes on the MarkDown price of a specific product code to the different concession databases that has this product.
July 1, 2011 at 11:15 pm
So, is there an outer loop in this trigger? What I am trying to get to is that you should provide the entire definition of the trigger, not just a portion of it.
July 1, 2011 at 11:21 pm
USE [SkiesHO]
GO
/****** Object: Trigger [dbo].[ADDINSERTEMP] Script Date: 06/24/2011 09:43:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE TRIGGER [dbo].[ADDINSERTEMP] ON [dbo].[Temptable]
FOR INSERT, UPDATE
AS
declare @strCon varchar(25)
declare @NewMDDate as smalldatetime
declare @pcode as varchar(20)
declare @NewMD as varchar(20)
declare @ID as int
set @ID = (SELECT TOP 1 ID FROM TempTable WHERE Name = 'Products')
set @NewMD = (SELECT TOP 1 NewCol2 FROM TempTable WHERE Name = 'Products' AND ID = @ID)
set @NewMDDate = (SELECT TOP 1 NewCol3 FROM TempTable WHERE Name = 'Products' AND ID = @ID)
set @pcode = (SELECT TOP 1 NewCol1 FROM TempTable WHERE Name = 'Products' AND ID = @ID)
set @i = 1
declare cur cursor fast_forward for
SELECT ConcessionCode FROM SkiesMaster.dbo.ConcessionDBs WHERE ConcessionCode <> 'SkiesHO'
open cur
fetch next from cur into @strCon
while @@fetch_status = 0
BEGIN
-- Apply markdown price to all databases that contains the product modified, except SKIESHO
--
if not db_id(@strCon) is null
Print @strCon
BEGIN
if @i =1 BEGIN
begin transaction
PRINT "'"
END
exec ('Update ['+ @strCon + '].dbo.Products SET [MarkDownDate] = '''+ @NewMDDate + ''' WHERE [ProductCode] = ''' + @pcode + '''' )
exec ('Update ['+ @strCon + '].dbo.Products SET [MarkDown] = '+ @NewMD + ' WHERE [ProductCode] = ''' + @pcode + '''' )
set @i= @i + 1
if @i > 30
BEGIN
commit transaction
Print @i
set @i=1
END
END
fetch next from cur into @strCon
END
close cur
deallocate cur
July 2, 2011 at 12:15 am
Are you guarantteed to have only one record inserted into the table [dbo].[Temptable] at a time or is it possible that multiple records could be inserted by a singe insert statement?
July 2, 2011 at 12:21 am
Its written to meet the possibility of having several inserts made to temptable. Thus the ID is saved so that when propagation is done the record with the ID is deleted from the table.
Trigger reacts to one record at a time.
July 2, 2011 at 12:33 am
alhayalara (7/2/2011)
Its written to meet the possibility of having several inserts made to temptable. Thus the ID is saved so that when propagation is done the record with the ID is deleted from the table.Trigger reacts to one record at a time.
Doesn't answer the question, are you guaranteed to have only one record inserted into the table at a time by a single insert statement? I'm not concerned about multiple singleton inserts, just that multiple records can't be written by a single insert.
July 2, 2011 at 1:59 am
Only 1 record written to temptable at a time.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply