Cursor error when executing Job

[Rob1412]

I have a job which executes a Stored procedure which in turn executes a series a stored procedures. Now I've executed this main stored procedure through the query analyzer and it executes correctly , but when i try to execute it through a job i get error The cursor is not open. [SQLSTATE 42000] (Error 16917) .


Anybody have a clue what could be wrong?

 

[nigelrivett]

Not without knowing what it does.
Try logging each SP call to a table to find out where it is failing then running that SP alone from the scheduler.

The scheduler is probably running it under a different user and maybe with different environment settings.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Rob1412]

I'm afraid it doesn't have anything to do with users because the job starts of fine and it seems to work alright when i'm processing one day it completes the job.

To be a little more exact here is a bit of code where i seem to have the problem but i've noticed that the problem happens in the sp that i'm calling that also uses a cursor
but it generally doesn't happen when theres one day to process.


--MAIN SP

DECLARE C_PROCESAR CURSOR FOR
--DIAS POR PROCESAR
SELECT DAY
FROM dbo.DaysProcessed
WHERE (Procesado = 0)
ORDER BY Day
-- PROCESAR DIA
OPEN C_PROCESAR


FETCH NEXT FROM C_PROCESAR INTO @DAY

WHILE @@FETCH_STATUS = 0
BEGIN
-- This is where the problem seems to be
-- BOTH OF THE STORED PROCEDURES ARE SIMILAR
-- but at some point the cursor just closes in
-- in any of these two procedures in the middle
-- of the execution.
-- when i take any one of these sp out of the code the
-- job finishes correctly
-- but when i put these together thats when the error
-- occurs.

EXEC dbo.Salientes_Tmp_Insertar_sp @DAY
EXEC dbo.Entrantes_Tmp_Insertar_sp @DAY


FETCH NEXT FROM C_PROCESAR INTO @DAY
END


CLOSE C_PROCESAR
DEALLOCATE C_PROCESAR


--------------------------------------





ALTER PROCEDURE Salientes_Tmp_Insertar_sp (@p_fecha varchar(8) )
AS
BEGIN

TRUNCATE TABLE Salientes_Tmp

DECLARE POINT CURSOR
FAST_FORWARD
FOR SELECT infobilling_raw_prd.dbo.RAWPRD.PKRegistro,
infobilling_raw_prd.dbo.RAWPRD.SubscriberNumber AS A,
infobilling_raw_prd.dbo.RAWPRD.PartyNumber AS B,
infobilling_raw_prd.dbo.RAWPRD.FecRegister AS CallDateTime,
infobilling_raw_prd.dbo.RAWPRD.Duration,
infobilling_raw_prd.dbo.RAWPRD.InTrunc AS Trunk,
infobilling_raw_prd.dbo.RAWPRD.RegisterType

FROM infobilling_raw_prd.dbo.RAWPRD INNER JOIN
Trunks ON infobilling_raw_prd.dbo.RAWPRD.InTrunc
COLLATE Modern_Spanish_CI_AS = Trunks.Trunk

WHERE (SUBSTRING(infobilling_raw_prd.dbo.RAWPRD.FecRegister, 1, 8) = @p_fecha)AND
(infobilling_raw_prd.dbo.RAWPRD.RegisterType = '01') AND
(Trunks.FkTipoTrafico = 2)


DECLARE @PkRegistro int
DECLARE @A VARCHAR (15)
DECLARE @B VARCHAR (15)
DECLARE @CALLDATETIME VARCHAR(14)
DECLARE @DURATION AS VARCHAR(6)
DECLARE @Trunk as VARCHAR(8)
DECLARE @RegisterType as VARCHAR(2)
DECLARE @DestinoGroupA INT
DECLARE @FkOperadoraA INT
DECLARE @DestinoGroupB INT
DECLARE @FkOperadoraB INT
DECLARE @ModalidadTelefonia varchar(2)
DECLARE @ModalidadPago int
DECLARE @ModalidadRoamer int
DECLARE @TipoExento int
DECLARE @FkTramo int
DECLARE @Fecha as datetime
DECLARE @TASA as float
DECLARE @prefix VARCHAR (15)
DECLARE @numero VARCHAR (15)

