Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performing a ALTER TABLE then modifying data in same transaction scope?


Performing a ALTER TABLE then modifying data in same transaction scope?

Author
Message
Banana-823045
Banana-823045
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 556
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?
t.brown 89142
t.brown 89142
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 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?
GSquared
GSquared
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16657 Visits: 9729
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
Banana-823045
Banana-823045
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 556
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.
Banana-823045
Banana-823045
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 556
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!
GSquared
GSquared
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16657 Visits: 9729
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
Banana-823045
Banana-823045
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 556
GSquared,

Yes you nailed it as I explained in my subsequent discovery in my previous post just before your last reply.

Again, thank you!
GSquared
GSquared
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16657 Visits: 9729
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
Banana-823045
Banana-823045
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 556
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57687 Visits: 44708
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search