Post reply

Create Table FAILED!?

  • December 29, 2014 at 9:43 pm

    #397815

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

  • December 30, 2014 at 12:39 am

    #1766923

    Became a bit wiser today, thanks for the question.

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

  • December 30, 2014 at 1:10 am

    #1766928

    I googled "Msg 2714" and answered correctly.

  • December 30, 2014 at 2:27 am

    #1766941

    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).

  • December 30, 2014 at 2:50 am

    #1766950

    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!

    🙂

  • December 30, 2014 at 4:30 am

    #1766963

    Carlo Romagnano (12/30/2014)

    I googled "Msg 2714" and answered correctly.

    +1 Same 🙂

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • December 30, 2014 at 4:32 am

    #1766964

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

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • December 30, 2014 at 4:47 am

    #1766966

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

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • December 30, 2014 at 5:11 am

    #1766970

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

  • December 30, 2014 at 6:29 am

    #1766984

    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.

  • December 30, 2014 at 6:32 am

    #1766985

    Nice one 🙂

  • December 30, 2014 at 7:17 am

    #1766991

    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

    );

  • December 30, 2014 at 8:09 am

    #1767008

    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.

  • December 30, 2014 at 9:18 am

    #1767026

    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. 🙂

  • December 30, 2014 at 2:48 pm

    #1767089

    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 27 total)

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