Hi.

I'm having a BIG problem calling a stored procedure with an insert-select.

If I call the same stored procedure from the SQL Query Analyser it works fine and returns a variable (select rowcount). However, if I run the DTS directly the variable is not correctly updated with the select rowcount.

Curiously, if I do not insert, and use the select part only, the variable contains the rowcount (running the DTS directly).

What am I doing wrong?

Thanks in advance...
goncalo

In article <122880FC-7116-4203-8054-768E0850D7E4 (AT) microsoft (DOT) com>, Goncalo
<anonymous (AT) discussions (DOT) microsoft.com> writes

Try adding SET NOCOUNT ON to the top of your code.

When you run a statement in Query Analyzer you see a message "x rows
affected" or similar. This information message actually appears as a
result set to DTS (and also ADO), which means that your SELECT is now
the second result set. Simple way to get around this is to add the SET
NOCOUNT ON command to the top of the stored procedure code.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

unfortunatly, that dint't work.
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,
@codlocactividade char(3),
@codmapa char(5),
@codrecolha integer,
@codtipoactividade char(2),
@dtreporte datetime,
@resultado integer OUTPUT
AS
set @resultado = 0
declare @codbase char

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




----- Goncalo wrote: -----

Hi.

I'm having a BIG problem calling a stored procedure with an insert-select.

If I call the same stored procedure from the SQL Query Analyser it works fine and returns a variable (select rowcount). However, if I run the DTS directly the variable is not correctly updated with the select rowcount.

Curiously, if I do not insert, and use the select part only, the variable contains the rowcount (running the DTS directly).

What am I doing wrong?

Thanks in advance...
goncalo

Try adding SET NOCOUNT ON to your stored procedure, e.g.

create PROCEDURE SPV_SP_INV_FI1_T2 @codentidade smallint,
@codlocactividade char(3),
@codmapa char(5),
@codrecolha integer,
@codtipoactividade char(2),
@dtreporte datetime,
@resultado integer OUTPUT
AS
SET NOCOUNT ON
set @resultado = 0
....


--
Darren Green
http://www.sqldts.com



"goncalo" <anonymous (AT) discussions (DOT) microsoft.com> wrote

DTS directly the variable is not correctly updated with the select rowcount.