I have extra carriage return inserted using SP_HELPTEXT after generating stored procedures dynamically in a VB.net application with ADODB command

Question

I have a VB.net application that gets the code to create a SP from a file and then execute it in order to create the SP, using the execute method of an ADODB.command object:

mcmd = New ADODB.Command
With mcmd
    .CommandType = ADODB.CommandTypeEnum.adCmdText
    .let_ActiveConnection(mcnn)
    .CommandText = mStrSql
    .Parameters.Refresh()
    .CommandTimeout = 0
    .Execute(lngMVV, , ADODB.ExecuteOptionEnum.adExecuteNoRecords)
End With 

The SP is created but here is the situation.

This is the original SP:

CREATE PROCEDURE [dbo].[spPrueba06] 
(
    @idcvedef int OUTPUT  ,  
    @anio smallint OUTPUT    
)    
AS 
BEGIN 
/* 
    Nombre Objeto   : [dbo].[spPrueba06] 
    Versión         : 001 
    Usuario Creación: ALBERTO VAZQUES GUTIERREZ
    Fecha Creación  : 14/01/2016 
    Módulo          :  
    Reportes        : NA 
    Requerimientos  :  
    Descripción     : SP de lectura de row de la tabla ClaveEjercicio 
*/
/*      
    -->>Modificación: 001JGB20160114 Creación del SP  
*/ 
SET NOCOUNT ON 
SELECT  
    @idcvedef = COALESCE(idcvedef,'') ,  
    @anio = COALESCE(anio,'')   

    FROM PppCveDef 
    WHERE  
    anio = @anio 
OPTION(OPTIMIZE FOR UNKNOWN) 
SET NOCOUNT OFF 
END  

And after the app runs, if i use SP_HELPTEXT with the "Results to text" SQL Server option enabled I get the following:

CREATE PROCEDURE dbo.spPrueba06 
(
    @idcvedef int OUTPUT  ,  
    @anio smallint OUTPUT    
)    
AS 
BEGIN 
/* 
    Nombre Objeto   : [dbo].[spPrueba06] 
    Versión         : 001 
    Usuario Creación: ALBERTO VAZQUES GUTIERREZ
    Fecha Creación  : 14/01/201
6 
    Módulo          :  
    Reportes        : NA 
    Requerimientos  :  
    Descripción     : SP de lectura de row de la tabla ClaveEjercicio 
*/
/*      
    -->>Modificación: 001JGB20160114 Creación del SP  
*/ 
SET NOCOUNT ON 
SELECT  
    @idcvedef = COALESCE(idcvede
f,'') ,  
    @anio = COALESCE(anio,'')   

    FROM PppCveDef 
    WHERE  
    anio = @anio 
OPTION(OPTIMIZE FOR UNKNOWN) 
SET NOCOUNT OFF 
END 

So extra Carriage Return or Enter's are inserted. First after the line:

Fecha Creación  : 14/01/201

and then after the line:

@idcvedef = COALESCE(idcvede

If i locate the SP in the object explorer and do right click-Modify I get the code without the CR:

USE [DBSIIF_AGS_MODELO_2016_12_22]
GO
/****** Object:  StoredProcedure [dbo].[spPrueba06]    Script Date: 26/12/2016 05:40:17 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spPrueba06] 
(
    @idcvedef int OUTPUT  ,  
    @anio smallint OUTPUT    
)    
AS 
BEGIN 
/* 
    Nombre Objeto   : [dbo].[spPrueba06] 
    Versión         : 001 
    Usuario Creación: ALBERTO VAZQUES GUTIERREZ
    Fecha Creación  : 14/01/2016 
    Módulo          :  
    Reportes        : NA 
    Requerimientos  :  
    Descripción     : SP de lectura de row de la tabla ClaveEjercicio 
*/
/*      
    -->>Modificación: 001JGB20160114 Creación del SP  
*/ 
SET NOCOUNT ON 
SELECT  
    @idcvedef = COALESCE(idcvedef,'') ,  
    @anio = COALESCE(anio,'')   

    FROM PppCveDef 
    WHERE  
    anio = @anio 
OPTION(OPTIMIZE FOR UNKNOWN) 
SET NOCOUNT OFF 
END 

So the problem occurs only with SP_HELPTEXT. Here this is important because we use a lot of SP and many times there is the need of locate the code of certain SP in order to use it to create the SP in another DB, for example. And this is easier than locate the SP in the object explorer.

Another thing is that if i create the SP running the original code directly in the SQL Server Management Studio and then I use SP_HELPTEXT, I get the code without the CR. So I assume that the problem is in the VB.Net app

The only pattern that i have found is that happens each 26X characters more o less.

Also, while debbuging i found out that if i stop the execution in the .Execute method of the ADODB command and i check for the value of the CommandText, its value contains the original code without the CR.

I hope i've explained myself properly. Any help is appreciated.

Answer 1

Thanks for the answers. Sorry for the delayed response. I already solved the problem. Yes, it had to do with the type of line-terminator. In the VB app the text that contains the variable mStrSql which is executed by the ADODB.Command, is manipulated a lot in the app before that point, so after following this text manipulation i found this line:

mStrSql = mStrSql.Replace(vbCrLf, vbCr)

So all the line-terminators vbCrLf of the text were replaced by vbCr. After the command was executed and the SP created, using SP_HELPTEXT with the "Results to text" showed me the text in which i could see the CR as a new line, but what it seems to happen here is that internally sp_helptext doesnt take the CR as an end of a line, and because it defines the max lenght of the line as 255, when this lenght is reached in the text, it inserts a new line in the result table. The code of SP_HELPTEXT has the following comment:

** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/

and below in the code it has the following line:

select @CurrentPos =   CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)

which searches for the line-terminator as char(13)+char(10) (LFCR), if it finds it, a new line containing the text until that point is inserted, if not, a new line containing the text with the 255 lenght is inserted in the result table.

So the problem was the incorrect line-terminator CR. I also found the suggestion of creating a new version of sp_helptext here But since i found the detail that caused the problem i didnt need to do this.