none
Como Obtener el ResultsSet de un SP con que utiliza sp_executesql en un informe RDLC RRS feed

  • Pregunta

  • Necesito poder obtener el conjunto de resultados que generará un procedimiento almacenado que tiene una consulta dinámica ( sp_executesql), el problema es que al definir el DataSource, que en este caso sería el procedimiento almacenado, no devuelve ninguna columna como un Resultado por el mismo del DataSourceasistente.

    Tengo otro informe (rdlc) de que el procedimiento almacenado tiene implícitamente un Select para poder obtener el ResultsSet que devuelve el conjunto de resultados.

    Estaba pensando en poder capturar la instrucción que resuelve la sp_executesqlen una variable ( @instruction) y luego tomarla y ejecutarla nuevamente como Seleccionar desde el asistente de configuración de consultas TableAdapter.

    Adjunto ambos stored procedures:

    ALTER PROCEDURE [dbo].[XXX_SP_REPORTING_SERVICES_DESPACHOS] @xmlParametros XML, @intruccionSelect Varchar(MAX) OUTPUT AS
    
    BEGIN
    
    SET NOCOUNT ON
    SET DATEFORMAT YMD
    
    DECLARE
    ...
    ...
    ...
    Set @instruccion  = N'SELECT D.Despacho, D.Fecha as FechaD, D.Aduana, D.Paisorigen as Pais, D.Garantia, 
    CASE WHEN D.TipoGarantia="E" THEN "Efectivo" 
    WHEN D.TipoGarantia="C" THEN "Caución" 
    WHEN D.TipoGarantia="M" THEN "Mixto" 
    ELSE null END as Tipo, 
    D.Efectivo, D.FechaGarantia as Fecha, D.Compania, D.Poliza, D.FechavtoGarantia as Fechavto, D.Monto as Importe, 
    De.Desde, De.Hasta, convert(varchar(254), D.Observacion) as Observacion 
      FROM Despachos D (nolock)
    LEFT JOIN Despachosestampillas De (nolock) ON D.Despacho=De.Despacho
      WHERE 1=1 ' +  @strWhere + '
      ORDER BY D.Despacho, De.Desde'
    
    
      exec sp_executesql
    @instruccion OUTPUT, N'@estampilla integer, @desfecha datetime, @hasfecha datetime',                                               
               @estampilla=@estampilla, @desfecha=@desfecha, @hasfecha=@hasfecha
      
     end
    
    END


    ALTER PROCEDURE [dbo].[XXX_SP_REPORTING_SERVICES_CENTRO_APROPIACION]
    @xmlParametros XML AS
    
    BEGIN
    
    SET NOCOUNT ON;
    SET CONCAT_NULL_YIELDS_NULL OFF
    SET ANSI_NULLS OFF
    SET QUOTED_IDENTIFIER OFF
    SET NOCOUNT ON
    
    DECLARE
    ...
    ...
    ...
            exec sp_executesql 
    @instruccion, @instruccion2,@xmlParametros=@xmlParametros,
    @Sistema=@Sistema OUTPUT
    END
    
    
    IF @Sistema != 'C'
    SELECT CENPREFI, CODCEN, NOMBRE, ADMNEGOCIOS
    FROM dbo.CENTROSAP
    (NOLOCK)
    ORDER BY CENPREFI
    ELSE
    SELECT CENPREFI, CODCEN, NOMBRE
    FROM dbo.CENTROSAP
    (NOLOCK)
    ORDER BY CENPREFI
    END

    Es necesario obtener en ambos casos el conjunto de resultados para proporcionar el DataSourcedefinido en el informe RDLC.

    Muchas gracias!


    • Editado NicolasHC viernes, 4 de enero de 2019 14:43
    viernes, 4 de enero de 2019 14:36

