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?