DTS Cache

[omarmtz]

Somebody knows how works the DTS cache??¡

I have trouble with this. Because, i have a DTS that run several stored procedures, but i create the stored procedures in the SQL Analyzer, and when i run my DTS it jumps some instruccions, but when i run the stored procedure in the SQL Analyzer it works correctly.

And i thought that maybe could be the cache.

Someboby knows anything about this?¡?¡

 

[tlbroadbent]

I don't know about any DTS cache. Procedure cache is procedure cache whether the procedure is executed from Query Analyzer, a DTS package, ASP, VB, OSQL, etc.

How can you be sure the insructions in the stored procedures are being "jumped?" Are complete procedures skipped? What types of instructions are skipped?

Are any warning or error messages displayed in either method of executing the processes? Are you executing the stored procedures using the Execute SQL Task in the DTS package?

Please provide more details about the differences you see between the executions. Also, post the Sp execution statements. We may need to see the SP code if nothing comes from the initial analysis. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[omarmtz]

I have done another thing, but i have the same problem of the Stored procedures being skipped. For example...
In the Query Analyzer:
EXEC sp_cicle_dwh_facts

and internally this procedure has the following, it's a cicle over the different weeks...:

CREATE PROCEDURE sp_cicle_dwh_facts AS
...
DECLARE CUR_WEEKS CURSOR FOR
SELECT DISTINCT SE_Week FROM LU_WEEKS
OPEN CUR_WEEKS
FETCH NEXT FROM CUR_WEEKS INTO @Next
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_dwh_facts

EXEC sp_states

FETCH NEXT FROM CUR_WEEKS INTO @Next
END

CLOSE CUR_WEEKS
DEALLOCATE CUR_WEEKS
GO

The problem is that it skippes the sp_states... it has the following...
CREATE PROCEDURE sp_cargar_estados_anteriores_FP AS
...
INSERT INTO FACT_CONTRATOS
SELECT ... FROM FACT_CONTRATOS T1 join LU_CONTRATO T3 on (T1.Id_Contrato = T3.Id_Contrato)
WHERE T1.Fecha > @Fecha_Carga
AND T1.Id_Estado_Contrato in ('AF')
AND T1.Id_Tipo_Contrato not in (1)
AND DATEDIFF(day, T1.Fecha_OT, @Fecha_Carga-1) > 0
AND T3.Fecha_Fin > @Fecha_Carga

But when i run this procedure for one week... it works!!!

I appreciate your help!!!

 

[omarmtz]

It's funny that when i run the stored procedure for one week, it works, but if i run the cicle over several weeks, it doesn't do what i want, it doesn't insert anything.

 

[omarmtz]

The reason could be the use of stored procedure in cascade??¡?¡ i'm going to put the instructions in the stored procedure directly... and see what happens...

 

[tlbroadbent]

Where is the code for sp_states? Is sp_states a short name for sp_cargar_estados_anteriores_FP? How is the value of the variable @Fecha_Carga set in sp_cargar_estados_anteriores_FP? Shold it be a parameter?

In sp_cicle_dwh_facts you fetch into a variable named @next but the variable is never referenced or used for anything. What is the purpose? Is it it suposed to be a parameter for teh stored procedures that are executed in the loop?

Did you remove code from the procedures you posted? I am confused because nothing matches or makes any sense. Please post the actual code with correct names so we can have a slight chance of helping.

Thanks.
If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[omarmtz]

This is the real name fot the stored procedure that individually it works for only a week.

CREATE PROCEDURE sp_cargar_estados_anteriores AS
DECLARE @Fecha_Carga smalldatetime
--> I get the week
SELECT @Fecha_Carga = Fecha_Carga FROM ESC_FECHA_CARGA
--> Check this instruction ******FIRST INSTRUCTION********
INSERT INTO FACT_CONTRATOS
SELECT @Fecha_Carga As Fecha, T1.Id_Zona, T1.Id_Estado, T1.Id_Centro, T1.Id_Usuario, T1.Id_Contrato,
1 As Id_Tipo_Contrato, 'FP' As Id_Estado_Contrato, T1.Id_Familia, T1.Id_Marca, T1.Id_Producto, T1.Ot, 'AS' As Estadt, T1.Fecha_Ot, NULL As Dias_Fuera_Plazo,
T1.Importe_contr, T1.Importe_Contr_Anticipo, T1.Importe_Contr_Pendiente, T1.Unidades
FROM FACT_CONTRATOS T1 join LU_CONTRATO T3 on (T1.Id_Contrato = T3.Id_Contrato)
WHERE T1.Fecha > @Fecha_Carga
AND T1.Id_Estado_Contrato in ('AF')
AND T1.Id_Tipo_Contrato not in (1)
AND DATEDIFF(day, T1.Fecha_OT, @Fecha_Carga-1) > 0
AND T3.Fecha_Fin > @Fecha_Carga

******SECOND INSTRUCTION********

INSERT INTO FACT_CONTRATOS
SELECT T2.SE_InicioSemana As Fecha, T1.Id_Zona, T1.Id_Estado, T1.Id_Centro, T1.Id_Usuario, T1.Id_Contrato,
1 As Id_Tipo_Contrato, 'AC' As Id_Estado_Contrato, T1.Id_Familia, T1.Id_Marca, T1.Id_Producto, T1.Ot, 'AS' As Estadt, T1.Fecha_Ot, NULL As Dias_Fuera_Plazo,
T1.Importe_contr, T1.Importe_Contr_Anticipo, T1.Importe_Contr_Pendiente, T1.Unidades
FROM FACT_CONTRATOS T1 join LU_CONTRATO T3 on (T1.Id_Contrato = T3.Id_Contrato), LU_SEMANA T2
WHERE T1.Fecha = @Fecha_Carga
AND T3.Fecha_Contrata between T2.SE_InicioSemana and T2.SE_FinSemana
AND T1.Id_Estado_Contrato in ('AF')
AND T1.Id_Tipo_Contrato not in (1)

GO

CREATE PROCEDURE sp_cargar_ciclo_dwh_facts AS
DECLARE @Fecha_Inicio smalldatetime
DECLARE @Fecha_Fin smalldatetime
DECLARE @Siguiente smalldatetime

SELECT @Fecha_Inicio = MIN(Fecha_Contra) FROM ESC_CONTRATO

SELECT @Fecha_Fin = getdate()

DECLARE CUR_SEMANAS CURSOR FOR
SELECT SE_InicioSemana FROM LU_SEMANA where SE_InicioSemana between @Fecha_Inicio and @Fecha_Fin

OPEN CUR_SEMANAS
FETCH NEXT FROM CUR_SEMANAS INTO @Siguiente
WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE ESC_FECHA_CARGA SET Fecha_Carga = @Siguiente

EXEC sp_cargar_dwh_facts

EXEC sp_cargar_estados_anteriores

FETCH NEXT FROM CUR_SEMANAS INTO @Siguiente
END
CLOSE CUR_SEMANAS
DEALLOCATE CUR_SEMANAS
go

If i run this procedure, the FIRST INSTRUCTION of the sp_cargar_estados_anteriores doesn't run, but the SECOND INSTRUCTION does. I don't know if the DATEDIFF instruction is doing strange things...

 

[tlbroadbent]

I believe you are correct about the datediff function being the problem. I think you need to reverse the order of the dates in the function to obtain a postive value. I say this because it appears that T1.Fecha_OT should be between @Fecha_Carga and @Fecha_Carga_fin. If this is true, then T1.Fecha_OT should be greater than @Fecha_Carga.

DATEDIFF(day, @Fecha_Carga-1, T1.Fecha_OT) > 0
If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm