Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2005
    Posts
    92

    Unanswered: Changing column's name in a temp table

    Dear All,

    I'm trying to alter the name of several columns' in a table which gets created in a stored procedure.

    trying to use:

    exec sp_rename '#tblBd.week1', '2007_18', 'COLUMN'

    I get:

    Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163
    Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

    There is no mistype, the table name and column name are correct.

    Can temp table's column names not be altered?

    If yes, how?

    Thanks in advance!
    Last edited by gorgenyi; 07-10-07 at 12:15.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi gorgenyi

    Let's cover the obvious stuff first of all to get an idea of what you are doing and why.
    1) Why not define your temp table with the correct column names in the first place?
    2) Why not alias your column names in the result set you create from the temp table?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2005
    Posts
    92
    ok, here it goes.

    I created a report with a stored procedure which displays resource allocation to projects in the BD pipeline week by week for the next 10 weeks ahead.

    the end reult looks like this:

    Project name, w1, w2, w3, w4, w5, w6 etc...
    Prj A 1 2 2 1
    Prj B 1 2 2 2.5 2 2
    Prj C .5 1 1 1.5 1
    ...


    I'm using w1, w2, w3 for the weeks ahead during the stored procedure for multiple joins, however I'd like to change them to the always relevant week numbers programatically in the follwoing format '2007-17', '2007-18', '2007-19' ... etc at the end so I can publish these as the datagrid's header.

    Did I explain clearly? What would you recomend? Thanks for your help on this.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are making the all too common mistake of trying to use SQL and/or Query Analyzer as a reporting environment. It is not designed for this.
    What you are trying to do is modify the way the data is displayed, and this is a job for Crystal Reports/Active Reports/Reporting Services/MS Access/Excel, or whatever reporting/interface platform you are using.
    Dynamic column names are BAD, because then whatever reporting tool you use will never know what output schema to expect.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    this is a job for Crystal Reports/Active Reports/Reporting Services/MS Access/Excel, or whatever reporting/interface platform you are using.
    Somehow you conspired to miss with all those I think. I suspect this is just some .NET front end.

    You can (I think) dynamically set up your column headers in a datagrid but I am afraid I cannot remember how.

    blindman is right though - this is best handled in your front end rather than SQL.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or .Net or ASP.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Mar 2005
    Posts
    92
    It just seemed very simple, and must say I do not have experience of the reporting tools. Here is what I did:

    temp table to get next 10 weeks ids
    temp table to get active BD items and the 10 columns with 0 values
    then a double loop where I update each cell with a resource number to the appropriate BD item and the appropriate week number.

    Dispay the data in datagrid, with headings for the weeks as Week 1, Week 2, etc. while adding the sums up / week in ASP.NET and do various other things like colouring background etc.

    But I'd like to get the actually internal week reference displayed on the grid, which of course chnage weekly, so want to do it dynamicaly. But yes, point taken on ASP.NET won't know what col name they will be.

    And yes, this is ASP.NET front end.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Some ideas from the web. Maybe consider posting in an ASP.NET site if you can't follow them. You defo do not want to do this at the database.
    http://www.thescripts.com/forum/thread320779.html
    http://www.experts-exchange.com/Prog..._20976549.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •