| Author |
Topic |
|
WindChaser
Posting Yak Master
225 Posts |
 Posted - 2004-03-06 : 10:49:37
|
| Folks, I want to change the default value of a column which is of nvarchar type but I can't figure out how. I have tried all of the following without success. conMyConnection.Execute "Alter Table MyTable Alter Column MyCol Default '00000000000000000'"conMyConnection.Execute "Alter Table MyTable Alter Column MyCol nvarchar(20) Null Default '00000000000000000'"conMyConnection.Execute "Alter Table MyTable Alter Column MyCol nvarchar(20) Null set Default as '00000000000000000'"Can anyone point me in the right direction? Thanks! |
|
|
ChrisFretwell
Starting Member
43 Posts |
Posted - 2004-03-08 : 11:48:42
|
| When I looked up defaults in BOL, it says you need to create the default then bind the default. So for you this is what you would doCREATE DEFAULT Empty_Def AS '00000000000000000'GOsp_bindefault Empty_Def, 'Mytable.mycol'GO |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-03-08 : 13:05:34
|
| Alter Table MyTable add default '00000000000000000' for MyCol==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2004-03-09 : 19:09:27
|
| Thanks to both of you. But the column already has a default. I just want to alter the column's default. Adding another default creates an error of course.Thanks! |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-03-09 : 19:12:31
|
| You will need to find the name of the old default and drop it first...As a side not it is a good idea to name your defaults (and constraints, keys, indexes) for cases just like this...DavidM"A Human Clone is a result of not implementing a Key Constraint." |
|
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2004-03-10 : 08:25:17
|
| I checked through Enterprise Manager and there here are no defaults defined for the database. This is a DB which was converted over from Access using the MSAccess Upgrade Wizard, so I guess it decided that defining defaults was not necessary... How could I therefore drop the old default if it does not have a name? I can't find anything in Books Online to provide a clue.Thanks! |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-03-10 : 18:44:23
|
| Run "sp_help tablename".. It should list everything...DavidM"A Human Clone is a result of not implementing a Key Constraint." |
|
|
|
ChrisFretwell
Starting Member
43 Posts |
Posted - 2004-03-11 : 11:44:18
|
| Or in EM, right click on the table, go down to all tasks, generate sql scripts and be sure to check under options that you want to script defaults.See what gets scripted. If its there, great, if not, then you have no ongoing default. |
|
|
|
|
Setting a string default for a column