Post reply

Primary Key- char 12

  • August 4, 2009 at 10:29 am

    #137561

    Hi

    We have an existing application that uses a table with a Primary ket tat has been set to char[12].

    This was developed many years back and due to the volume of data need to convert char12- char 15.

    There are other aproc's using this field as input param . Is there a way to do this onversion with out effecting existing code?

    Thanks

  • August 4, 2009 at 10:40 am

    #1034407

    i would definitely do this using the SSMS GUI on a test database;

    since it is a PK,the table will probably be dropped and recreated, and any indexes, foreign keys and other references need to be dropped and recreated, which is one of the things the GUI handles so well for us.

    after that, if you do sp_refreshview 'YourTableName', all the views that reference that table will get refreshed with the new larger column definition;i forgot how to do procedures that reference the table in question, I've got to take a look in BOL; since you know some parameters for procs are CHAR(12), you'll need to fix those manually, but others just need to be recompiled i think.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 4, 2009 at 9:26 pm

    #1034603

    pzmrcd (8/4/2009)

    Hi

    We have an existing application that uses a table with a Primary ket tat has been set to char[12].

    This was developed many years back and due to the volume of data need to convert char12- char 15.

    There are other aproc's using this field as input param . Is there a way to do this onversion with out effecting existing code?

    Thanks

    No. You will need to change the input parameters for those stored procedures from 12 to 15 as well. Just changing the data in the table will not be enough.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 5, 2009 at 1:57 am

    #1034646

    No. You will need to change the input parameters for those stored procedures from 12 to 15 as well. Just changing the data in the table will not be enough.

    And don't forget to change any other variables/table variables/temp tables etc inside the stored procedures that may be used to hold temporary copies of the primary key.

    It's going to be a long slog, but you will probably need to look at each of these stored procedures in detail (or at the very least search for all instances of "12" in those procedures).

  • August 5, 2009 at 9:31 am

    #1034890

    Heh... that's why I plan on "growth" and I'll always make VARCHAR variables about 50% bigger than they need to be.

    I do wish that MS would come out with type inheritance in the definition of parameters and variables.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 5, 2009 at 10:30 am

    #1034940

    Jeff Moden (8/5/2009)I do wish that MS would come out with type inheritance in the definition of parameters and variables.

    Isn't that what UDTs go some way to providing, as long as they are alias based?

  • August 5, 2009 at 9:33 pm

    #1035164

    Ian Scarlett (8/5/2009)

    Jeff Moden (8/5/2009)I do wish that MS would come out with type inheritance in the definition of parameters and variables.

    Isn't that what UDTs go some way to providing, as long as they are alias based?

    I suppose, but it just doesn't seem like you should have to do that.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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