Post reply

String Max Length

  • March 19, 2009 at 3:48 am

    #198100

    I want to delete all the stored procedure I created in the database. I write the following statements:

    DECLARE @ssql varchar(8000)

    SET @ssql = ''

    SELECT @ssql = @ssql + 'Drop procedure ' + name + ' '

    FROM sys.objects

    WHERE type = 'p'

    SELECT LEN(@ssql)

    EXECUTE ( @ssql )

    An error is appear

    I found the max length of the @ssql is 4000.

    Thanks

  • March 19, 2009 at 4:19 am

    #962562

    sys.objects.name is NVARCHAR

    SELECT @ssql = @ssql + 'Drop procedure ' + CAST(name AS VARCHAR(128)) + ' '

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • March 19, 2009 at 5:02 am

    #962573

    An error is appear

    I found the max length of the @ssql is 4000.

    Hi,

    The issue is, eventhough you have set the @ssql variable as varchar(8000), it will take only 4000 characters. And the error you are getting is because of the dynamic query what you are constructing is having more than 4000 characters. Hence, I suggest you to use NVarchar(max) for @ssql variable. Hope this will solve the issue.

    Regards,

    Ashok S

  • March 19, 2009 at 8:33 pm

    #963283

    Thank you man!

    😛

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

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