May 30, 2011 at 12:11 pm
It's just a matter of ordering the output of the command... what have you tried and where are you getting blocked??
May 30, 2011 at 12:41 pm
well, this, and it appears to have worked.
DECLARE @sql1 nvarchar(4000)
SET @sql1 = 'ALTER TABLE '+ @Table_Name + ' DROP ' + @default
EXEC(@sql1)
DECLARE @sql2 nvarchar(4000)
SET @sql2 = 'ALTER TABLE ' + @Table_Name + ' ALTER COLUMN IsActive bit not null'
EXEC(@sql2)
DECLARE @sql3 nvarchar(4000)
SET @sql3 = 'ALTER TABLE '+ @Table_Name + ' ADD DEFAULT ((1)) FOR IsActive'
EXEC(@sql3)
May 30, 2011 at 12:44 pm
well, it appears to have worked for some columns but not for all. Some columns are missing a default constraint.
Do I need to add error handling? for example. what if one of the columns doesn't have a default constraint, and I am trying to drop one that doesn't exist? Then, when I go to add one, will that fail?
May 30, 2011 at 4:11 pm
hxkresl (5/30/2011)
well, it appears to have worked for some columns but not for all. Some columns are missing a default constraint.Do I need to add error handling? for example. what if one of the columns doesn't have a default constraint, and I am trying to drop one that doesn't exist? Then, when I go to add one, will that fail?
All those questions can be answered by trying to run the code. Come back with a more specific question once you do that.
May 30, 2011 at 5:57 pm
you are right, thanks for support.
right now I have recreated the db and ran the above code and all it all looks good. I think I am fine.
Again thank you all!!!!
May 30, 2011 at 10:24 pm
I think this will be the last of it.
i am automating the renaming of the table column segment with the following Dynamic SQL, and am getting error:
"Msg 102, Level 15, State 1, Line 41
Incorrect syntax near ''IsActive', 'COLUMN''."
Here are lines 40-42.
DECLARE @sql0 nvarchar(4000)
SET @sql0 = 'sp_rename '+ @TableName + ', 'IsActive', 'COLUMN''
EXEC(@sql0)
I believe it is the way I am enclosing in quotes. Can you see the syntax error?
thanks much.
May 30, 2011 at 10:43 pm
Try this:
DECLARE @sql0 nvarchar(4000)
SET @sql0 = 'sp_rename ''' + @TableName + ''', ''IsActive'', ''COLUMN'''
EXEC(@sql0)
You need to double up the quotes if you need the resulting statement to have quotes (if that makes sense).
You can also try replacing the EXEC with PRINT when debugging - wouldn't have helped in this case as you would still have got a syntax error, but it is often useful when making sure you have your quotes correct.
May 31, 2011 at 1:14 am
Many Thanks.
May 31, 2011 at 1:16 am
sourayan (5/31/2011)
Many Thanks.
It really helped me.
May 31, 2011 at 1:17 am
great
May 31, 2011 at 10:37 am
Jenny, Can you take another look?
This
sp_rename 'csmr.nameoftable.Active', 'IsActive', 'COLUMN';
Needs to become this:
DECLARE @sql0 nvarchar(4000)
SET @sql0 = 'sp_rename '+ ''@Table_Name'.Active'''', '''IsActive''', '''COLUMN''
EXEC(@sql0)
and I still can't get the quotes right. With the above, I'm still getting syntax error: "Incorrect syntax near '@Table_Name'."
May 31, 2011 at 10:53 am
DECLARE @Table_Name VARCHAR(128)
DECLARE @sql0 VARCHAR(1000)
SET @Table_Name = 'MyTable'
SET @sql0 = 'sp_rename '''+ @Table_Name + '.Active'', ''IsActive'', ''COLUMN'''
PRINT @sql0
--sp_rename 'MyTable.Active', 'IsActive', 'COLUMN'
May 31, 2011 at 11:02 am
Oh the concatenation symbols. Thanks so much Ninja. I appreciate the amazing support on this forum!
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply