SQL Server Performance

problem in sp_helptext

Discussion in 'SQL Server 2005 General Developer Questions' started by vsnreddi, Nov 30, 2006.

  1. vsnreddi New Member

    hi when i try to retrieve stored proc text iam getting some extra spaces, is there any settings for this

    sp_helptext <spname>

    SURYA

  2. smy New Member

    what do you mean by extra spaces?
  3. Madhivanan Moderator

    Those extra spaces are part of the procedure


    Create procedure test
    as

    Select

    'test'


    Now see what sp_helptext 'test' shows

    Madhivanan

    Failing to plan is Planning to fail
  4. satya Moderator

    Is there any problem with those extra spaces?
    I don't think it will have any in execution of Stored Procedure.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  5. vsnreddi New Member

    see the problem below, after getting the text when i recompile the sp, its giving errors

    Case "Excel" ''For exporting into Excel
    'XL.FileName = "C:WINDOWSDesktopReports" & strScheme & ".xls"
    'XL.Export reps.Pages
    Case "HTML" 'Html format
    ' HTM.FileNamePrefix = "C:WINDOWSDesktopReports" & strScheme & ".htm"
    ' HTM.Export reps.Pages
    Case "Text" ''IN notepad format
    'txt.FileName = "C:WINDOWSDesktopReports" & strScheme & ".txt"
    'txt.Export reps.Pages
    Case "RTF" 'Word Document
    ' rtf.FileName = "C:WINDOWSDesktopReports" & strScheme & & ".rtf"
    ' rtf.Export reps.Pages
    Case "VIEW" 'Show into Privew Contros
    frmPreview.arv.ReportSource = reps
    frmPreview.Show 1
    Case Else 'Default pdf
    pdf.FileName = "C:WINDOWSDesktopReports" & strScheme & ".pdf"
    pdf.Export reps.Pages
    End Select

    SURYA

  6. DilliGrg Member

    quote:Originally posted by vsnreddi

    see the problem below, after getting the text when i recompile the sp, its giving errors

    Case "Excel" ''For exporting into Excel
    'XL.FileName = "C:WINDOWSDesktopReports" & strScheme & ".xls"
    'XL.Export reps.Pages
    Case "HTML" 'Html format
    ' HTM.FileNamePrefix = "C:WINDOWSDesktopReports" & strScheme & ".htm"
    ' HTM.Export reps.Pages
    Case "Text" ''IN notepad format
    'txt.FileName = "C:WINDOWSDesktopReports" & strScheme & ".txt"
    'txt.Export reps.Pages
    Case "RTF" 'Word Document
    ' rtf.FileName = "C:WINDOWSDesktopReports" & strScheme & & ".rtf"
    ' rtf.Export reps.Pages
    Case "VIEW" 'Show into Privew Contros
    frmPreview.arv.ReportSource = reps
    frmPreview.Show 1
    Case Else 'Default pdf
    pdf.FileName = "C:WINDOWSDesktopReports" & strScheme & ".pdf"
    pdf.Export reps.Pages
    End Select

    SURYA




    Can you post the sql code for the stored procedure instead of FE code? It's not clear where you are getting the errors. I have never had problem with sp_helptext and recompiling the sp again.


    Thanks,
    DilliGrg
  7. vsnreddi New Member

    this code is from sp only.
    see the problem the quote is coming next line, so while compiling iam getting the errors

    Case "RTF" 'Word Document
    '

    SURYA

  8. Madhivanan Moderator

    When you run sp_helptext set the result mode to Text and run

    Madhivanan

    Failing to plan is Planning to fail
  9. thomas New Member

    Try using sys.sql_modules instead

    select definition from sys.sql_modules
    where object_id = object_id('your procedure name')
  10. vsnreddi New Member

    hi thomas
    i am getting the 255 charcters only..one row only..im not getting the total sp text..

    madhi:
    can u please give me the correct syntax of set result mode

    SURYA

  11. Roji. P. Thomas New Member

  12. thomas New Member

    You need to change your Management Studio Query Editor settings to return more than 256 characters.

    Tools.. Options.. Query Results.. SQL Server ... Results To Text .. change Max Number of Characters Displayed Per Column to something big, 8000 for example
  13. vsnreddi New Member

    quote:Originally posted by Roji. P. Thomas

    You can also try

    SELECT OBJECT_DEFINITION(OBJCECT_ID('YourprocName'))

    Roji. P. Thomas
    http://toponewithties.blogspot.com


    not working..

    SURYA

  14. thomas New Member

    There was a typo in Roji's post

    SELECT OBJECT_DEFINITION(OBJECT_ID('Acetate_Delete'))


    Are you using SQL Server 2005?
  15. vsnreddi New Member

    yes iam using sql server 2005

    SURYA

  16. Madhivanan Moderator

    <<
    madhi:
    can u please give me the correct syntax of set result mode
    >>

    When you use QA, press Control+T and execute sp_helptext 'proc'

    Madhivanan

    Failing to plan is Planning to fail
  17. vsnreddi New Member

    not meet the requirement..

    SURYA

  18. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by thomas</i><br /><br />There was a typo in Roji's post<br />SELECT OBJECT_DEFINITION(OBJECT_ID('Acetate_Delete'))<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Thanks [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  19. vsnreddi New Member

    quote:Originally posted by Madhivanan

    <<
    madhi:
    can u please give me the correct syntax of set result mode
    >>

    When you use QA, press Control+T and execute sp_helptext 'proc'

    Madhivanan

    Failing to plan is Planning to fail

    not working madhi

    SURYA

Share This Page