OPEN POINT


set @TASA = dbo.Get_Tasa_Cambio(CAST (substring(@p_fecha, 7, 2)
+ '/'+ SUBSTRING(@p_fecha, 5, 2) + '/' +
LEFT(@p_fecha, 4) AS Datetime))

set @Fecha= CAST(SUBSTRING(@p_fecha, 7, 2) + '/' + SUBSTRING(@p_fecha, 5, 2)
+ '/' + LEFT(@p_fecha, 4) AS Datetime)


FETCH NEXT FROM POINT INTO @PkRegistro,@A,@B,@CALLDATETIME,@DURATION,@Trunk,@RegisterType

WHILE @@FETCH_STATUS = 0
BEGIN
set @numero = substring( @A,4,15)
set @prefix = substring(@A,1,3)
set @ModalidadRoamer=dbo.Get_Modalidad_Roamer(@A)
set @TipoExento=dbo.Get_Exentos_Infonet(@A)
set @ModalidadTelefonia=dbo.Get_Modalidad_Telefonia(@A)
set @ModalidadPago=dbo.Get_Modalidad_Pago(@A)
SET @FkTramo=dbo.Get_Tramo(@B)

SET @FkOperadoraA= NULL
SET @DestinoGroupA = NULL

SELECT @DestinoGroupA = FkDestinoGroup,@FkOperadoraA=FkOperadora
FROM infobilling_cdr_prd.dbo.getdatosdestino(@prefix, @numero ,'PNN' )


set @numero = substring( @B,4,15)
set @prefix = substring(@B,1,3)

SET @FkOperadoraB= NULL
SET @DestinoGroupB = NULL

SELECT @DestinoGroupB = FkDestinoGroup,@FkOperadoraB=FkOperadora
FROM infobilling_cdr_prd.dbo.getdatosdestino(@prefix, @numero ,'PNN' )


INSERT INTO Salientes_Tmp
(PKRegistro , A, B , CallDateTime, Duration, Trunk, RegisterType,
Fecha, FkOperA,FkDestinoGroupA,FkOperB,FkDestinoGroupB,Tasa_Cambio,
FkModalidadTelefonia,FkModalidadPago,FkTipoExento,FkModalidadRoamer,FkTramo)
VALUES
(@PKRegistro,@A, @B, @CallDateTime, @Duration, @Trunk, @RegisterType,
@Fecha, @FkOperadoraA,@DestinoGroupA, @FkOperadoraB,@DestinoGroupB, @Tasa,
@ModalidadTelefonia,@ModalidadPago,@TipoExento,@ModalidadRoamer,@FkTramo)



FETCH NEXT FROM POINT INTO @PkRegistro,@A,@B,@CALLDATETIME,@DURATION,@Trunk,@RegisterType

END

CLOSE POINT
DEALLOCATE POINT

END

--------------------
ALTER PROCEDURE dbo.Entrantes_Tmp_Insertar_sp (@p_fecha varchar(8) )
AS
BEGIN

TRUNCATE TABLE Entrantes_Tmp


--LLAMADAS 02
DECLARE POINTER CURSOR
FAST_FORWARD
FOR
SELECT infobilling_raw_prd.dbo.RAWPRD.PKRegistro,
infobilling_raw_prd.dbo.RAWPRD.PartyNumber AS A,
infobilling_raw_prd.dbo.RAWPRD.SubscriberNumber AS B,
infobilling_raw_prd.dbo.RAWPRD.FecRegister AS CallDateTime,
infobilling_raw_prd.dbo.RAWPRD.Duration,
infobilling_raw_prd.dbo.RAWPRD.OutTrunc AS Trunk,
infobilling_raw_prd.dbo.RAWPRD.RegisterType

FROM infobilling_raw_prd.dbo.RAWPRD INNER JOIN
Trunks ON infobilling_raw_prd.dbo.RAWPRD.OutTrunc
COLLATE Modern_Spanish_CI_AS = Trunks.Trunk
WHERE (SUBSTRING(infobilling_raw_prd.dbo.RAWPRD.FecRegister, 1, 8) = @p_fecha) AND
(infobilling_raw_prd.dbo.RAWPRD.RegisterType = '02') AND
(Trunks.FkTipoTrafico = 2)


DECLARE @PkRegistro int
DECLARE @A VARCHAR (15)
DECLARE @B VARCHAR (15)
DECLARE @CALLDATETIME VARCHAR(14)
DECLARE @DURATION AS VARCHAR(6)
DECLARE @Trunk as VARCHAR(8)
DECLARE @RegisterType as VARCHAR(2)
DECLARE @DestinoGroupA INT
DECLARE @FkOperadoraA INT
DECLARE @DestinoGroupB INT
DECLARE @FkOperadoraB INT
DECLARE @Fecha as datetime
DECLARE @TASA as float
DECLARE @ModalidadTelefonia varchar(2)
DECLARE @ModalidadPago int
DECLARE @ModalidadRoamer int
DECLARE @TipoExento int
DECLARE @prefix VARCHAR (15)
DECLARE @numero VARCHAR (15)

OPEN POINTER


set @TASA = dbo.Get_Tasa_Cambio(CAST (substring(@p_fecha, 7, 2)
+ '/'+ SUBSTRING(@p_fecha, 5, 2) + '/' +
LEFT(@p_fecha, 4) AS Datetime))

set @Fecha= CAST(SUBSTRING(@p_fecha, 7, 2) + '/' + SUBSTRING(@p_fecha, 5, 2) + '/' + LEFT(@p_fecha, 4) AS Datetime)




FETCH NEXT FROM POINTER INTO @PkRegistro,@A,@B,@CALLDATETIME,@DURATION,@Trunk,@RegisterType


WHILE @@FETCH_STATUS = 0
BEGIN
set @numero = substring( @A,4,15)
set @prefix = substring(@A,1,3)
set @ModalidadTelefonia= dbo.Get_Modalidad_Telefonia(@B)
set @ModalidadPago= dbo.Get_Modalidad_Pago(@B)
set @TipoExento= dbo.Get_Tipo_Exento(@A)
set @ModalidadRoamer= dbo.Get_Modalidad_Roamer(@B)

SET @FkOperadoraA= NULL
SET @DestinoGroupA = NULL

SELECT @DestinoGroupA = FkDestinoGroup,@FkOperadoraA=FkOperadora
FROM infobilling_cdr_prd.dbo.getdatosdestino(@prefix, @numero ,'PNN' )



set @numero = substring( @B,4,15)
set @prefix = substring(@B,1,3)
SET @FkOperadoraB= NULL
SET @DestinoGroupB = NULL


SELECT @DestinoGroupB = FkDestinoGroup,@FkOperadoraB=FkOperadora
FROM infobilling_cdr_prd.dbo.getdatosdestino(@prefix, @numero ,'PNN' )


INSERT INTO Entrantes_Tmp
(PKRegistro , A, B , CallDateTime, Duration, Trunk, RegisterType, Fecha,
FkOperA,FkDestinoGroupA,FkOperB,FkDestinoGroupB,Tasa_Cambio,FkModalidadTelefonia,FkModalidadPago,
FkTipoExento,FkModalidadRoamer)
VALUES
(@PKRegistro,@A, @B, @CallDateTime, @Duration, @Trunk, @RegisterType, @Fecha,
@FkOperadoraA,@DestinoGroupA, @FkOperadoraB,@DestinoGroupB, @Tasa, @ModalidadTelefonia,@ModalidadPago,
@TipoExento,@ModalidadRoamer)


FETCH NEXT FROM POINTER INTO @PkRegistro,@A,@B,@CALLDATETIME,@DURATION,@Trunk,@RegisterType


END

CLOSE POINTER
DEALLOCATE POINTER


DECLARE POINTER2 CURSOR
FAST_FORWARD
FOR
SELECT infobilling_raw_prd.dbo.RAWPRD.PKRegistro,
infobilling_raw_prd.dbo.RAWPRD.ThirdPartyOtherNumber as A,
infobilling_raw_prd.dbo.RAWPRD.SubscriberNumber as B,
infobilling_raw_prd.dbo.RAWPRD.FecRegister AS CallDateTime,
infobilling_raw_prd.dbo.RAWPRD.Duration,
infobilling_raw_prd.dbo.RAWPRD.OutTrunc AS Trunk,
infobilling_raw_prd.dbo.RAWPRD.RegisterType


FROM infobilling_raw_prd.dbo.RAWPRD INNER JOIN
Trunks ON infobilling_raw_prd.dbo.RAWPRD.OutTrunc
COLLATE Modern_Spanish_CI_AS = Trunks.Trunk
WHERE (SUBSTRING(infobilling_raw_prd.dbo.RAWPRD.FecRegister, 1, 8) = @p_fecha) AND
(infobilling_raw_prd.dbo.RAWPRD.RegisterType = '1D') AND
(Trunks.FkTipoTrafico = 2)



FETCH NEXT FROM POINTER2 INTO @PkRegistro,@A,@B,@CALLDATETIME,@DURATION,@Trunk,@RegisterType


WHILE @@FETCH_STATUS = 0
BEGIN
set @numero = substring( @A,4,15)
set @prefix = substring(@A,1,3)
set @ModalidadTelefonia= dbo.Get_Modalidad_Telefonia(@B)
set @ModalidadPago= dbo.Get_Modalidad_Pago(@B)
set @TipoExento= dbo.Get_Tipo_Exento(@A)
set @ModalidadRoamer= dbo.Get_Modalidad_Roamer(@B)

SET @FkOperadoraA= NULL
SET @DestinoGroupA = NULL


SELECT @DestinoGroupA = FkDestinoGroup,@FkOperadoraA=FkOperadora
FROM infobilling_cdr_prd.dbo.getdatosdestino(@prefix, @numero ,'PNN' )



set @numero = substring( @B,4,15)
set @prefix = substring(@B,1,3)
SET @FkOperadoraB= NULL
SET @DestinoGroupB = NULL


SELECT @DestinoGroupB = FkDestinoGroup,@FkOperadoraB=FkOperadora
FROM infobilling_cdr_prd.dbo.getdatosdestino('000', @numero ,'PNN' )

INSERT INTO Entrantes_Tmp
(PKRegistro , A, B , CallDateTime, Duration, Trunk, RegisterType, Fecha,
FkOperA,FkDestinoGroupA,FkOperB,FkDestinoGroupB,Tasa_Cambio,FkModalidadTelefonia,FkModalidadPago,
FkTipoExento,FkModalidadRoamer)
VALUES
(@PKRegistro,@A, @B, @CallDateTime, @Duration, @Trunk, @RegisterType, @Fecha,
@FkOperadoraA,@DestinoGroupA, @FkOperadoraB,@DestinoGroupB, @Tasa,@ModalidadTelefonia,@ModalidadPago,
@TipoExento,@ModalidadRoamer)


FETCH NEXT FROM POINTER2 INTO @PkRegistro,@A,@B,@CALLDATETIME,@DURATION,@Trunk,@RegisterType


END
CLOSE POINTER2
DEALLOCATE POINTER2
END


--------------------------

Could it be that there is a cursor limitation that I'm not aware of that doesn't allow me to do this procedure through a job?

 

[SQLSister]

First I would suggest that your cursors are entirely unnecessary and should all be removed and replaced with set-based statements. This fixes your problem and improves your system efficiency. Cursors should NEVER be used for updates or inserts! Learn to use the insert statement with Select instead of using the values keyword. Look up how in books online.

 

[Rob1412]

OK , Everyone seems to have the same opinion on the use of cursors, could you give me a simple example on how to avoid using them. Because i really don't see anyway around them.

Enlighten Me

 

[SQLSister]

I told you, use an insert staement with a select instead of a a value clause

Insert into table1 (col1, col2, col3, col4)
Select table3.Col1, 'sometext', Sum(table2.Col4), getdate() from table2 join table 3 on table2.id = table 3.id where table2.col5 = 12345

This would give you all the records that meet the criteria specified in the where clause and insert the specified fields from the involed tables as well as some text that goes in every record and the current date and time into the first table.


Assuming that you have 2000 records which meet the criteria, this will execute once (in milliseconds if you have proper indexing) whereas the cursor will do 2000 separte insert staements which might take minutes. This is why you should not use cursors if they can be avoided and there are very few cases where they are actually needed and virtually never for data inserts, deletes or updates.