April 12, 2010 at 6:31 am
I have a table that may or may not have a timestamp field in it. For me to send out the deployment scripts it is required I account for the possibility of either. I wrote my script to see if the field exists, and if not, create it, then, regardless or whether the field exists or not, I populate the field with a value because I need it to not allow nulls.
So, the script is:
check if field exists.
If not, create it.
Populate field where is it Null.
Change the field to not allow nulls
The problem is that the part of the script where the field gets populated gives me an error if the field does not already exist.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'InsertedDateTime'.
If I run the part of the script that creates the field, then run the entire script, which skips the create since the field is already there, it runs just fine.
I enclosed the create field part in a transaction, to see if the query engine would complete that before it started to populate the field, but now I am thinking that the query engine is validating the TSQL before it runs it, and because the field does not exist it is giving me an error and not taking the create field code into account. I have not seen a way to turn off validation, so I am stumped.
Here is the code:
If Not Exists(Select column_name From information_schema.columns
Where column_name = 'InsertedDateTime'
And table_name = 'RecipEligToVendorErr')
Begin
Begin Transaction
Alter Table RecipEligToVendorErr
Add InsertedDateTime DateTime
Commit Transaction
End
--Make sure the field has a value in it before we change it to not allow Nulls.
Set RowCount 100000 --Keep tran log manageable.
While 1=1 --Set up a loop.
Begin
Update RecipEligToVendorErr
Set InsertedDateTime = '1/1/1900' --Default date for records that have nothing here.
Where InsertedDateTime Is Null
If @@ROWCOUNT = 0
Break
End
Alter Table RecipEligToVendorErr
Alter Column InsertedDateTime DateTime NOT Null --Will always need a value in future.
Anyone know a way to make this script work, regardless of whther the field exists?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
April 12, 2010 at 7:07 am
if you move the alter into an EXEC statement, it will work:
If Not Exists(Select column_name From information_schema.columns
Where column_name = 'InsertedDateTime'
And table_name = 'RecipEligToVendorErr')
Begin
Begin Transaction
EXEC('Alter Table RecipEligToVendorErr
Add InsertedDateTime DateTime ')
Commit Transaction
End
--Make sure the field has a value in it before we change it to not allow Nulls.
Set RowCount 100000 --Keep tran log manageable.
While 1=1 --Set up a loop.
Begin
Update RecipEligToVendorErr
Set InsertedDateTime = '1/1/1900' --Default date for records that have nothing here.
Where InsertedDateTime Is Null
If @@ROWCOUNT = 0
Break
End
Alter Table RecipEligToVendorErr
Alter Column InsertedDateTime DateTime NOT Null --Will always need a value in future.
Lowell
April 12, 2010 at 7:52 am
Nope, didn't work.
Here's my code:
If Not Exists(Select column_name From information_schema.columns
Where column_name = 'InsertedDateTime'
And table_name = 'RecipEligToVendorErr')
Begin
Begin Transaction
Exec('Alter Table RecipEligToVendorErr
Add InsertedDateTime DateTime')
Commit Transaction
End
--Make sure the field has a value in it before we change it to not allow Nulls.
Set RowCount 100000 --Keep tran log manageable.
While 1=1 --Set up a loop.
Begin
Update RecipEligToVendorErr
Set InsertedDateTime = '1/1/1900' --Default date for records that have nothing here.
Where InsertedDateTime Is Null
If @@ROWCOUNT = 0
Break
End
And the error:
Msg 207, Level 16, State 1, Line 14
Invalid column name 'InsertedDateTime'.
Tried it with and without the transaction wrapper.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
April 12, 2010 at 8:05 am
I tried going the other way:
While 1=1 --Set up a loop.
Begin
Exec('Update RecipEligToVendorErr
Set InsertedDateTime = ''1/1/1900'' --Default date for records that have nothing here.
Where InsertedDateTime Is Null')
If @@ROWCOUNT = 0
Break
End
And it runs fine.
I wonder if this is the best way to do it though.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply