FacePalm
-
So I have been working on a replicating database. Trigger on one of the tables being replicated, used to populate the Data Warehouse. So, trigger writes record to 'Ids that need to be updated in the DW' table and calls SP that reads through that table for all unprocessed records, and processes them. Because some related data may not (yet) have been replicated, this SP can legitimately fail to update the DW, and so shouldn't update the 'Ids that need to be updated in the DW' table to set it as processed. I"d been having a few problems with various bits and bobs, and had created a table, MaxxxsTemporaryTable, just used for logging stuff. So here was my code ...
Insert into DW.SomeTable
Select col, othercol, morecols
From ReplicatedDatabaseTable join AllSortsOfOtherTablesInsert into MaxxxsTemporaryTable Select @Id, @OtherId, @@ROWCOUNT
IF @@RowCount > 0
BEGIN
Update 'Ids that need to be updated in the DW' table
Set IsProcessed = 1
Where Id = @Id
ENDBut, for some reason, the IsProcessed flag was always being set, even though I knew it was zero, because that was what was written in my temp table!! :-O I had to post about it to bluff my shame!
PooperPig - Coming Soon
-
So I have been working on a replicating database. Trigger on one of the tables being replicated, used to populate the Data Warehouse. So, trigger writes record to 'Ids that need to be updated in the DW' table and calls SP that reads through that table for all unprocessed records, and processes them. Because some related data may not (yet) have been replicated, this SP can legitimately fail to update the DW, and so shouldn't update the 'Ids that need to be updated in the DW' table to set it as processed. I"d been having a few problems with various bits and bobs, and had created a table, MaxxxsTemporaryTable, just used for logging stuff. So here was my code ...
Insert into DW.SomeTable
Select col, othercol, morecols
From ReplicatedDatabaseTable join AllSortsOfOtherTablesInsert into MaxxxsTemporaryTable Select @Id, @OtherId, @@ROWCOUNT
IF @@RowCount > 0
BEGIN
Update 'Ids that need to be updated in the DW' table
Set IsProcessed = 1
Where Id = @Id
ENDBut, for some reason, the IsProcessed flag was always being set, even though I knew it was zero, because that was what was written in my temp table!! :-O I had to post about it to bluff my shame!
PooperPig - Coming Soon
I don't know anything about SQL, so what is the goof?
What do you get when you cross a joke with a rhetorical question?
-
I don't know anything about SQL, so what is the goof?
What do you get when you cross a joke with a rhetorical question?
@@ROWCOUNT is a 'system variable' that tells you how many rows were affected by the last statement. So my Insert into the temporary table, changed it from whatever value it had (zero) , to 1 - so it was always > 0 so always set IsProcessed
PooperPig - Coming Soon
-
@@ROWCOUNT is a 'system variable' that tells you how many rows were affected by the last statement. So my Insert into the temporary table, changed it from whatever value it had (zero) , to 1 - so it was always > 0 so always set IsProcessed
PooperPig - Coming Soon
Ah. I see.
What do you get when you cross a joke with a rhetorical question?