Create Table FAILED!?

  • sknox

    SSChampion

    Points: 12294

    Comments posted to this topic are about the item Create Table FAILED!?

  • sqlsniper

    Right there with Babe

    Points: 734

    Became a bit wiser today, thanks for the question.

    Always nice to see this kind of question about the basics.

  • Carlo Romagnano

    SSC-Insane

    Points: 22015

    I googled "Msg 2714" and answered correctly.

  • patricklambin

    SSCrazy Eights

    Points: 9964

    As I have installed only SQL Server 2012 and 2014 instances , I am obtaining only the following message

    Warning! The maximum key length is 900 bytes. The index 'PK__QoTD__2CB664DD844B62FA' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

    The creation is OK , any insert is OK except of course when the value for Title has a length > 900 bytes.

    I have not searched in the BOL but I am surprised that I have got only a warning with the 2012/2014 versions when 2008 would give an error message at the creation of the table.

    Is it an error in the BOL for 2012/2014 ? If yes , It could be an error and I will post a request to modify the SQL Server BOL (2012/2014).

  • Carlo Romagnano

    SSC-Insane

    Points: 22015

    patricklambin (12/30/2014)


    As I have installed only SQL Server 2012 and 2014 instances , I am obtaining only the following message

    Warning! The maximum key length is 900 bytes. The index 'PK__QoTD__2CB664DD844B62FA' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

    The creation is OK , any insert is OK except of course when the value for Title has a length > 900 bytes.

    I have not searched in the BOL but I am surprised that I have got only a warning with the 2012/2014 versions when 2008 would give an error message at the creation of the table.

    Is it an error in the BOL for 2012/2014 ? If yes , It could be an error and I will post a request to modify the SQL Server BOL (2012/2014).

    Please, re-read explanation! It's very clear!

    🙂

  • Hany Helmy

    SSChampion

    Points: 13488

    Carlo Romagnano (12/30/2014)


    I googled "Msg 2714" and answered correctly.

    +1 Same 🙂

  • Hany Helmy

    SSChampion

    Points: 13488

    Wow :w00t: 41% didn`t know about the "Extra comma in column list" will not prevent you from creating a table.

  • Hany Helmy

    SSChampion

    Points: 13488

    Very nice tricky question for the OBJECT_ID('dbo.QoTD') 😉

  • erb2000

    SSCommitted

    Points: 1697

    I sux at sql, but isn't the fact the error is in line 1 a huge hint?

  • cgumprich

    SSC Eights!

    Points: 857

    Hany Helmy (12/30/2014)


    Wow :w00t: 41% didn`t know about the "Extra comma in column list" will not prevent you from creating a table.

    I was one of them! 🙂

    Learned TWO things with this question. Nice one.

  • ManicStar

    SSCoach

    Points: 17992

    Nice one 🙂

  • sknox

    SSChampion

    Points: 12294

    erb2000 (12/30/2014)


    I sux at sql, but isn't the fact the error is in line 1 a huge hint?

    You'd think so, but not necessarily. The line number means different things depending on when SQL Server encounters the error.

    If it happens at parse time, the line number will be the exact line of the script. Try this for example:

    CREATE TABLE Test (

    c1 -- no data type; will fail at parse time with error at line 2

    );

    But if it happens at run time, the line number will be the first line number of the statement. For example:

    CREATE TABLE Test (

    c1 CHAR(10),

    c2 INT,

    c3 CHAR(1000) PRIMARY KEY -- error here on line 4, but error message says line 1

    );

  • Mike Hays

    SSCommitted

    Points: 1871

    Interesting, but I learned two things.

    Tested on SQL 2005, and received the following error message:

    Msg 2715, Level 16, State 7, Line 2

    Column, parameter, or variable #5: Cannot find data type DATE.

    I did not realize the DATE datatype started with SQL 2008.

  • Rich Weissler

    Hall of Fame

    Points: 3286

    Hany Helmy (12/30/2014)


    Wow :w00t: 41% didn`t know about the "Extra comma in column list" will not prevent you from creating a table.

    I didn't. And after a few hundred "Incorrect syntax near ','" from Select statements with an extra trailing comma and learning not to put that extra comma in... I don't even feel bad about not knowing. 🙂

  • Andre Ranieri

    SSCrazy

    Points: 2819

    It makes it hard to go onsite with a customer that's still using SQL 2005. That, and I keep forgetting at first why Intellisense isn't working.

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply