InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 03:40:04 GMT20http://www.sqlservercentral.com/Forums/Topic1357888-392-1.aspxWithout 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.Wed, 12 Sep 2012 07:41:07 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1357888-392-1.aspxIt'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.Wed, 12 Sep 2012 07:11:40 GMTBanana-823045http://www.sqlservercentral.com/Forums/Topic1357888-392-1.aspx[quote][b]Banana-823045 (9/12/2012)[/b][hr]GSquared,Yes you nailed it as I explained in my subsequent discovery in my previous post just before your last reply. Again, thank you![/quote]And of course, we overlapped post-timing.Wed, 12 Sep 2012 07:02:34 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1357888-392-1.aspxGSquared,Yes you nailed it as I explained in my subsequent discovery in my previous post just before your last reply. Again, thank you!Wed, 12 Sep 2012 06:52:48 GMTBanana-823045http://www.sqlservercentral.com/Forums/Topic1357888-392-1.aspxI just set up this test harness:[code="sql"]CREATE TABLE dbo.tbl (ID INT, A BIT, B BIT, C BIT);GOALTER 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;GOINSERT INTO dbo.tbl (ID, A, B, C)VALUES (1, 1, 1, 1), (2, 0, 0, 0);GOSET 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.tblDROP CONSTRAINT DF_tbl_A, DF_tbl_B,DF_tbl_C;ALTER TABLE dbo.tblALTER COLUMN A DATETIME NULL;GOALTER TABLE dbo.tblALTER COLUMN B DATETIME NULL;GOALTER TABLE dbo.tblALTER COLUMN C DATETIME NULL;GOUPDATE tSET 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 ENDFROM dbo.tbl AS t INNER JOIN #tmp AS x ON t.ID = x.ID;DROP TABLE #tmp;COMMIT;GODROP TABLE dbo.tbl;[/code]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.Wed, 12 Sep 2012 06:45:40 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1357888-392-1.aspxGSquared,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!Wed, 12 Sep 2012 06:43:42 GMTBanana-823045http://www.sqlservercentral.com/Forums/Topic1357888-392-1.aspxThe 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.Wed, 12 Sep 2012 06:37:44 GMTBanana-823045http://www.sqlservercentral.com/Forums/Topic1357888-392-1.aspxHave 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.Wed, 12 Sep 2012 06:36:33 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1357888-392-1.aspxWhat is the full definition for dbo.tblI tried this[code="sql"]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 ALLSELECT 2, 1,0,1 UNION ALLSELECT 3, 0,1,0 [/code]and it all worked fine - so perhaps you have other constraints on your version?Wed, 12 Sep 2012 06:28:24 GMTt.brown 89142http://www.sqlservercentral.com/Forums/Topic1357888-392-1.aspxI'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:[code="sql"]--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, CFROM dbo.tbl;ALTER TABLE dbo.tblDROP CONSTRAINT DF_tbl_A, DF_tbl_B, DF_tbl_C;ALTER TABLE dbo.tblALTER COLUMN A datetime NULL;GOALTER TABLE dbo.tblALTER COLUMN B datetime NULL;GOALTER TABLE dbo.tblALTER COLUMN C datetime NULL;GOUPDATE 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 ENDFROM dbo.tbl AS tINNER JOIN #tmp AS x ON t.ID = x.ID;DROP TABLE #tmp;ROLLBACK;[/code]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?Wed, 12 Sep 2012 04:56:42 GMTBanana-823045