Solved

VBA executed SQL stored Proc -- row limitation

Posted on 2009-07-07
6
524 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
  • 3
  • 2
6 Comments
 
LVL 142

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now