Post reply

Sp_Helptext not working properly

  • May 10, 2012 at 3:06 am

    #273878

    What is difference between "Sp_Helptext" and "generating script using right click on stored procedure and click on modify" script.

    In few case both option giving different result set in case comment style.

    Suppose there is line in Sp like below in comment

    -- ABCDBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

    So If take script using sp_helptext then igive result as below

    --ABCDBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

    BBBBBBBBBBBBBBBBBBBBB

    So if I alter this sp it give error

    Please suggest.

    Thanks

    Kuldeep

  • May 10, 2012 at 3:25 am

    #1485551

    panwar.jt (5/10/2012)

    What is difference between "Sp_Helptext" and "generating script using right click on stored procedure and click on modify" script.

    In few case both option giving different result set in case comment style.

    Suppose there is line in Sp like below in comment

    -- ABCDBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

    So If take script using sp_helptext then igive result as below

    --ABCDBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

    BBBBBBBBBBBBBBBBBBBBB

    So if I alter this sp it give error

    Please suggest.

    Thanks

    Kuldeep

    have you tried using

    select object_definition(id) from sysobjects where name='myproc'

    this might yield better results

    MVDBA

  • May 10, 2012 at 3:35 am

    #1485554

    panwar.jt (5/10/2012)

    What is difference between "Sp_Helptext" and "generating script using right click on stored procedure and click on modify" script.

    In few case both option giving different result set in case comment style.

    Suppose there is line in Sp like below in comment

    -- ABCDBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

    So If take script using sp_helptext then igive result as below

    --ABCDBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

    BBBBBBBBBBBBBBBBBBBBB

    So if I alter this sp it give error

    Please suggest.

    Thanks

    Kuldeep

    If may be occuring because of some formatting issue. Have you tried changing the restults to text to view the output? I think that may help.

    1. In SSMS, go to Query menu

    2. In the submenu "Results To" choose "Results to Text"

    3. Now run your query i.e. sp_helptext

    It should resolve the issue.

    Sujeet Singh

  • May 10, 2012 at 4:31 am

    #1485583

    Hi

    I had tried all possible options but relult is same.

    And its due to using single line comment for multiple lines.

  • May 10, 2012 at 4:46 am

    #1485593

    try going in to management studio options and in the query results section set the "maximum number of characters displayed in each column" to a figure higher than 256

    not sure if that will help, but might do

    MVDBA

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

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