Store procedure returned parameter

#1

Hi,
i have a huge 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
SET NOCOUNT 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
SET NOCOUNT OFF
GO

#2

Add the SET NOCOUNT ON inside 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" <anonymous (AT) discussions (DOT) microsoft.com> wrote in
message news:826B70C8-64B8-4C70-9C7E-A99868084AEC (AT) microsoft (DOT) com...

Quote:

Hi,
i have a huge 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
SET NOCOUNT 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
SET NOCOUNT OFF
GO

#3

create PROCEDURE SPV_SP_INV_FI1_T2 @codentidade smallint,
@codrecolha integer, @dtreporte datetime,
@resultado integer OUTPUT
AS
SET NOCOUNT ON
set @resultado = 0
declare @codbase char

etc

SET NOCOUNT ON needs to be *inside* the procedure body

"Rita & Oscar Alhinho" <anonymous (AT) discussions (DOT) microsoft.com> wrote in
message news:826B70C8-64B8-4C70-9C7E-A99868084AEC (AT) microsoft (DOT) com...

Quote:

Hi,
i have a huge 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
SET NOCOUNT 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
SET NOCOUNT OFF
GO

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Store procedure returned parameter

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Store procedure returned parameter - 10-20-2003 , 05:26 AM

Re: Store procedure returned parameter - 10-20-2003 , 05:37 AM

Re: Store procedure returned parameter - 10-20-2003 , 05:44 AM