asked on

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?

Guy Hengel [angelIII / a3]

seeing the stored proc and the calling code could help identifying the problem.
however, the cursor is likely to be replaced by something else...

T Hoecherl

ASKER

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.

T Hoecherl

ASKER

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

T Hoecherl

ASKER

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

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

I am confused as to why I was awarded points. Feel free to post a message in Community Support to have the points redistributed.