Results 1 to 12 of 12

Thread: BOL is Wrong?

  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: BOL is Wrong?

    This is the third time I cut and pasted some bol code and I get errors...what's up

    I tried this from bol, which doesn't work

    ALTER TABLE MyCustomers ALTER COLUMN CompanyName DROP DEFAULT

    And SSMS generates this syntax, which does work

    ALTER TABLE dbo.RHO_Type
    DROP CONSTRAINT DF__RHO_Type__ADD_Dt__112C2E87

    What's up?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i do not have a sql server in front of me but are you missing ALTER COLUMN?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ummmm...no

    I don't think so

    ALTER TABLE MyCustomers ALTER COLUMN CompanyName DROP DEFAULT
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    sorry just reread your post. exhausted.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It should be something like this:

    ALTER TABLE MyCustomers DROP CONSTRAINT <ConstraintName>

    Where, by default, <ConstraintName> should be DF_CompanyName but you have to verify if it's the real name of the constraint.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Not sure I see the code in BOL. is it under "Alter Table"?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes it is. If you go down, you'll find the example (BOL for SQL Server 2005):

    CREATE TABLE doc_exc ( column_a INT
    CONSTRAINT my_constraint UNIQUE) ;
    GO
    ALTER TABLE doc_exc DROP CONSTRAINT my_constraint ;
    GO
    DROP TABLE doc_exc ;
    GO

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    It's here:
    ALTER TABLE (SQL Server Compact)

    But this syntax applies only to SQL Server CE as indicated by the title "ALTER TABLE (SQL Server Compact)".

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Nope! It's the same syntax for every edition of SQL Server 2k5.

    ALTER TABLE (Transact-SQL)

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I was referring to the OP's original question regarding this example:

    Code:
    ALTER TABLE MyCustomers ALTER COLUMN CompanyName DROP DEFAULT
    That syntax is valid only in Compact Edition even though it is ISO standard SQL. Other editions use the non-ISO standard syntax that you already replied with, ie. they require the CONSTRAINT keyword and the name of the default being dropped. For some strange reason that I've never worked out Microsoft's proprietary syntax calls a default a "constraint"!

  11. #11
    Join Date
    Apr 2007
    Posts
    183
    Also asked and answered here
    SQL Server Forums - BOL is Wrong?

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I hate when n00bs start cross posting and don't link.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •