March 16, 2011 at 4:32 pm
The coffee is no longer working - I bomb out on
Msg 207, Level 16, State 1, Line 7
Invalid column name 'Suspension_URL'.
???
Doug
-- As things change, alter them at the end of the existing definition.
IF NOT EXISTS( SELECT * FROM sys.columns WHERE Name = 'Suspension_URL' AND OBJECT_NAME(OBJECT_ID) = 'ProviderService')
BEGIN
ALTER TABLE [dbo].[ProviderService] ADD [Suspension_URL] [NVARCHAR](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
END
IF NOT EXISTS( SELECT * FROM [dbo].[ProviderService] WHERE [Suspension_URL] IS NULL)
BEGIN
-- We need this to be NOT NULL - populate all columns
UPDATE [dbo].[ProviderService]
SET [Suspension_URL] = 'Suspension_URL'
WHERE [Suspension_URL] IS NULL
END
-- Now make NOT NULL
ALTER TABLE [dbo].[ProviderService] ALTER COLUMN [Suspension_URL] [NVARCHAR](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL;
March 16, 2011 at 5:52 pm
Hi Doug,
you're most likely getting your error because you're doing this all in one batch and SQL Server does not know about the Suspension_URL column when it's looking at the update query. You could just add the column first then run the rest of the script. If you want to run this as one script, put a GO after you alter the table you should be good.
Another way of accomplishing this is:
BEGIN TRY
BEGIN TRAN --start a transaction
IF NOT EXISTS( SELECT * FROM sys.columns WHERE Name = 'Suspension_URL' AND OBJECT_NAME(OBJECT_ID) = 'ProviderService')
BEGIN
--Add the column with a default so you can make it not null. This should also fill all the columns with
--the value 'Suspension_URL' eliminating the need for the Update
ALTER TABLE [dbo].[ProviderService] ADD [Suspension_URL] [NVARCHAR](2048) NOT NULL CONSTRAINT [DF_ProviderService_Suspension_URL] DEFAULT 'Suspension_URL'
--Drop the default constraint if you wish
ALTER TABLE [dbo].[ProviderService] DROP CONSTRAINT [DF_ProviderService_Suspension_URL]
END
--if no errors occur then commit the transaction
COMMIT TRAN
END TRY
BEGIN CATCH
--if there is an error undo everything
ROLLBACK TRAN
END CATCH
Good Luck
Joe K
March 16, 2011 at 5:54 pm
Easier way: Slap a GO between each statement.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 17, 2011 at 1:18 pm
Neither - this is what worked - a little dynamic SQL since the column doesn't exist at run time.
-- As things change, alter them at the end of the existing definition.
IF NOT EXISTS( SELECT * FROM sys.columns WHERE Name = 'Suspension_URL' AND OBJECT_NAME(OBJECT_ID) = 'ProviderService')
BEGIN
ALTER TABLE [dbo].[ProviderService] ADD [Suspension_URL] [NVARCHAR](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
-- We need this to be NOT NULL - populate all columns. Needs to be dynamic SQL since the column doesn't exist at
-- evaluation time
SET @sql = '
UPDATE [dbo].[ProviderService]
SET [Suspension_URL] = ''Suspension_URL'''
EXECUTE ( @sql )
-- Now make NOT NULL
ALTER TABLE [dbo].[ProviderService] ALTER COLUMN [Suspension_URL] [NVARCHAR](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL;
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply