Sunday 22 September 2013

SSMS 2012–SP_HelpText workaround

 

There is a well-known issue in SQL Server Management Studio 2012 with sp_helptext: It puts extra lines in grid result set. This problem is known by the Microsoft since 2011 and it seems that they really do  not want to fix it until the next version of SQL Server.

I will show how to reproduce the problem and how to make a workaround for it.

I: Reproduce the problem:

1: Open SQL Server Management Studio 2012

2: Create a sample stored procedure:

DoSomething
  1. create procedure dbo.DoSomething (
  2.     @string nvarchar(100)
  3. )
  4. as begin
  5.     set nocount on
  6.  
  7.     if @string is null or @string = ''
  8.         return 1
  9.  
  10.     select @string
  11. end
  12. go

3: Run SP_HelpText with Results to Grid mode:

- Make sure that Results to Grid option is set:

ResultsToGrid

- Run sp_helptext:

exec usp_helptext 'usp_helptext'

- Copy the resultset, and paste into the code window:

image

As we can se, as soon as we pasted the result into our code window, each line got an extra row.

 

II. Possible solutions:

- Most famous on the internet: use the Results to Text (CTRL-T) option and copy from there.

- Create our own version from the sp_helptext stored procedure.

- Switch to SSMS 2008R2

- Wait until the next version of SQL Server is released.

* Extra solution: use regexp replace on the copied result (we just have to replace all \n\n patterns to \n

I think there is no really good solution for the problem: Using Results to Text option means that we always have to switch between the text and grid mode during our work. As a database engineer I think that is only waste of time. We can create an own version of sp_helptext. The advantage is that we can use it really easy and we have a full control on the solution (that is why I have chosen this method. The disadvantage is that we have to compile our custom procedure in each database that we use. Of course we can switch to the older version of Management Studio too but I am not sure backward upgrading can be a proper solution for anything, and of course we can wait until the next release but if we work with it every day we must have a fix as soon as possible. And of course, we can use regexp replace but who has such time?

 

III. Let se usp_helptext

After I decided to create a custom version, I sat down and created the following script:

usp_helptext
  1. drop procedure dbo.usp_helptext
  2. go
  3.  
  4. create procedure dbo.usp_helptext(
  5.   @SPName sysname
  6. )
  7. as begin
  8.   set nocount on
  9.  
  10.   declare @LocalSPName varchar(100)
  11.   declare @Definition nvarchar(max)
  12.   declare @NewLine nchar(2) = CHAR(13) + CHAR(10)
  13.   declare @SPDefTable table (RowNumber int identity(1,1) primary key, Row nvarchar(500) not null)
  14.   declare @NLPos int
  15.   declare @Row nvarchar(max)
  16.   declare @Length int
  17.  
  18.   if @SPName is null or @SPName = ''
  19.     return
  20.  
  21.   set @LocalSPName = @SPName
  22.   select @Definition = definition from sys.all_sql_modules with(nolock) where object_id = object_id(@LocalSPName)
  23.  
  24.   if @@ROWCOUNT = 0
  25.     return
  26.  
  27.   set @Length = LEN(@Definition)
  28.  
  29.   while @Definition > ''
  30.   begin
  31.     set @NLPos = PATINDEX('%' + @NewLine + '%', @Definition)
  32.  
  33.     if @NLPos <> 0
  34.     begin
  35.       set @Row = LEFT(@Definition, @NLPos - 1)
  36.       set @Definition = SUBSTRING(@Definition, @NLPos + 2, @Length)
  37.     end
  38.     else
  39.     begin
  40.       break;
  41.     end
  42.  
  43.     insert into @SPDefTable (Row)
  44.     select @Row
  45.   end
  46.  
  47.   if @Definition > ''
  48.     insert into @SPDefTable (Row)
  49.     select @Definition
  50.  
  51.   select Row
  52.   from @SPDefTable
  53.   order by RowNumber
  54. end
  55. go

As we can see, I have to learn a lot about programming sql server. This solution is based on my structured programming studies: It uses a loop and conditional statements. I made so of course it works fine but it is not a good-looking solution if your solution is set-based. It must contain some selects or it not a solution Arc nagy mosollyal.
When I have finished with my usp_helptext procedure, I showed it to my boss. He likes challenges so immediately started to type something. When he finished, he send his solution to me and I started to test it. After I recognized all the boundary cases the transact-sql solution has born:

usp_helptext
  1. drop procedure dbo.usp_helptext
  2. go
  3.  
  4. create procedure dbo.usp_helptext(
  5.   @SPName varchar(100)
  6. )
  7. as begin
  8.   set nocount on
  9.  
  10.   declare @LocalSPName varchar(100)
  11.   declare @Definition nvarchar(max)
  12.   declare @NewLine nchar(2) = CHAR(13) + CHAR(10)
  13.   declare @Length int
  14.   declare @ErrMsg nvarchar(200)
  15.  
  16.   if @SPName is null or @SPName = ''
  17.     return
  18.  
  19.   set @LocalSPName = @SPName
  20.   select @Definition = definition from sys.all_sql_modules with(nolock) where object_id = object_id(@LocalSPName)
  21.  
  22.   if @@ROWCOUNT = 0
  23.   begin
  24.     set @ErrMsg = N'Cannot find stored procedure: ' + @LocalSPName
  25.  
  26.     raiserror (@ErrMsg, 16, 1)
  27.   end
  28.  
  29.   set @Length = LEN(@Definition)
  30.  
  31.   ;with a as
  32.   (
  33.     select cast(1 as bigint) start, PATINDEX('%' + @NewLine + '%', @Definition) curlength, 0 as stopthere
  34.     union all
  35.     select a.start+a.curlength, PATINDEX('%' + @NewLine + '%', SUBSTRING(@Definition, a.start+a.curlength, @Length)) , case when PATINDEX('%' + @NewLine + '%', SUBSTRING(@Definition, a.start+a.curlength, @Length)) = 0 then 1 else 0 end
  36.     from a
  37.     where a.start+a.curlength < @Length and a.stopthere = 0
  38.   )
  39.   select REPLACE(REPLACE(SUBSTRING(@Definition, a.start, a.curlength), CHAR(10), ''), CHAR(13), '')
  40.   from a
  41.   option (maxrecursion 0)
  42.  
  43. end
  44. go

I am proud to this solution. It is really a set-based solution and it shows what is the difference between an usual programmer and an sql developer. The golden rule is: If we want to use a loop we just have to sit down and search a proper select statement.

Friday 29 March 2013

The real multi-lingual site

Multi-Language site

We can find hungarian, english and german languages in one form.