×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Stored procedure in ADP not running complete?

Stored procedure in ADP not running complete?

Stored procedure in ADP not running complete?

(OP)
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:

CODE

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

RE: Stored procedure in ADP not running complete?

Quote:


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

Quote:



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.

RE: Stored procedure in ADP not running complete?

(OP)
Update...

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

RE: Stored procedure in ADP not running complete?

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

RE: Stored procedure in ADP not running complete?

(OP)
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

RE: Stored procedure in ADP not running complete?

(OP)
Here is the complete stored procedure, for your viewing pleasure smile

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...


CODE

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

RE: Stored procedure in ADP not running complete?

(OP)
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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close