Stored procedure in ADP not running complete?

[flaviooooo]

Hey,

I have a Stored Procedure, which I trigger in my ADP-file. It uses some parameters from a form.

The SP needs to fill a table with data from several tables, and consists of several 'update'-queries.

Now, when I run this Sp through the code, it seems that it doesn't reach the last 2 update-queries? That data remains empty in the table.

When I run the exact same SP on the SQL-server itself, everything fills OK.

I run the code in the following manner:

Dim strsql As String

'Stored Procedure build with following Parameters
'@COMPA
'@BRAND
'@IMPORTDATE
'@REQDATE
'@STOCKDATE

strsql = "Exec spReportReqW " & _
         "'" & [Forms]![frmRapporten]!cmbOrigine_Type.Column(2) & "', " & _
         "'" & SQLDate([Forms]![frmRapporten]!cmbSAPDate) & "', " & _
         "'" & SQLDate([Forms]![frmRapporten]!cmbReqDate) & "', " & _
         "'" & SQLDate([Forms]![frmRapporten]!cmbStockDate) & "'"

Debug.Print strsql

DoCmd.RunSQL strsql

Any ideas on what can cause this??

Thanks in advance

Kind regards

 

[PWise]

The SP needs to fill a table with data from several tables, and consists of several 'update'-queries.

Now, when I run this Sp through the code, it seems that it doesn't reach the last 2 update-queries? That data remains empty in the table

Is that Update Queries or Append Queries.

 

[flaviooooo]

Update...

I meant that the data in the cells that need to be updated remains empty

 

[PWise]

I see that the SP take 5 parameters you are only passing 4 parameters.

Debug.Print strsql

What does this print

what is the SQL of the Update Queries

 

[flaviooooo]

Ah yes, the brand thing... I removed that some time ago, but it's still there in the comments.

Here is the strsql:

Exec spReportReqW '201', '2007-5-4 10:5:25', '2007-5-4 0:0:0', '2007-5-4 0:0:0'


When I run this exact same code in query analyzer, it works perfectly

 

[flaviooooo]

Here is the complete stored procedure, for your viewing pleasure

You can see that only 4 parameters are needed

It's about at the "--Update the TEMP-table with the "SAP Sales Orders"-data that was selected, adding the values/week"-part that it doesn't update the records anymore. Anything previous to that is filled in perfectly from the ADP...

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER  PROCEDURE dbo.spReportReqW
(@COMPA nvarchar(4),
--@BRAND nvarchar(255),
@IMPORTDATE nvarchar(50),
@REQDATE nvarchar(50),
@STOCKDATE nvarchar(50))
AS DELETE FROM dbo.TEMPREQ_W

--Insert the Requirements-data that was selected in the TEMP-table
                         
INSERT INTO dbo.TEMPREQ_W
           (COMPA, PLANT, WAREH, MAT_NUMBER, REQDATE, [FOR], YEAR, PERIODE, WEEK, DATE_VAN, DATE_TOT, DATE_LEV, QTFOR_UNIT, ORIGINE, SAP, CODE)
SELECT     COMPA, PLANT, WAREH, MAT_NUMBER, REQDATE, [FOR], YEAR, PERIODE, WEEK, DATE_VAN, DATE_TOT, DATE_LEV, QTFOR_UNIT, ORIGINE, SAP, CODE
FROM       dbo.vwReq_W
WHERE     (COMPA = @COMPA) AND --(CUST_BRAND = @BRAND) AND 
	  (DATEADD(Minute, DATEDIFF(Minute, 0, REQDATE), 0)= DATEADD(Minute, DATEDIFF(Minute, 0, @REQDATE), 0))

--Update the TEMP-table with the "SAP Artikel Klant"-data that was selected

