Post reply

Object text too long error?????

  • June 28, 2003 at 9:45 am

    #81928

    I'm working in a SQL Server 6.5 database. I wrote a SP that got a little long (2700 lines) It debugged fine and works like a charm, but when I tried to do a "Create Procedure" I got this error. I don't really want to hack this into 5 SP (my boss will kill me). Is there anything I can do????

    Object text is too long - can only support 255 rows in Syscomments.

    Edited by - jw48 on 06/28/2003 09:47:59 AM


    JW

  • June 29, 2003 at 4:27 pm

    #463588

    You've hit the SQL 6.5 hard limit of 64k bytes in a stored procedure. (this limit became about 250mb with the introduction of SQL 7.0). This is 255 bytes of SP text * 255 rows in syscomments.

    Your only real options are to streamline your SP, or break it down into functional (and reusable?) components.

    Your boss won't be happy I'm afraid.

    Another option is break down the SP into numbered procedures with the same name:

    CREATE PROCEDURE MyProc;1

    .

    .

    GO

    CREATE PROCEDURE MyProc;2

    .

    .

    GO

    etc etc


    Cheers,
    - Mark

  • June 29, 2003 at 6:30 pm

    #463594

    Suppose you can remove layout spaces, carriage returns, etc. from the code and get it to recompile.

    Rather go for McCork suggestion of functional breakouts(sub procs).

  • June 30, 2003 at 7:23 am

    #463639

    Thank you very much mccork & 540... (whatever!!:-))

    I can put all the "CREATE PROCEDURE's" in the same script??

    JW


    JW

Viewing 4 posts - 1 through 4 (of 4 total)

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