October 2, 2007 at 8:05 am
Hello everybody,
I can't perform an operation apparently very easy: set a field to a NULL value.
This is the db:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
This is the table:
CREATE TABLE [ProgettoTracce] (
[ID_Progetto] [int] NOT NULL ,
[MisDifDef] [real] NULL ,
[MisDifMeas] [real] NULL ,
[MisDifAna] [real] NULL ,
[MisDifID] [real] NULL ,
[MisDifCV] [real] NULL
) ON [PRIMARY]
GO
This is qry:
UPDATE ProgettoTracce
SET MisDifDef = NULL
WHERE ID_Progetto = 3444
The qry has been performed with no error. Then I execute SELECT * FROM ProgettoTracce WHERE ID_Progetto = 3444 and I find the value I tried to overwrite with NULL. If I update with 0 (for example) it works.
Obviously this happens on the production db, because on the development db the update with NULL works fine.
No transaction is called, db options are the same on dbs...
What's happen? Have I to call an exorcist???
Thanks in advance for any help!
Nicola
October 2, 2007 at 8:46 am
What value are you trying to set to NULL?
October 2, 2007 at 9:04 am
Hi,
thanks for your interest!
I try to update a real value, for example: 758210,
Nicola
October 2, 2007 at 9:19 am
Any chance that there's an (enbled?) trigger on Production that's not on Dev? Or perhaps some refresh routine that comes in after your update?
Some investigating queries I'd try:
-- Test #1, is the value written
SELECT ID_Progetto, MisDifDef
from ProgettoTracce
where ID_Progetto = 3444
UPDATE ProgettoTracce
SET MisDifDef = NULL
WHERE ID_Progetto = 3444
SELECT ID_Progetto, MisDifDef
from ProgettoTracce
where ID_Progetto = 3444
-- Test #2, no really, is the value written
BEGIN TRANSACTION
PRINT 'Before'
SELECT ID_Progetto, MisDifDef
from ProgettoTracce
where ID_Progetto = 3444
UPDATE ProgettoTracce
SET MisDifDef = NULL
WHERE ID_Progetto = 3444
PRINT 'After - within transaction'
SELECT ID_Progetto, MisDifDef
from ProgettoTracce
where ID_Progetto = 3444
COMMIT
PRINT 'After transaction'
SELECT ID_Progetto, MisDifDef
from ProgettoTracce
where ID_Progetto = 3444
Try the above from Query Analyzer with "Include Actual Execution Plan" set on, and see if that turns up any strangeness. SET STATISTICS IO ON might also help.
Philip
October 3, 2007 at 3:59 am
Bingo!
There was a trigger on production db!
Thank you very much!
Nicola
October 3, 2007 at 6:12 pm
So here's your real problem, and your real question: How did the Production database get to be so significantly different (I consider all triggers to be significant -- and you now have one good reason why) from your Development database? Good luck figuring it out!
Philip
October 3, 2007 at 6:18 pm
I would consider having a single space out of order an issue (after the deploying has been done).
I'd strongly suggest you check out the change management articles on this site. Also I'd lock out all developpers. Take backup of production and restore immediately on dev servers and then synchronise the db scripts. That'll save you a lot of grief down the road.
October 4, 2007 at 1:57 am
Philip and Ninja, you have centered the issue.
The problem now is not the trigger but why it was there and not documented.
I'll read the article first of all.
Thank you very much.
Regards
October 4, 2007 at 4:11 am
Happy to help. Let us know if you need further assistance on the subject (I suggest a new thread to get more attention on that issue then).
October 8, 2007 at 8:21 pm
nba (10/4/2007)
The problem now is not the trigger but why it was there and not documented.
Simple... database is not correctly locked down for changes. No one but DBA's should promote the code. Also, you should occasionally make a copy of the prod database for use as development.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply