Tuesday, October 14, 2014

I really do not like to use Management Studio to generate a script of Stored Procedure, Function, View or Trigger and for last several years dealing was almost constantly hit by "sp_helptext" problem.

The Problem.
The problem occurs when an object's text has lines which are bigger than 255 characters.
In that case "sp_helptext" automatically cuts the line, even if it is on the middle of a word.

Here is an example:
1. Create test Stored Procedure:

USE TestDB;

GO

DECLARE @SQL NVARCHAR(MAX);

SET @SQL ='CREATE PROCEDURE sp_Test_sp_helptext AS SELECT '''

+REPLICATE('X',200)

+''' as
[Stored Procedure Select Result];';

EXEC (@SQL);

GO

2. Try to extract body of the Stored Procedure by "sp_helptext":

EXECsp_helptext'sp_Test_sp_helptext';

GO

As a result you get two lines of code, which you won't be able to use out of the box because "sp_helptext" splits column name right in the middle. Just look at the second line:
"tored Procedure Select Result];"

If you try to re-create this SP you'll get an error. In order to fix it you'd have to fix line split manually.

The Solution.
The easiest solution is to write your code with 255 character limitation in mind or use only SSMS GUI to extract that code back to you.

However, I think you are here because you do not like "the easy way" and here is another easy solution to extract SQL code, which has lines of code longer than 255 chars.

Script below extracts code of our earlier created procedure correctly, without line separation:

DECLARE @objname nvarchar(776)=N'sp_Test_sp_helptext';

DECLARE @ObjectText nvarchar(MAX)='';

DECLARE @SyscomTextnvarchar(MAX);

DECLARE @LineLen INT;

DECLARE @LineEnd BIT= 0;

DECLARE @CommentText TABLE(

LineId intIDENTITY(1,1),

Textnvarchar(MAX)collate
catalog_default);

DECLARE ms_crs_syscomCURSORLOCALFOR

SELECTtextFROMsys.syscomments

WHERE id =OBJECT_ID(@objname)and encrypted = 0

ORDERBY number, colid

FORREAD ONLY

OPEN ms_crs_syscom

FETCHNEXTfrom ms_crs_syscom into
@SyscomText

WHILE@@fetch_status>= 0

BEGIN

SET @LineLen =CHARINDEX(CHAR(10),@SyscomText);

WHILE @LineLen > 0

BEGIN

SELECT@ObjectText
+=LEFT(@SyscomText,@LineLen)

,@SyscomText
=SUBSTRING(@SyscomText,
@LineLen+1,
4000)

,@LineLen =CHARINDEX(CHAR(10),@SyscomText)

,@LineEnd = 1;

INSERTINTO
@CommentText(Text)

VALUES (@ObjectText)

SET @ObjectText ='';

END

IF @LineLen = 0

SET @ObjectText +=
@SyscomText;

ELSE

SELECT@ObjectText
= @SyscomText

,@LineLen = 0;

FETCHNEXTfrom ms_crs_syscom into
@SyscomText

END

CLOSEms_crs_syscom;

DEALLOCATEms_crs_syscom;

INSERTINTO @CommentText(Text)

SELECT @ObjectText;

SELECTtextFROM @CommentText

ORDERBY LineId;

GO

The Disclosure.
That code has been tested with code lines bigger than 255, 4000 and 8000 characters, but I wouldn't recommend to replace any section of "sp_helptext" in your production box because it might work incorrectly with types of objects different than simple Stored Procedures, Functions, Views or Triggers.

Friday, October 10, 2014

One application uses binary matrix to define user rights. That is very smart move, which is easy to implement, and it saves space and bust performance.

However, when you look at the value in SQL table it does not make ANY sense at all.

In order to reverse engineer you have to convert numbers to binaries in order to determine which bits are responsible for certain rights/operations.

At first I tried to do it manually, using calculator, but was quickly tired of it. So many copy-paste and switching modes between Decimal and Binary in addition to inability to process really big numbers bigger than "9223372036854775807", which is just NUMERIC(19) and you limited to see only 64 bits.