|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 PM
Points: 67,
Visits: 367
|
|
I'm writing a SQL change script and finding it difficult to get the changes performed in an atomic operation. We typically prefer to keep our change operations completely atomic. Here's a skeleton of the test script:
--using SQLCMD mode :ON ERROR EXIT SET XACT_ABORT ON;
BEGIN TRANSACTION;
CREATE TABLE #tmp ( ID int, A bit NOT NULL, B bit NOT NULL, C bit NOT NULL );
INSERT INTO #tmp(ID, A, B, C) SELECT ID, A, B, C FROM dbo.tbl;
ALTER TABLE dbo.tbl DROP CONSTRAINT DF_tbl_A, DF_tbl_B, DF_tbl_C;
ALTER TABLE dbo.tbl ALTER COLUMN A datetime NULL;
GO
ALTER TABLE dbo.tbl ALTER COLUMN B datetime NULL;
GO
ALTER TABLE dbo.tbl ALTER COLUMN C datetime NULL;
GO
UPDATE t SET t.A = CASE WHEN x.A = 1 THEN GETDATE() ELSE NULL END, t.B = CASE WHEN x.B = 1 THEN GETDATE() ELSE NULL END, t.C = CASE WHEN x.C = 1 THEN GETDATE() ELSE NULL END FROM dbo.tbl AS t INNER JOIN #tmp AS x ON t.ID = x.ID;
DROP TABLE #tmp;
ROLLBACK; In my tests, the scripts fails on the UPDATE statement with error saying that implicit conversion from datetime to bit is not allowed. This error indicates to me that it hasn't picked up the fact that the table got changed already. I tried breaking up the UPDATE statement into 3 updates, between each ALTER TABLE ALTER COLUMN statement and found that it will work for column A and column B but simply dies on column C.
AFAIK, the 3 columns are all identical in their properties so I'm not sure why script can't see the changes made to column C (which apparently succeeds) by time it tries to update the column C to correct default values. Only one significant difference was that column C has been a part of the table definition for a good while while column A and B were relatively recent additions.
I'd rather not have to commit the transaction between the altering of table and modifying the data and would love to see if there's a solution to keep it all within a single transaction. Ideas?
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114,
Visits: 140
|
|
What is the full definition for dbo.tbl
I tried this
CREATE TABLE dbo.tbl ( ID int, A bit NOT NULL DEFAULT (0), B bit NOT NULL DEFAULT (0), C bit NOT NULL DEFAULT (0) );
INSERT INTO dbo.tbl SELECT 1, 0,0,0 UNION ALL SELECT 2, 1,0,1 UNION ALL SELECT 3, 0,1,0
and it all worked fine - so perhaps you have other constraints on your version?
|
|
|
|
|
SSCoach
         
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442,
Visits: 9,571
|
|
Have you checked to make sure there are no triggers being fired by the updates, or anything like that?
I'd have to see the table definition (create script) before I could suggest anything more detailed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 PM
Points: 67,
Visits: 367
|
|
The actual table is more complicated than that but that is in fact the gist of the definition of pertinent columns. The ID is an identity, and the actual table has a rowversion, plus few more columns. For this change script, only 3 columns I've mentioned will be affected.
If I omit column C from the change script, it will succeed. But I need to alter column C atomically with its sibling column A and B and I can't think what else may be causing this to fail. The table as whole has about 10,000+ records, and I've indicated that column C is older than A and B. In the test database (where I was also testing the script), there are only 4 rows that has 1 for column C.
Maybe I'm totally missing something else that would prevent the UPDATE statement to proceed. If ALTER TABLE ALTER COLUMN statement was failing, then I would know there's something wrong with my process in modifying the table structure but it is succeeding but UPDATE fails. Totally baffles me.
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 PM
Points: 67,
Visits: 367
|
|
GSquared,
I was going to say that I had disabled a trigger as part of the change script but you made me go and look again and turns out there's one more trigger that I didn't actually get to disable. Looking into it, it's copying data into history table which I didn't update and the reason it was working for column A & B is because that trigger wasn't moving those columns, only the older column C.
When I saw that implicit conversion error, I got fixated on the fact that I needed to have a GO between the ALTER and UPDATE to ensure that the changes becomes "visible" which I did originally forget in my first draft.
Mystery solved with egg on my face.
Thank you!
|
|
|
|
|
SSCoach
         
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442,
Visits: 9,571
|
|
I just set up this test harness:
CREATE TABLE dbo.tbl (ID INT, A BIT, B BIT, C BIT); GO ALTER TABLE dbo.tbl ADD CONSTRAINT DF_tbl_A DEFAULT(0) FOR A; ALTER TABLE dbo.tbl ADD CONSTRAINT DF_tbl_B DEFAULT(0) FOR B; ALTER TABLE dbo.tbl ADD CONSTRAINT DF_tbl_C DEFAULT(0) FOR C;
GO INSERT INTO dbo.tbl (ID, A, B, C) VALUES (1, 1, 1, 1), (2, 0, 0, 0); GO SET XACT_ABORT ON;
BEGIN TRANSACTION;
CREATE TABLE #tmp (ID INT, A BIT NOT NULL, B BIT NOT NULL, C BIT NOT NULL);
INSERT INTO #tmp (ID, A, B, C) SELECT ID, A, B, C FROM dbo.tbl;
ALTER TABLE dbo.tbl DROP CONSTRAINT DF_tbl_A, DF_tbl_B, DF_tbl_C;
ALTER TABLE dbo.tbl ALTER COLUMN A DATETIME NULL;
GO
ALTER TABLE dbo.tbl ALTER COLUMN B DATETIME NULL;
GO
ALTER TABLE dbo.tbl ALTER COLUMN C DATETIME NULL;
GO
UPDATE t SET t.A = CASE WHEN x.A = 1 THEN GETDATE() ELSE NULL END, t.B = CASE WHEN x.B = 1 THEN GETDATE() ELSE NULL END, t.C = CASE WHEN x.C = 1 THEN GETDATE() ELSE NULL END FROM dbo.tbl AS t INNER JOIN #tmp AS x ON t.ID = x.ID;
DROP TABLE #tmp;
COMMIT; GO DROP TABLE dbo.tbl;
Runs without errors. That means there's something else going on with your table. Possibly a trigger, either DDL or DML. That would be my first suspicion in a case like this.
If, for example, there's a logging trigger on column C, but not on A or B, with an audit trail being generated by it, that would totally explain what you're running into.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 PM
Points: 67,
Visits: 367
|
|
GSquared,
Yes you nailed it as I explained in my subsequent discovery in my previous post just before your last reply.
Again, thank you!
|
|
|
|
|
SSCoach
         
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442,
Visits: 9,571
|
|
Banana-823045 (9/12/2012) GSquared,
Yes you nailed it as I explained in my subsequent discovery in my previous post just before your last reply.
Again, thank you!
And of course, we overlapped post-timing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 PM
Points: 67,
Visits: 367
|
|
| It'd have been nice to not have to use GO between certain DDL and DML statements. That would have made things more simpler. Oh, well.
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 37,744,
Visits: 30,025
|
|
Without a GO, the entire thing (alter and subsequent data modifications) gets parsed as a batch and the parsing happens before anything is run. Hence if the alter adds a column, the data modifications can't affect that column in the same batch, because at parse (and optimisation) time, the new column isn't there.
Gail Shaw Microsoft Certified Master: SQL Server 2008, MVP SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter We stand on the bridge and no one may pass
|
|
|
|