Respuestas

  • Si executo desde SSMS, me trae el ResultSet esperado[...] cuando lo ejecuto desde la consola de Visual Studio, la misma no me trae los resultados:

    Cuando suceden estas cosas, típicamente es por una de estas dos razones:

    La primera es que sea debido a una diferencia de credenciales, de manera que el usuario no es el mismo cuando se ejecuta desde la consola y cuando se ejecuta desde Visual Studio, y el procedimiento tiene algo por dentro cuyos resultados varían según las credenciales del llamante.

    La segunda es que haya algún error en el programa, y no esté realmente enviando al servidor lo que creemos que le está enviando. Para comprobar si es esto, se puede utilizar el Profiler de SQL para capturar la llamada que le está llegando. Puede que al ver la captura el error sea evidente; si no lo es, se puede copiar y pegar desde ahí al SSMS y ejecutarlo, a ver en qué difiere.

    lunes, 7 de enero de 2019 18:44
    Moderador
  • Ya esta solucionado, el Problema es que no estaba correctamente Seteado (CONCAT_NULL_YIELD_NULL) en el SP al momento de la ejecucion.

    Slds.

    • Marcado como respuesta NicolasHC martes, 12 de febrero de 2019 22:00
    martes, 12 de febrero de 2019 22:00

Todas las respuestas

  • ALTER PROCEDURE [dbo].[XXX_SP_REPORTING_SERVICES_DESPACHOS] @xmlParametros XML, @intruccionSelect Varchar(MAX) OUTPUT AS
    
    BEGIN
    
    SET NOCOUNT ON
    SET DATEFORMAT YMD
    
    DECLARE
    @DespachoR	VARCHAR(100),
    @DespachoRBEsta TINYINT,
    @PaisOrigenR VARCHAR(100),
    @PaisOrigenBEsta TINYINT,
    @Estampilla INTEGER,
    @DesdeFecha VARCHAR(10),
    @HastaFecha VARCHAR(10),
    @desfecha datetime,
    @hasfecha datetime,
    @strWhereRan VARCHAR(MAX),
    @strWhere VARCHAR(MAX),
    
    @auxDesFecha		VARCHAR(10),
    @auxHasFecha		VARCHAR(10),
    
    
    @seteos nvarchar(200),
    @instruccion nvarchar(max),
    @instruccion2 nvarchar(max),
    @texto varchar(max),
    @columna varchar(100),
    @tipodato varchar(50)
    
    
    If @xmlParametros is not null
     begin
    	--DECLARA TABLA PARA IR PONIENDO LOS VALORES DEL XML (IDENTIDAD)Y LAS PROPIEDADES DE LOS NODOS
    	DECLARE @xml TABLE ( identidad integer IDENTITY (1,1) not null, tipo char(1) not null, columna varchar(30) not null, campoxml varchar(1000) not null, 
    		tipodato varchar(50) not null, nulo tinyint null)
    
    	INSERT @xml (columna, tipo, campoxml, tipodato, nulo)
    
    
    	
    	SELECT '@DespachoR', 'E', '(/Parameters/DESPACHOR/text())[1]', 'varchar(100)', null 
    	UNION ALL
    	SELECT '@DespachoRBEsta', 'E', '(/Parameters/DESPACHORBESTA/text())[1]', 'tinyint', null 
    	UNION ALL
    	SELECT '@PaisOrigenR', 'E', '(/Parameters/PAISORIGENR/text())[1]', 'varchar(100)', null 
    	UNION ALL
    	SELECT '@PaisOrigenBEsta', 'E', '(/Parameters/PAISORIGENRBESTA/text())[1]', 'tinyint', null 
    	UNION ALL
    	SELECT '@Estampilla', 'E', '(/Parameters/ESTAMPILLA/text())[1]', 'integer', null 
    	UNION ALL
    	SELECT '@DesdeFecha', 'E', '(/Parameters/DESDEFECHA/text())[1]', 'varchar(10)', null 
    	UNION ALL
    	SELECT '@HastaFecha', 'E', '(/Parameters/HASTAFECHA/text())[1]', 'varchar(10)', null 
    	
    
    
    	SELECT @seteos = 'SET ANSI_NULLS ON
    			SET QUOTED_IDENTIFIER ON
    			SET CONCAT_NULL_YIELDS_NULL ON
    			SET ANSI_PADDING ON
    			SET ANSI_WARNINGS ON
    			SET ARITHABORT ON
    			SET NUMERIC_ROUNDABORT OFF '
    	select @instruccion =null, @instruccion2 = N'@xmlParametros xml'
    
    	DECLARE INSTRUCCION	INSENSITIVE CURSOR FOR
    	SELECT columna, "@xmlParametros.value('" + campoxml + "', '" + tipodato + "')", tipodato
    	FROM @xml
    	WHERE tipo='E'
    	ORDER BY identidad  
    	
    	OPEN INSTRUCCION
    	FETCH NEXT FROM INSTRUCCION INTO @columna, @texto, @tipodato
    	
        WHILE (@@FETCH_STATUS <> -1)
    	 begin
    		If @instruccion is not null
    			SELECT @instruccion  = @instruccion + ', '
    		
    		SELECT @instruccion  = @instruccion + @columna + '=' + @texto, @instruccion2 = @instruccion2 + ', ' + @columna + ' ' + @tipodato + ' OUTPUT'
    
    		FETCH NEXT FROM INSTRUCCION INTO @columna, @texto, @tipodato
    	 end
     
    	CLOSE INSTRUCCION
    	DEALLOCATE INSTRUCCION
    	 
    	SELECT @instruccion = @seteos + N'SELECT ' + @instruccion 
    
    
    	exec sp_executesql	@instruccion, @instruccion2,@xmlParametros=@xmlParametros, @DespachoR=@DespachoR OUTPUT,
    						@DespachoRBEsta=@DespachoRBEsta OUTPUT, @PaisOrigenR=@PaisOrigenR OUTPUT, @PaisOrigenBEsta=@PaisOrigenBEsta OUTPUT, @Estampilla=@Estampilla OUTPUT,
    						@DesdeFecha=@DesdeFecha OUTPUT, @HastaFecha=@HastaFecha OUTPUT
    
        IF @DespachoR is not null
    	 begin
    		EXEC sp_armo_rango @DespachoR, 'D.Despacho', @DespachoRBEsta, 0, @strWhereRan OUTPUT
    
    		IF @strWhereRan is not null
    			Set @strWhere = @strWhere + " AND " + rtrim(@strWhereRan)
    
    
    	 end
    
    	IF @PaisOrigenR is not null
    	begin
    		 EXEC sp_armo_rango @PaisOrigenR, 'D.Paisorigen', @PaisOrigenBEsta, 0, @strWhereRan OUTPUT
    		 IF @strWhereRan is not null
    			  Set  @strWhere  =  @strWhere  + " AND " + rtrim(@strWhereRan)
    	end
    
    	IF @Estampilla is not null
    		 --Esto determina que se listen los despachos que tienen la estampilla indicada, los despachos así determinados se listan completos, 
    		 --es decir, con todos los rangos de estampillas.
    		 Set @strWhere = @strWhere + ' AND 0<>(SELECT count(*) FROM Despachosestampillas De1 (nolock)
    							  WHERE     De1.Despacho=D.Despacho AND 
    								   De1.Hasta >=@estampilla AND De1.Desde <= @estampilla )'
    
    	IF @DesdeFecha is not null
    	begin
    		select @desfecha=convert(datetime, @desdefecha, 103)
    
    		 Set @auxDesFecha = left(convert(varchar, @desfecha, 120), 10)
    		 Set  @strWhere  =  @strWhere  + ' AND  D.Fecha >=' + "'" + @auxDesFecha + "'"	 
    	 end
    
    	IF @HastaFecha is not NULL
    		begin
    		select @hasfecha=convert(datetime, @HastaFecha, 103)
    		 Set @auxHasFecha = left(convert(varchar, @hasfecha, 120), 10)
    		 Set @strWhere = @strWhere + ' AND  D.Fecha <=' + "'" + @auxHasFecha + "'" 	 
    	 end
    
    
    	Set @instruccion  = N'SELECT D.Despacho, D.Fecha as FechaD, D.Aduana, D.Paisorigen as Pais, D.Garantia, 
    					CASE WHEN D.TipoGarantia="E" THEN "Efectivo" 
    						 WHEN D.TipoGarantia="C" THEN "Caución" 
    						 WHEN D.TipoGarantia="M" THEN "Mixto" 
    						 ELSE null END as Tipo, 
    					D.Efectivo, D.FechaGarantia as Fecha, D.Compania, D.Poliza, D.FechavtoGarantia as Fechavto, D.Monto as Importe, 
    					De.Desde, De.Hasta, convert(varchar(254), D.Observacion) as Observacion 
    			  FROM Despachos D (nolock)
    					LEFT JOIN Despachosestampillas De (nolock) ON D.Despacho=De.Despacho
    			  WHERE 1=1 ' +  @strWhere + '
    			  ORDER BY D.Despacho, De.Desde'
    
    	
    			  exec sp_executesql	@instruccion OUTPUT, N'@estampilla integer, @desfecha datetime, @hasfecha datetime', @estampilla=@estampilla, @desfecha=@desfecha, @hasfecha=@hasfecha
    									
    
    			
    			  
     end
    
    END

    ALTER PROCEDURE [dbo].[XXX_SP_REPORTING_SERVICES_CENTRO_APROPIACION]	@xmlParametros XML AS
    
    BEGIN
    	
    	SET NOCOUNT ON;
    	SET CONCAT_NULL_YIELDS_NULL OFF
    	SET ANSI_NULLS OFF
    	SET QUOTED_IDENTIFIER OFF
    	SET NOCOUNT ON
    
    	DECLARE	
    	@Sistema CHAR(1),	
    	@seteos NVARCHAR(200),
    	@instruccion NVARCHAR(max),
    	@instruccion2 NVARCHAR(max),
    	@texto VARCHAR(max),
    	@columna VARCHAR(100),
    	@tipodato VARCHAR(50)
    	
    	IF @xmlParametros is not null
    	 BEGIN
    
    		DECLARE @xml TABLE ( identidad integer IDENTITY (1,1) not null, tipo char(1) not null, columna varchar(30) not null, campoxml varchar(1000) not null, 
    			tipodato varchar(50) not null, nulo tinyint null)
    
    		INSERT @xml (columna, tipo, campoxml, tipodato, nulo)
    								
    		SELECT '@Sistema', 'E', '(/Parameters/SISTEMA/text())[1]', 'char(1)', null 
    
    		SELECT @seteos = 'SET ANSI_NULLS ON
    				SET QUOTED_IDENTIFIER ON
    				SET CONCAT_NULL_YIELDS_NULL ON
    				SET ANSI_PADDING ON
    				SET ANSI_WARNINGS ON
    				SET ARITHABORT ON
    				SET NUMERIC_ROUNDABORT OFF '
    		select @instruccion =null, @instruccion2 = N'@xmlParametros xml'
    	
    
    		DECLARE INSTRUCCION	INSENSITIVE CURSOR FOR
    		SELECT columna, "@xmlParametros.value('" + campoxml + "', '" + tipodato + "')", tipodato
    		FROM @xml
    		WHERE tipo='E'
    		ORDER BY identidad  
    	
    		OPEN INSTRUCCION
    		FETCH NEXT FROM INSTRUCCION INTO @columna, @texto, @tipodato
    	
    		WHILE (@@FETCH_STATUS <> -1)
    		 BEGIN
    			If @instruccion is not null
    				SELECT @instruccion  = @instruccion + ', '
    		
    			SELECT @instruccion  = @instruccion + @columna + '=' + @texto, @instruccion2 = @instruccion2 + ', ' + @columna + ' ' + @tipodato + ' OUTPUT'
    
    			FETCH NEXT FROM INSTRUCCION INTO @columna, @texto, @tipodato
    		 END
     
    		CLOSE INSTRUCCION
    		DEALLOCATE INSTRUCCION
    	 
    		SELECT @instruccion = @seteos + N'SELECT ' + @instruccion 
    
    
    		exec sp_executesql	@instruccion, @instruccion2,@xmlParametros=@xmlParametros,
    							@Sistema=@Sistema OUTPUT
    	 END
    
    	
    	IF @Sistema != 'C'
    		SELECT CENPREFI, CODCEN, NOMBRE, ADMNEGOCIOS
    		FROM dbo.CENTROSAP	(NOLOCK)
    		ORDER BY CENPREFI
    	ELSE
    		SELECT CENPREFI, CODCEN, NOMBRE
    		FROM dbo.CENTROSAP	(NOLOCK)
    		ORDER BY CENPREFI
    END
    

    viernes, 4 de enero de 2019 14:56
  • Puedes "trucarlo" para que funcione el diseñador escribiendo provisionalmente un procedimiento que tenga una sentencia "fija" con las columnas correctas. Por ejemplo:

    Select 1 as ID, 'Pepe' as Nombre, etc

    Con ese procedimiento ejecutas el diseñador del informe, y una vez que esté diseñado borras ese procedimiento y en su lugar pones el "auténtico" que dentro tiene el sp_executesql. Suponiendo que efectivamente las columnas que devuelve sean las mismas, al reporte le da lo mismo y funcionará contra el procedimiento "real".

    viernes, 4 de enero de 2019 15:32
    Moderador
  • Buenas Alberto!

    Gracias por Responder. Te comento que probé lo que me indicaste pero aun así la misma no me devuelve nada cuando vuelvo a poner el "autentico" que tiene el sp_executesql.

    Slds.

    viernes, 4 de enero de 2019 15:57
  • Acabo de generar la conexión, el comando sql y el adaptador de datos para obtener los resultset. Podrían indicarme si esta bien:

    SqlConnection conexionSQL = new SqlConnection("Data Source=SRVDESARROLLO7\\BASCS;Initial Catalog=BASCSREPORT;Integrated Security=True");
    conexionSQL.Open();
    string stringCommand = "dbo.XXX_SP_REPORTING_SERVICES_DESPACHOS";
    SqlCommand comandoSQL = new SqlCommand(stringCommand, conexionSQL);
    comandoSQL.CommandType = CommandType.StoredProcedure;
    comandoSQL.Parameters.AddWithValue("@xmlParametros", SqlDbType.Xml).Value = parametrosXML.ToString();
    SqlDataAdapter da = new SqlDataAdapter(comandoSQL.ToString(), conexionSQL);
    da.SelectCommand = comandoSQL;
    BASCSREPORTDataSet dataSet1 = new BASCSREPORTDataSet();
    da.Fill(dataSet1, dataSet1.Tables[0].TableName);
    ReportDataSource rds = new ReportDataSource("XXX_SP_REPORTING_SERVICES_DESPACHOS", dataSet1.Tables[0]);
    this.reportViewer1.LocalReport.DataSources.Clear();
    this.reportViewer1.LocalReport.DataSources.Add(rds);
    this.reportViewer1.LocalReport.Refresh();
    this.reportViewer1.RefreshReport();

    lunes, 7 de enero de 2019 13:05
  • Sí, en principio tiene pinta de que debería funcionar. Pero tienes varias cosas que son redundantes y las podrías simplificar.

    Primero, esta sentencia:

    SqlDataAdapter da = new SqlDataAdapter(comandoSQL.ToString(), conexionSQL);

    se puede abreviar asi:

    SqlDataAdapter da = new SqlDataAdapter(comandoSQL);

    Segundo, esta otra:

    da.SelectCommand = comandoSQL;

    se puede suprimir por completo. Al construir el DataAdapter a partir del comando, el comando ya se inserta en el SelectCommand, por lo que es superfluo volvérselo a meter de nuevo al SelectCommand.

    Tercero, estás usando un DataSet para rellenar únicamente uno de sus DataTables, y luego usas únicamente ese DataTable y no usas nada más del DataSet. Para eso no merece la pena usar un DataSet, podrías usar directamente el DataTable. Esa es una costumbre heredada del Framework 1.0, en el que el método Fill solo se podía aplicar a un DataSet y no a un DataTable. Pero ha llovido muchísimo desde el 1.0, ya va siendo hora de que usemos el Fill sobre un DataTable, que se puede hacer desde la version 2.0 que ya salió hace unos 16 años más o menos.

    lunes, 7 de enero de 2019 14:33
    Moderador
  • Gracias por contestar Alberto!

    Ya tome nota de lo que me comentaste, pero aun asi no me esta trayecto los resultados esperados el sp. Si executo desde SSMS, me trae el ResultSet esperado:

     
    EXEC [dbo].[XXX_SP_REPORTING_SERVICES_DESPACHOS] '<Parameters>
      <ORACLE>0</ORACLE>
      <BASE>bascs460</BASE>
      <USER>BAS-adrye</USER>
      <CLAVE></CLAVE>
      <DEV>4</DEV>
      <SISTEMA>G</SISTEMA>
      <CODPROG>483</CODPROG>
      <CARATULA>0</CARATULA>
      <FORMATOQRP></FORMATOQRP>
      <CONEXIONESNAME>BASCS</CONEXIONESNAME>
      <PROGRAMNAME>BASCS</PROGRAMNAME>
      <PROGRAMVERSION>4-6-0 VP1</PROGRAMVERSION>
      <DESPACHOR>COM0002-EEUU0003,com0001</DESPACHOR>
      <DESPACHORBESTA>0</DESPACHORBESTA>
      <PAISORIGENR></PAISORIGENR>
      <PAISORIGENRBESTA>1</PAISORIGENRBESTA>
      <ESTAMPILLA></ESTAMPILLA>
      <DESDEFECHA></DESDEFECHA>
      <HASTAFECHA></HASTAFECHA>
      <MonoEmp>1</MonoEmp>
      <CODEMPUSU>1</CODEMPUSU>
    </Parameters>'

    Ahora cuando lo ejecuto desde la consola de Visual Studio, la misma no me trae los resultados:


    lunes, 7 de enero de 2019 16:29
  • Si executo desde SSMS, me trae el ResultSet esperado[...] cuando lo ejecuto desde la consola de Visual Studio, la misma no me trae los resultados:

    Cuando suceden estas cosas, típicamente es por una de estas dos razones:

    La primera es que sea debido a una diferencia de credenciales, de manera que el usuario no es el mismo cuando se ejecuta desde la consola y cuando se ejecuta desde Visual Studio, y el procedimiento tiene algo por dentro cuyos resultados varían según las credenciales del llamante.

    La segunda es que haya algún error en el programa, y no esté realmente enviando al servidor lo que creemos que le está enviando. Para comprobar si es esto, se puede utilizar el Profiler de SQL para capturar la llamada que le está llegando. Puede que al ver la captura el error sea evidente; si no lo es, se puede copiar y pegar desde ahí al SSMS y ejecutarlo, a ver en qué difiere.

    lunes, 7 de enero de 2019 18:44
    Moderador
  • Ya esta solucionado, el Problema es que no estaba correctamente Seteado (CONCAT_NULL_YIELD_NULL) en el SP al momento de la ejecucion.

    Slds.

    • Marcado como respuesta NicolasHC martes, 12 de febrero de 2019 22:00
    martes, 12 de febrero de 2019 22:00
  • Alberto: Ya esta solucionado, gracias de todas formas por contestar.

    Slds.

    martes, 12 de febrero de 2019 22:02