September 14, 2005 at 2:48 am
Hi,
I´m executing a stored procedure with output variables inside a DTS "Execute SQL Task". I store the output values in Package global variables. The code that I´m running to set the output values for the procedure is:
CREATE PROCEDURE [dbo].[P_VALIDAR_FICH_COMPENSADOSCC] @valor_out int OUTPUT
AS
-- Declaring cursor
DECLARE C_ESTADO_CENTRALES CURSOR FOR
select a2.central,.....
-- Opening cursor...
OPEN C_ESTADO_CENTRALES
FETCH NEXT FROM C_ESTADO_CENTRALES
INTO @v_central, @v_ok
IF @v_ok='KO'
BEGIN
SET @valor_out=10
INSERT INTO TE_ERROR_GENERAL (LCV_COD_TABLA, LCV_COD_PAQUETE, LCV_DES_ERROR)
VALUES ('DATAMART_SP.COMPENSADOS','PCK_CARGA_COMPENSADOS',@v_central+' - '+@v_ok)
--COMMIT
RETURN --0
END
.
.
.
The output value from "@valor_out" is related to a DTS global variable called "vg_valor_out". The problem is the next:
The "vg_valor_out" value only is changed to the "valor_out" value when the piece of code corresponding to the INSERT statement is commented. ¿Why can´t I execute the INSERT statement together with the SET statement?
I´m not very skillfull with transact and I think it´s basic Knowledge but, Can anybody help me?
Regards.
September 19, 2005 at 8:00 am
This was removed by the editor as SPAM
October 4, 2005 at 2:34 pm
I have a similar problem then you have.
I couldn't figure it out yet, but in certain situation the "SET NOCOUNT ON" solved the problem when i have added it to the DTS task; in other cases it didn't make any difference.
Execute SQL Task example:
SET NOCOUNT ON
DECLARE @Param_out int
EXEC AnyStoredProc @Param_out OUTPUT
SELECT @Param_out ParamToDTSGlobal
ParamToDTSGlobal is mapped to a DTS global variable.
I'm not sure that this will help at all... If the mean time you found the solution please let me know!
October 5, 2005 at 9:23 am
Hi ,
I just feel that insert statement what you have given is wrong.The whole of insert statement is considered as a mixture of a variable and a constant so you can have the insert statment as
exec 'INSERT INTO TE_ERROR_GENERAL (LCV_COD_TABLA, LCV_COD_PAQUETE, LCV_DES_ERROR)
VALUES ('DATAMART_SP.COMPENSADOS','PCK_CARGA_COMPENSADOS','+@v_central+' - '+@v_ok + ')'
I havent checked the syntax error in my select statment ,But if you can execute the query in this fashion definitely it should work
cheers!!!!
Suresh
October 5, 2005 at 1:45 pm
Hi,
The problem with the solution that you recommended is that any variable or input parameter is invisible for the INSERT statement inside the exec statement.
This kind of kills the purpose of a stored procedure that has input and output parameters, and it still doesn't solves the original problem, which is:
An Execute SQL Step in a DTS package executes a stored procedure and maps the outcome of the SP to a global variable of the DTS package but the mapping never passes the value to the global variable of the DTS package unless the SP simplified to select statements only.
Although this is not true for all cases since I have Execute SQL Steps in a DTS that works the way it should and others in the same DTS package that do what I described above.
If you have any idea what could be the problem please let us know!
Take care,
Attila
October 5, 2005 at 3:54 pm
ok, I got it.
Thank you very much!!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply