VBA executed SQL stored Proc -- row limitation

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?
T HoecherlDeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
T HoecherlDeveloperAuthor Commented:
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
T HoecherlDeveloperAuthor Commented:
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

T HoecherlDeveloperAuthor Commented:
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
Anthony PerkinsCommented:
>>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.