Hi,
i have a problem, i have a DTS that calls a store
procedure (SP). This SP should return a value but it
doesn´t. If i run it in the SQL query analyser it works
fine and returns the proper value.
If i modify the SP, redrowing only the insert line but
keeping the select it returns the proper value even when
called from the DTS.
What kind of effect has the insert line when executing the
SP from the DTS?

The code i have in the DTS (in an EXECUTE SQL Task) is:
DECLARE @Result INTEGER
EXEC SPV_SP_INV_FI1_T2 1,1,1,1,1,1, @result Output
SELECT @result AS gvResult

And the code in the store procedure is:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create PROCEDURE SPV_SP_INV_FI1_T2 @codentidade smallint,
@codrecolha integer, @dtreporte datetime,
@resultado integer OUTPUT
AS
set @resultado = 0

INSERT ODS.dbo.SPV_ODS_CTRL_ERROS_VALIDA_2(codentidade,
codrecolha,descerro,[timestamp])
SELECT @CODENTIDADE, @CODRECOLHA, T1.CodBaseActivo,
getdate()
FROM ODS.dbo.TABLE1 TDesc
Right OUTER JOIN ES.dbo.TABLE2 T1
ON T1.CodBaseActivo=TDesc.CodBaseActivo_PK
WHERE TDesc.CodBaseActivo_Pk is null
IF (@@ROWCOUNT != 0)
begin
set @resultado = 1
end

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

try setting

SET NOCOUNT ON in the proc

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Rita&Oscar Alhinho" <ricardo.f.silva (AT) netcabo (DOT) pt> wrote

Hi,
i have a problem, i have a DTS that calls a store
procedure (SP). This SP should return a value but it
doesn´t. If i run it in the SQL query analyser it works
fine and returns the proper value.
If i modify the SP, redrowing only the insert line but
keeping the select it returns the proper value even when
called from the DTS.
What kind of effect has the insert line when executing the
SP from the DTS?

The code i have in the DTS (in an EXECUTE SQL Task) is:
DECLARE @Result INTEGER
EXEC SPV_SP_INV_FI1_T2 1,1,1,1,1,1, @result Output
SELECT @result AS gvResult

And the code in the store procedure is:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create PROCEDURE SPV_SP_INV_FI1_T2 @codentidade smallint,
@codrecolha integer, @dtreporte datetime,
@resultado integer OUTPUT
AS
set @resultado = 0

INSERT ODS.dbo.SPV_ODS_CTRL_ERROS_VALIDA_2(codentidade,
codrecolha,descerro,[timestamp])
SELECT @CODENTIDADE, @CODRECOLHA, T1.CodBaseActivo,
getdate()
FROM ODS.dbo.TABLE1 TDesc
Right OUTER JOIN ES.dbo.TABLE2 T1
ON T1.CodBaseActivo=TDesc.CodBaseActivo_PK
WHERE TDesc.CodBaseActivo_Pk is null
IF (@@ROWCOUNT != 0)
begin
set @resultado = 1
end

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO