SQL Server Performance Forum – Threads Archive

problem in sp_helptext

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

---

what do you mean by extra spaces?

---

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

---

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.

---

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

---

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

---

When you run sp_helptext set the result mode to Text and run Madhivanan Failing to plan is Planning to fail

---

Try using sys.sql_modules instead select definition from sys.sql_modules
where object_id = object_id(‘your procedure name’)

---

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

---

You can also try SELECT OBJECT_DEFINITION(OBJCECT_ID(‘YourprocName’)) Roji. P. Thomas
http://toponewithties.blogspot.com

---

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

---

not working.. SURYA

---

There was a typo in Roji’s post SELECT OBJECT_DEFINITION(OBJECT_ID(‘Acetate_Delete’))
Are you using SQL Server 2005?

---

yes iam using sql server 2005 SURYA

---

<<
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 meet the requirement.. SURYA

---

<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 />

---

not working madhi SURYA

---

]]>