March 5, 2013 at 6:43 am
Hello all, I have seen some strange things in my SQL Server 2008 enterprise edition. whenever I do sp_helptext for any procedure, some of the random linescome as two lines and this risks the code break. For example, if I have a code like this:
create procedure Temp_Procedure
@p1 varchar(20)
as
begin
select * from dbo.Table1
where Col1 = @p1
and Col2 = 'Temporary'
end
It shows as:
create proce
dure Temp_Procedure
@p1 varchar(20)
as
begin
select * from dbo.Table1
where Col1 = @p1
and Col2 = 'Temp
orary'
end
It looks simpler in this example but where the codes are of thousand lines, it becomes headache to format it again and again. I don't know if there is any environment variable setting or something. I have checked many a options but nothing worked.
March 5, 2013 at 6:55 am
You need to save the it as SQL file and same way you need to open via SQL file.
While saving if you save as SQL file you can overcome this.
If you take it as plan text you will get this problem.
Thanks!
March 5, 2013 at 7:05 am
Yes, but then is it a known bug with SQL ? because sp_helptext is the most basic and efficient way of getting the proc definition.
March 5, 2013 at 7:20 am
sqlnaive (3/5/2013)
Yes, but then is it a known bug with SQL ? because sp_helptext is the most basic and efficient way of getting the proc definition.
If you go to the object explorer tree of that particular object(Stored Procedure here) you can see Script Stored Procedure as "CREATE","ALTER".. That is the best way to deal with stored procedure scripts.
March 5, 2013 at 7:24 am
sqlnaive (3/5/2013)
Yes, but then is it a known bug with SQL ? because sp_helptext is the most basic and efficient way of getting the proc definition.
First, I don't use sp_helptext to script out any code. I may use it for a quick look, but that is it. I prefer to script code from the ObjectExplorer pane. This way it keeps any formatting of code I have applied while writing the code.
March 5, 2013 at 3:54 pm
Check out OBJECT_DEFINITION(). That returns a single nvarchar(MAX) that is the whole procedure, rather than 255 characters per row (artificial "line"?) that sp_helptext seems to do.
I find it useful when I need to get the full definition of an object within TSQL code.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 6, 2013 at 1:49 am
Yuvipoy/Lynn,
I agree with both of you. Infact i'm checking my code that way only. However my concern is if SQL is giving such an efficient way of checking a code then it should be useful to endusers. Going to object explorer and looking for that particular object and thereafter getting that script out is hard work as compared to just writing sp_helptext.
ScottPletcher (3/5/2013)
Check out OBJECT_DEFINITION(). That returns a single nvarchar(MAX) that is the whole procedure, rather than 255 characters per row (artificial "line"?) that sp_helptext seems to do.I find it useful when I need to get the full definition of an object within TSQL code.
Scott,
I tried your option but it has text limit with it. My procedure has thousand of codes.
March 6, 2013 at 7:22 am
Anyone ??????
March 6, 2013 at 7:24 am
What more do you want? If you think it is a bug report it on Connect.
March 6, 2013 at 7:35 am
Just want to clear if this is something related to any configuration setting ? Because I have never observed not heard this kind of problem anywhere.
March 6, 2013 at 7:38 am
There's a workaround here: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/2e9b611f-0fe7-43ff-a420-5e96d14334d8
declare @name nvarchar(max)
set @name = 'proc_name'
declare @txt nvarchar(max)
declare @n int
declare @i int
select @i=1, @n=COUNT(*) from syscomments where id=OBJECT_ID(@name)
while @i<=@n
begin
select @i=@i+1, @txt=text from syscomments
where id=OBJECT_ID(@name) and colid=@i
-- process @txt as needed
print @txt
end
March 6, 2013 at 7:46 am
Note syscomments is due to be removed from SQL so this may not work in future versions.
March 6, 2013 at 8:00 am
Have you looked at the Options in SSMS? Under "Tools", "Options", "Query Results", "Results to Text" what is "Maximum number of characters displayed in each column" set to? It defaults to 256, try changing it to 8192 and see what happens.
Otherwise, don't use sp_helptext becasue this is a known limitation.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 6, 2013 at 10:59 am
sqlnaive (3/6/2013)
Yuvipoy/Lynn,I agree with both of you. Infact i'm checking my code that way only. However my concern is if SQL is giving such an efficient way of checking a code then it should be useful to endusers. Going to object explorer and looking for that particular object and thereafter getting that script out is hard work as compared to just writing sp_helptext.
ScottPletcher (3/5/2013)
Check out OBJECT_DEFINITION(). That returns a single nvarchar(MAX) that is the whole procedure, rather than 255 characters per row (artificial "line"?) that sp_helptext seems to do.I find it useful when I need to get the full definition of an object within TSQL code.
Scott,
I tried your option but it has text limit with it. My procedure has thousand of codes.
I suspect that's just a display limit. SSMS only shows a limited number of characters for some data types. You can find the specifics in SSMS under "Tools" / "Options..." / "Query Results".
To confirm, try writing the results returned by OBJECT_DEFINITION() to an nvarchar(max) column and checking the length of that column: I think you'll find that the entire code is actually returned by that function.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 7, 2013 at 3:07 am
ScottPletcher (3/6/2013)
To confirm, try writing the results returned by OBJECT_DEFINITION() to an nvarchar(max) column and checking the length of that column: I think you'll find that the entire code is actually returned by that function.
It is; but you will still hit the 8192 character limit of SSMS' results to text if the definition's longer than that.
There's always powershell:
(gci 'sqlserver:\sql\machine\instance\databases\dbname\storedprocedures' | where {$_.name -eq 'procname'}).Script() + "GO"
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply