Post reply

varchar(255) - syscomments - Update

  • November 12, 2008 at 3:27 am

    #77134

    Hi All,

    We recently developed some new modules in an existing project. We also moved it to UAT which Business users are checking it now. I have created 32 new tables and 81 stored procedures for that new module. For most of the varchar fields (which are used as the input parameters in the procedures), i declared like varchar(255). But since users are passing more than 255 characters to all of those parameters, the remanining characters were exclueded. So now i have to change all those things to varchar(2000).

    I Just thought immediately to update the TEXT column of syscomments table. But it may be a wrong idea. is it possible to update the TEXT column of syscomments table ? if not, what would be the best way to do this ?

    Note : Why i declared varchar length to 255 ?

    Actual requirement was, user will pass the manager id as below.

    '~00ZFJ~00764~0030D~00492~00Z59~0089A~00Z7R~0070A~00ZG7~00Z4Y~0020A~0021A~00617~00293~00ZKL~00212~00ZFA~00185~0003N~0068A~00ZFK~00321~00573~00375~00Z4M~0025A~00312~00146~'

    I declared the above input parameter as below

    @ManagerList varchar(255)

    I have to split it into seperate values. I used Tally table to split it. But i though the user didn't give more than 20 or 25 managers. But my thought was wrong. They will give more than 25 manager.

    Inputs are welcome !

    karthik

  • November 12, 2008 at 4:06 am

    #897410

    Since it seems that manager codes are always 5 characters evenly spaced at 6 positions, try this

    DECLARE@Sample VARCHAR(8000)

    SET@Sample = '~00ZFJ~00764~0030D~00492~00Z59~0089A~00Z7R~0070A~00ZG7~00Z4Y~0020A~0021A~00617~00293~00ZKL~00212~00ZFA~00185~0003N~0068A~00ZFK~00321~00573~00375~00Z4M~0025A~00312~00146~'

    SELECTtheCode

    FROM(

    SELECTSUBSTRING(@Sample, 6 * Number + 2, 5) AS theCode

    FROMmaster..spt_values

    WHEREType = 'P'

    ) AS d

    WHEREtheCode > ''

    N 56°04'39.16"
    E 12°55'05.25"

  • November 12, 2008 at 5:17 am

    #897428

    Thanks Peso !

    I used Tally table to split the values.

    #1,

    do you feel any performance difference using Tally table logic and your logic ?

    #2,

    I want to update syscomments table. is it possible ? if not ,what is the work around to do it ?

    karthik

  • November 12, 2008 at 5:25 am

    #897430

    my actual requirement is

    "I want to update/replace varchar(255) to varchar(2000) for that (may be 25 or 30 out of 81) procedures".

    Can't we update or replace the TEXT column of syscomments table directly ?

    karthik

  • November 12, 2008 at 5:58 am

    #897442

    wouldn't it be better to generate a script of the said procedures and do a replace for varchar(255) with varchar(2000) and execute them.

    I don't think it is advisable to modify system tables.

  • November 12, 2008 at 7:19 am

    #897500

    Do not, do not, do not ever directly update the system tables. Doing so is asking for big problems in the future.

    Generate the scripts of the procedures, search and replace the varchar definitions and run the procedures.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 12, 2008 at 7:26 am

    #897510

    is there any other work around to do the same without generating script and replacing them with varchar(255) ?

    karthik

  • November 12, 2008 at 7:37 am

    #897530

    Use Enterprise manager to generate a script that contains all of the procedures and then do a find and replace. Even if you have to check each location before doing the replace, it shouldn't take more than 10 min.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

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