Solved

VBA executed SQL stored Proc -- row limitation

Posted on 2009-07-07
6
542 Views
Last Modified: 2012-05-07
I have an SQL stored procedure that uses a cursor to cycle through records in a table and perform various tasks.  When I execute the stored proc in Management Studio, it works properly.  However, when I execute it from VBA (Microsoft Dynamics GP), it processes the first 128 rows and then stops.  It doesn't throw any errors -- it simply stops processing after it has finished 128 records.  Is there a buffer problem or something that could be causing this?
0
Comment
Question by:thoecherl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24798519
seeing the stored proc and the calling code could help identifying the problem.
however, the cursor is likely to be replaced by something else...
0
 

Author Comment

by:thoecherl
ID: 24798598
Except the cursor works fine when the proc is executed in Management Studio.  And if I execute from VBA and the table has fewer than 128 rows, it works fine.  The problem only occurs when I execute from VBA and I have more than 128 records.

I need to access the SQL and VBA code from a client so as soon as I can remote into the server, I will attach it to this incident.

Thanks for responding.
0
 

Author Comment

by:thoecherl
ID: 24799179
Here is the portion of the VBA code that calls the procedure:

    Set userinfo = CreateObject("RetrieveGlobals9.retrieveuserinfo")
    luserid = userinfo.retrieve_user()
    luserid = UserInfoGet.UserID
    lintercompanyid = userinfo.intercompany_id()
    lsqldatasourcename = userinfo.sql_datasourcename()
   
    IntercompanyID = UserInfoGet.IntercompanyID
   
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = IntercompanyID & ".." & dtspkg
    Set rst = cmd.Execute
   
    If rst.State = adStateOpen Then rst.Close
    If cn.State = 1 Then cn.Close
    Set cmd = Nothing
    Set cn = Nothing
    Set rst = Nothing
    Set userinfo = Nothing

    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = IntercompanyID & ".." & distpkg
    Set rst = cmd.Execute
   
    If rst.State = adStateOpen Then rst.Close
    If cn.State = 1 Then cn.Close
    Set cmd = Nothing
    Set cn = Nothing
    Set rst = Nothing
    Set userinfo = Nothing

    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = IntercompanyID & ".." & processpkg
    Set rst = cmd.Execute

The last 6 lines call the proc and earlier in the code the processpkg variable has been given the value udp_CREATE_AP_CREDIT_LINES_A, the name of the stored procedure.  Here is the cursor code in the stored procedure that is stopping short of completion:

DECLARE AddCreditLines CURSOR FAST_FORWARD FOR
      SELECT DISTINCT VCHRNMBR
      FROM PCT_CRE_DIST_A;
      
OPEN AddCreditLines;
FETCH NEXT FROM AddCreditLines INTO @VOUCHER

WHILE @@FETCH_STATUS = 0
      BEGIN
            SELECT @TOTAL = (SELECT SUM(CAST(TRXAMNT AS NUMERIC(19,5))) FROM PCT_CRE_DIST_A WHERE VCHRNMBR = @VOUCHER)
            SELECT @SEQ = (SELECT MAX(SEQNUMBR) FROM PCT_CRE_DIST_A WHERE VCHRNMBR = @VOUCHER) + 1

            --CREATE CREDIT ENTRY
            INSERT PCT_CRE_DIST_A
            (RECORD_ID,CMPNYNUM,VCHRNMBR,PMTRXTYP,VNDTRXNM,SEQNUMBR,ENTITYNM,GLACCTNM,TRXAMNT,DSCDSTFG,TRXDESC,DSTCONUM,TXCODE_1,
            TXCODE_2,TXCODE_3,CNCYRATE,ADDLINFO,SNGPONUM,SEQNMBR2,ZDFIL1,PMREFNUM,TRXNUMBR,SEQNMBR3,QUANTITY,PARTNMBR,POREQFLG,
            JOBNUM_1,JOBNUM_2,JOBNUM_3,JOBNUM_4,JOBNUM_5,JOBNUM_6,UNITS_CD,TRXUNITS,FACLASS, DIST_TYPE)

            SELECT RECORD_ID,CMPNYNUM,VCHRNMBR,PMTRXTYP,VNDTRXNM,@SEQ,ENTITYNM,'215010-000-101',TRXAMNT,DSCDSTFG,'CREATED BY STORED PROC',DSTCONUM,TXCODE_1,
            TXCODE_2,TXCODE_3,CNCYRATE,ADDLINFO,SNGPONUM,SEQNMBR2,ZDFIL1,PMREFNUM,TRXNUMBR,SEQNMBR3,QUANTITY,PARTNMBR,POREQFLG,
            JOBNUM_1,JOBNUM_2,JOBNUM_3,JOBNUM_4,JOBNUM_5,JOBNUM_6,UNITS_CD,TRXUNITS,FACLASS, 2
            FROM PCT_CRE_DIST_A
            WHERE VCHRNMBR = @VOUCHER AND SEQNUMBR = 1
            
            --UPDATE TRXAMNT AND CHECK AMOUNT FIELDS
            UPDATE PCT_CRE_DIST_A
            SET TRXAMNT = -@TOTAL
            WHERE VCHRNMBR = @VOUCHER AND SEQNUMBR = @SEQ
                        
            FETCH NEXT FROM AddCreditLines INTO @VOUCHER
      END
      
CLOSE AddCreditLines;
DEALLOCATE AddCreditLines;

Again, remember, this works if I run it from Management Studio or if the table has fewer than 129 rows.  Ths problem is when I call it from VBA.

T
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

Author Comment

by:thoecherl
ID: 24799444
I have this resolved now.  The VBA code was actually calling a stored procedure that did the first phase and then when it had run to completion the first proc called a second proc.  The first proce has always worked.  It was the second proc that had the trouble.

I changed VBA to call the first proc and when it is finished, call the second proc.  That solved the problem, though I don't really know wny.

T
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 24799618
>>though I don't really know wny.<<
Unfortunately, without seeing the entire Stored Procedure it is impossible to say for sure.  However, as angelIII has suggested this could be resolved quite easily without the need to resort to a CURSOR.

>>That solved the problem<<
Please post a message by clicking on the Request Attention link to have this question deleted.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24921358
I am confused as to why I was awarded points.  Feel free to post a message in Community Support to have the points redistributed.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question