UPDATE    dbo.TEMPREQ_W
SET       IMPORTDATE = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.IMPORTDATE is null) then 0 else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.IMPORTDATE END, 
	  CUST_BRAND = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH Customer Brand] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH Customer Brand] END, 
	  ArticleGroup = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH Article Group] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH Article Group] END,  
	  ArtDescr = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[ArtDescr] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[ArtDescr] END, 
	  Remark = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.Remark is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.Remark END, 
	  [Customer's description of material] = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[Customer's description of material] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[Customer's description of material] END,  
	  [Material Description] = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[Material Description] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[Material Description] END, 
	  [Customer Material Number] = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[Customer Material Number] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[Customer Material Number] END, 
	  [AFH Specnr] = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH Specnr] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH Specnr] END,  
	  [AFH MF Remark] = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH MF Remark] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH MF Remark] END,  
	  Material = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.Material is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.Material END
FROM	  dbo.TEMPREQ_W LEFT OUTER JOIN
          dbo.vwSAP_Artikel_MF_Artikel_Single_Detail ON dbo.TEMPREQ_W.ORIGINE = dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.ORIGINE AND 
	  dbo.TEMPREQ_W.MAT_NUMBER = dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.CustMatNum AND
	  (DATEADD(Minute, DATEDIFF(Minute, 0, vwSAP_Artikel_MF_Artikel_Single_Detail.ImportDate), 0)= DATEADD(Minute, DATEDIFF(Minute, 0, @IMPORTDATE), 0))-- OR
         -- DATEADD(Minute, DATEDIFF(Minute, 0, ImportDate), 0) IS NULL) AND
         
-- Update the TEMP-table with the Stockdata that MF has sent us

UPDATE    dbo.TEMPREQ_W
SET       MAT_DESCRIPTION = case when (dbo.tStock.MAT_DESCRIPTION is null) then '' else dbo.tStock.MAT_DESCRIPTION END, 
          SPECNR = case when (dbo.tStock.SPECNR is null) then '' else dbo.tStock.SPECNR END, 
	  SAFETY_STOCK = case when (dbo.tStock.SAFETY_STOCK is null) then 0 else dbo.tStock.SAFETY_STOCK END, 
	  EO_DATE = case when (dbo.tStock.EO_DATE is null) then 0 else dbo.tStock.EO_DATE END, 
	  FO_MATERIAL = case when (dbo.tStock.FO_MATERIAL is null) then '' else dbo.tStock.FO_MATERIAL END,  
	  QTY_MARSNETUNSTRICTEDSTK = case when (dbo.tStock.QTY_MARSNETUNSTRICTEDSTK is null) then 0 else dbo.tStock.QTY_MARSNETUNSTRICTEDSTK END, 
	  QTY_MARSTOTALSTK = case when (dbo.tStock.QTY_MARSTOTALSTK is null) then 0 else dbo.tStock.QTY_MARSTOTALSTK END, 
          QTY_MARSBLOCKEDSTK = case when (dbo.tStock.QTY_MARSBLOCKEDSTK is null) then 0 else dbo.tStock.QTY_MARSBLOCKEDSTK END,  
	  QTY_MARSQUANINSPSTK = case when (dbo.tStock.QTY_MARSQUANINSPSTK is null) then 0 else dbo.tStock.QTY_MARSQUANINSPSTK END,  
	  QTY_VENDORNETUNSTRICTEDSTK = case when (dbo.tStock.QTY_VENDORNETUNSTRICTEDSTK is null) then 0 else dbo.tStock.QTY_VENDORNETUNSTRICTEDSTK END, 
	  Qty_VENDORTOTALSTK = case when (dbo.tStock.Qty_VENDORTOTALSTK is null) then 0 else dbo.tStock.Qty_VENDORTOTALSTK END,  
          Qty_VENDORBLOCKEDSTK = case when (dbo.tStock.Qty_VENDORBLOCKEDSTK is null) then 0 else dbo.tStock.Qty_VENDORBLOCKEDSTK END,  
	  Qty_VENDORQUANINSPSTK = case when (dbo.tStock.Qty_VENDORQUANINSPSTK is null) then 0 else dbo.tStock.Qty_VENDORQUANINSPSTK END,  
	  QTY_VENDORISSUEDNETUNSTRICTEDSTK = case when (dbo.tStock.QTY_VENDORISSUEDNETUNSTRICTEDSTK is null) then 0 else dbo.tStock.QTY_VENDORISSUEDNETUNSTRICTEDSTK END,  
	  Qty_VENDORISSUEDTOTALSTK = case when (dbo.tStock.Qty_VENDORISSUEDTOTALSTK is null) then 0 else dbo.tStock.Qty_VENDORISSUEDTOTALSTK END,  
          Qty_VENDORISSUEDBLOCKEDSTK = case when (dbo.tStock.Qty_VENDORISSUEDBLOCKEDSTK is null) then 0 else dbo.tStock.Qty_VENDORISSUEDBLOCKEDSTK END,  
	  Qty_VENDORISSUEDQUANINSPSTK = case when (dbo.tStock.Qty_VENDORISSUEDQUANINSPSTK is null) then 0 else dbo.tStock.Qty_VENDORISSUEDQUANINSPSTK END,  
	  BASE_UNIT_OF_MEASURE = case when (dbo.tStock.BASE_UNIT_OF_MEASURE is null) then '' else dbo.tStock.BASE_UNIT_OF_MEASURE END, 
	  STOCKDATE = case when (dbo.tStock.DOCDATE is null) then 0 else dbo.tStock.DOCDATE END 
