Solved

VBA executed SQL stored Proc -- row limitation

Posted on 2009-07-07
6
537 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 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

820 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