8/21/2012–News of the Day

Technical News

• I was troubleshooting an issue with a SQL Server script that we use to upgrade the database of an application we develop.  I ran into the weird situation where if I renamed a column in a table and then tried to update that in the script I would get a script compile time error saying that the column didn’t exist.  If I created a temp table with one row in it and did a cross join in the update statement like below the script would compile and run just fine.  Most of the websites I looked at explained that you could also accomplish the same thing by using dynamic sql with the update statement.  Somehow the compilation step for this “hack” I did with the temp table must be forcing the sql statement to be compiled in a more dynamic sense.

select ’1′ as SomeText
            into #dummytable

update q set q.ValueTXT = convert(varchar(255), q.ValueTXTTMP) from [dbo].[SomeImportantTable] q cross join #dummytable p

About these ads