FROM	  dbo.TEMPREQ_W LEFT OUTER JOIN
          dbo.tStock ON dbo.TEMPREQ_W.COMPA = dbo.tStock.COMPA AND 
	  dbo.TEMPREQ_W.PLANT = dbo.tStock.PLANT AND
          dbo.TEMPREQ_W.MAT_NUMBER = dbo.tStock.MAT_NUMBER AND 
          DATEADD(Minute, DATEDIFF(Minute, 0, dbo.tStock.DOCDATE), 0)= 
	  DATEADD(Minute, DATEDIFF(Minute, 0, @STOCKDATE), 0) --OR
          --DATEADD(Minute, DATEDIFF(Minute, 0, dbo.TEMPREQ_W.STOCKdate), 0) IS NULL

--Update the TEMP-table with the "SAP Sales Orders"-data that was selected, adding the cumulated value 

UPDATE    TEMPREQ_W
SET       SALESORD_TOTAL = case when (dbo.vwSalesOrd_cumul.SalesOrd is null) then 0 else dbo.vwSalesOrd_cumul.SalesOrd END
FROM      dbo.TEMPREQ_W LEFT OUTER JOIN
          dbo.vwSalesOrd_cumul ON dbo.TEMPREQ_W.COMPA = dbo.vwSalesOrd_cumul.CODE AND 
          dbo.TEMPREQ_W.MAT_NUMBER = dbo.vwSalesOrd_cumul.CustMatNum AND 
          dbo.TEMPREQ_W.ImportDate = dbo.vwSalesOrd_cumul.ImportDate

--Update the TEMP-table with the "SAP Sales Orders"-data that was selected, adding the values/week

UPDATE    TEMPREQ_W
SET       SALESORD_WEEK = case when (dbo.vwSalesOrd_perWeek.SalesOrd is null) then 0 else dbo.vwSalesOrd_perWeek.SalesOrd END
FROM      dbo.TEMPREQ_W LEFT OUTER JOIN
          dbo.vwSalesOrd_perWeek ON dbo.TEMPREQ_W.COMPA = dbo.vwSalesOrd_perWeek.CODE AND 
          dbo.TEMPREQ_W.MAT_NUMBER = dbo.vwSalesOrd_perWeek.CustMatNum AND 
          dbo.TEMPREQ_W.ImportDate = dbo.vwSalesOrd_perWeek.ImportDate AND
	  dbo.TEMPREQ_W.DATE_VAN = dbo.vwSalesOrd_PerWeek.DATE_VAN

