Why do they make it so difficult?
From: Wayne Wengert (wayneDONTWANTSPAM_at_wengert.com)
Date: 12/23/04
- Next message: Paul: "RE: Holidays"
- Previous message: Paul: "RE: Database Design"
- In reply to: Kalen Delaney: "Re: Still Can't Make it Work?"
- Next in thread: Wayne Wengert: "Think I got it"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 23 Dec 2004 13:27:43 -0700
Kalen;
I finally figured out the syntax to drop the constraint but it still won't
let me add a default value as part of an ALTER COLUMN? I notice all the
examples are ADD, not ALTER (real helpful!)
I tried the following:
ALTER TABLE Units
ALTER COLUMN UnitInfo bit NOT NULL
CONSTRAINT AddUnitInfoDflt <= I also tried just the column name here -
same result
DEFAULT 0 WITH VALUES
But it gives me the error:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CONSTRAINT'.
Wayne
"Kalen Delaney" <replies@public_newsgroups.com> wrote in message
news:upGy%233R6EHA.3616@TK2MSFTNGP11.phx.gbl...
> No, you'll have to drop the existing default first with ALTER TABLE DROP
> CONSTRAINT.
>
> --
> HTH
> ----------------
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "Wayne Wengert" <wayneDONTWANTSPAM@wengert.com> wrote in message
> news:eHJRAgR6EHA.824@TK2MSFTNGP11.phx.gbl...
> > Kalen;
> >
> > That was my first attempt but it gives the following error:
> >
> > Server: Msg 5074, Level 16, State 1, Line 1
> > The object 'AddUnitInfoDflt' is dependent on column 'UnitInfo'.
> > Server: Msg 4922, Level 16, State 1, Line 1
> > ALTER TABLE ALTER COLUMN UnitInfo failed because one or more objects
> > access
> > this column.
> > The object 'AddUnitInfoDflt' is dependent on column 'UnitInfo'.
> >
> > so I assumed that I needed to include the default?
> >
> > BTW - all rows are 0 or 1
> >
> > Wayne
> >
> > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
> > news:uuCPHVR6EHA.2540@TK2MSFTNGP09.phx.gbl...
> >> Wayne
> >>
> >> The syntax is a bit confusing because there are so many possibilities,
> >> but
> >> there is no possibility that actually changes the datatype and includes
a
> >> constraint. You will have to do this in multiple steps.
> >>
> >> You could change the datatype, and then run ALTER again to add the
> >> constraint. If you get an error changing the type, you might need to
> >> first
> >> update the table to make sure all the rows have 1 or 0 in that column.
> >>
> >> --
> >> HTH
> >> ----------------
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Wayne Wengert" <wayneDONTWANTSPAM@wengert.com> wrote in message
> >> news:%23aQNU8Q6EHA.3828@TK2MSFTNGP09.phx.gbl...
> >> > Thanks for the clarification but that doesn't work either? That
article
> >> > defines the syntax to be used (very confusing definitions) but the
> >> > examples
> >> > show only ADD or DROP columns. I want to change an existing column to
> > type
> >> > "bit" (it is currently int). I tried:
> >> >
> >> > ALTER TABLE Units
> >> > ALTER COLUMN UnitInfo bit NOT NULL
> >> > DEFAULT 0 WITH VALUES
> >> >
> >> > but that gives a syntax error "near DEFAULT"
> >> >
> >> > then I tried:
> >> >
> >> > ALTER TABLE Units
> >> > ALTER COLUMN UnitInfo bit NOT NULL
> >> > Constraint UnitInfoDflt
> >> > DEFAULT 0 WITH VALUES
> >> >
> >> > (I got the constraint name from - exec sp_helpconstraint 'units') but
> > that
> >> > gives a syntax error "near Constraint"
> >> >
> >> > Are there any examples of simply altering a column with a default
> > setting?
> >> >
> >> > Wayne
> >> >
> >> >
> >> > "Wayne Wengert" <wayneDONTWANTSPAM@wengert.com> wrote in message
> >> > news:eNLXwEQ6EHA.1188@tk2msftngp13.phx.gbl...
> >> >> Kalen;
> >> >>
> >> >> I am using the following QA statement:
> >> >>
> >> >> ALTER TABLE Units
> >> >> ALTER COLUMN UnitInfo bit NOT NULL
> >> >> SET DEFAULT 0
> >> >>
> >> >> But I get a syntax error "near the word DEFAULT"
> >> >>
> >> >> I got this syntax from the MSDN site where the example is:
> >> >>
> >> >> ALTER TABLE TableName1 ALTER [COLUMN] FieldName2 [NULL | NOT NULL]
> >> >> [SET
> >> >> DEFAULT eExpression2]
> >> >> [SET CHECK lExpression2 [ERROR cMessageText2]] [ DROP DEFAULT ] [
> > DROP
> >> >> CHECK ] [ NOVALIDATE ]Why does my code fail?Wayne"Kalen Delaney"
> >> >> <replies@public_newsgroups.com> wrote in message
> >> >> news:ed%23xxVI6EHA.1524@TK2MSFTNGP09.phx.gbl...
> >> >> > Hi Wayne
> >> >> >
> >> >> > I presume you're using Enterprise Manager. Try using Query
Analyzer
> > and
> >> >> > actually make the change using the ALTER TABLE command. Any error
> >> >> > you
> >> > get
> >> >> > should be a lot more meaningful.
> >> >> >
> >> >> > To truly verify you have no triggers on a table, run
> >> >> >
> >> >> > exec sp_helptrigger 'tablename'
> >> >> >
> >> >> > --
> >> >> > HTH
> >> >> > ----------------
> >> >> > Kalen Delaney
> >> >> > SQL Server MVP
> >> >> > www.SolidQualityLearning.com
> >> >> >
> >> >> >
> >> >> > "Wayne Wengert" <wayneDONTWANTSPAM@wengert.com> wrote in message
> >> >> > news:esKGlFI6EHA.3828@TK2MSFTNGP09.phx.gbl...
> >> >> > >I am using SQL Server 2000. I try to modify a filed in a table
> >> > definition
> >> >> > > (from int to bit) and when I save it I get a popup saying that
the
> >> >> update
> >> >> > > trigger could not be read and the process aborts. There are no
> >> > triggers
> >> >> on
> >> >> > > the table. If I right click on the table and open the triggers
it
> >> > offers
> >> >> a
> >> >> > > new trigger - there are none currently.
> >> >> > >
> >> >> > > How do I resolve this?
> >> >> > >
> >> >> > > Wayne
> >> >> > >
> >> >> > >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>
- Next message: Paul: "RE: Holidays"
- Previous message: Paul: "RE: Database Design"
- In reply to: Kalen Delaney: "Re: Still Can't Make it Work?"
- Next in thread: Wayne Wengert: "Think I got it"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
