Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA executed SQL stored Proc -- row limitation

Posted on 2009-07-07
6
Medium Priority
?
560 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 375 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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.
Viewers will learn how the fundamental information of how to create a table.

670 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