--Update the TEMP-table with the "SAP Production Orders"-data that was selected, adding the cumulated value

UPDATE    TEMPREQ_W
SET       PRODORD_TOTAL = case when (dbo.vwProdOrd_cumul.ProdOrd is null) then 0 else dbo.vwProdOrd_cumul.ProdOrd END
FROM      dbo.TEMPREQ_W LEFT OUTER JOIN
          dbo.vwProdOrd_cumul ON dbo.TEMPREQ_W.COMPA = dbo.vwProdOrd_cumul.CODE AND 
          dbo.TEMPREQ_W.MAT_NUMBER = dbo.vwProdOrd_cumul.CustMatNum AND 
          dbo.TEMPREQ_W.ImportDate = dbo.vwProdOrd_cumul.ImportDate

--Update the TEMP-table with the "SAP Production Orders"-data that was selected, adding the values/week

UPDATE    TEMPREQ_W
SET       PRODORD_WEEK = case when (dbo.vwProdOrd_perWeek.ProdOrd is null) then 0 else dbo.vwProdOrd_perWeek.ProdOrd END
FROM      dbo.TEMPREQ_W LEFT OUTER JOIN
          dbo.vwProdOrd_perWeek ON dbo.TEMPREQ_W.COMPA = dbo.vwProdOrd_perWeek.CODE AND 
          dbo.TEMPREQ_W.MAT_NUMBER = dbo.vwProdOrd_perWeek.CustMatNum AND 
          dbo.TEMPREQ_W.ImportDate = dbo.vwProdOrd_perWeek.ImportDate AND
	  dbo.TEMPREQ_W.DATE_VAN = dbo.vwProdOrd_PerWeek.DATE_VAN

--Update the TEMP-table with the "SAP Stock"-data that was selected

UPDATE    TEMPREQ_W
SET       STOCK_HALEN = case when (dbo.vwStockHalen.Ustock is null) then 0 else dbo.vwStockHalen.Ustock END, 
	  STOCK_HALEN_BUN = case when (dbo.vwStockHalen.Ustock is null) then '' else dbo.vwStockHalen.BUn END
FROM      dbo.TEMPREQ_W LEFT OUTER JOIN
          dbo.vwStockHalen ON dbo.TEMPREQ_W.COMPA = dbo.vwStockHalen.CODE AND 
          dbo.TEMPREQ_W.MAT_NUMBER = dbo.vwStockHalen.CustMatNum AND 
          dbo.TEMPREQ_W.ImportDate = dbo.vwStockHalen.ImportDate

-- Empty and fill a temporary table to put the "SAP Stock"-data that was selected 
-- This table will be used to create the subreports

DELETE FROM dbo.TEMPSAPSTOCK

INSERT INTO dbo.TEMPSAPSTOCK
          (CustMatNum, Customer, Material, SLoc, Unrestr, InQual, Total, [Special Stock], BUn, StockKlant, ImportDate, CODE)
SELECT    CustMatNum, Customer, Material, SLoc, Unrestr, InQual, Total, [Special Stock], BUn, StockKlant, ImportDate, CODE
FROM      dbo.vwStock
WHERE	  (DATEADD(Minute, DATEDIFF(Minute, 0, ImportDate), 0)= DATEADD(Minute, DATEDIFF(Minute, 0, @IMPORTDATE), 0) OR
          DATEADD(Minute, DATEDIFF(Minute, 0, ImportDate), 0) IS NULL)	


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 

[flaviooooo]

Hey,

I have now 'solved' this. Apparently, the ADP had issues with the amount of data in my tables and it 'timed-out' or something like that?

I created an Archive-database, and removed a lot of data from the main tables. Now the Stored Procedure runs completey in my ADP as well...

Does anyone know how to set this timeout or what could have been going on?