x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 6 replies
  • Subscribers 30 subscribers
  • Views 800 views
  • Users 0 members are here
Browse Forums
Announcements
19
topics
3
replies
General Discussion
790
topics
1k
replies
slacka

Upgrade to BV2023/ActianSQL V15 (previously Pervasive V10) - issues with querying GL_TRANSACTIONS and workarounds

Posted By

After upgrading Sage Business Vision to version 2023 and the subsequent database update from Pervasive V10 to Actian V15,
Issues have arisen which affect querying the GL_TRANSACTIONS table.  I am currently in contact with both Actian Support and Sage Customer Support and I'm trying to get them to talk to each other to resolve this. In the mean time, if you ever write your own software to interact with the database AND OR you use Actian Control Center to do queries (previously Pervasive Control Center), here are some tips to fix queries of yours which have likely broken.

Tips if you're still running Pervasive V10:
1. To lookup line entries for a particular TRANS_NO, you cannot do a direct string compare against the TRANS_NO, you need to use one of the following:
select * from GL_TRANSACTIONS where LTRIM(TRANS_NO) = '0000123456'
select * from GL_TRANSACTIONS where CONVERT(TRANS_NO, SQL_CHAR) = '0000123456'

Tips if you're now running Actian V15:

These are now broken:
- order by RECNO
- compare against RECNO
- group by TRANS_NO ("solution" below groups by DIVISION first and then TRANS_NO, which is fine in our case because division is the same for all line entries)

Work arounds:
select * from GL_TRANSACTIONS order by CONVERT(RECNO, SQL_INTEGER)
select * from GL_TRANSACTIONS where CONVERT(RECNO, SQL_INTEGER) = 1
select SUM(DEBIT_AMT) as SumDebit from GL_TRANSACTIONS group by DIVISION, TRANS_NO

Tips if you're connecting via CODE / ODBC / ADO.
- previously, large queries were just working but after the update they started timing out and quite fast.
  This may have to do with our configuration somewhere but I think the default TIMEOUT has changed.
  I think the default timeout is now 30 secs.  In VBA, here is how you can increase it.
 Dim DB as ADODB.Connection
Set DB = new ADODB.Connection
DB.Open "your connection string"
DB.CommandTimeout = 600  'increase timeout to 600 from 30. 

  • 0

    Hi  ,

    Thanks for reaching out. Did you ever find an answer for this issue? If you are still looking for guidance, please let us know and we can try to point you in the right direction or suggest some resources or next steps.

    Warm Regards,

    Mayla

  • 0 in reply to ClarkM

    Hello Mayla,
    A third party has hinted that our indexes are likely out of alignment, which is causing this.
    I confirmed with a support person at Sage that the query issues noted above also affected them for V10, V15.
    The Sage support manager said it doesn't affect the BV software, so they will not be wasting time on it.
    However, if it's truely an issue breaking the indexing, then it should be affecting BV performance.
    That all said, fixing the indexes is not a skill i have time to learn at the moment, as I have higher priority tasks assigned to me. 
    If you have any information that is useful for resolving this issue, please post it here in this thread so other users also have access to it.
    Thx,
    Mark.

  • 0 in reply to slacka

    Hi  ,

    Thank you for bringing this issue to our attention. Your detailed explanation of the challenges you're facing with querying the GL_TRANSACTIONS table and suspected index misalignment is invaluable. I understand how this issue could be impacting your workflow and software performance.

    I've escalated your concerns to our support management team for further review and action. While I cannot guarantee an immediate resolution, rest assured that your feedback is being taken seriously and will be looked into as a priority.

    In the meantime, I encourage other community members to share any insights or solutions they might have regarding this issue, as you requested.

    Thank you for your understanding and patience.

    Best regards,
    Erzsi

  • 0 in reply to slacka

    Hi Mark, have you solved your issue? 'cause I found a third-party vendor who can help you fix the indexes. Moreover, I am also a student who tries exceeding and learning for the best knowledge, so I have found proficient essay writers on https://ca.papersowl.com/ to help me. I write my own research papers due to the academic assistance I get from the academic experts. Now any formula is no longer a problem for me.

  • 0

       , 

    I see that this was posted by  6 months ago.

    I don't understand why this issue hasn't been taken seriously. This seems to be a critical issue in the way a client is able to, or in many cases, not able to retrieve data from a BusinessVision database. I have tested this over the past week and the issue still exists as of today, even in brand new data sets generated in a clean install from Sage BusinessVision CSE 2023 v7.93.05, running the latest patched version of Actian v15.21.006. 

    Several indexes particularly on GL_TRANSACTIONS, AP_TRANSACTIONS, and AR_TRANSACTIONS tables seem to be broken. Successful table queries are only possible by dropping indexes or bypassing them with LTRIM statements or by converting data types, and in some case by turning ANSI padding off.

    Although there were some issues in Pervasive, these problems have become significantly worse since the addition of Actian 15 with the BusinessVision 2023 release. 

    Try running these commands, and you will quickly confirm that there are problems, that should be of high priority to have resolved.

    PROBLEMS WITH GL_TRANSACTIONS TABLE & INDEXES

    //This does not work as expected, many results are missing
    //RECNO is an integer but appears to sort as if it was a string data type.
    select * from "GL_TRANSACTIONS" order by RECNO

    //This does not work as expected, many results are missing
    select * from "GL_TRANSACTIONS" where TRANS_NO = '{insert known transaction #}' 

    //This does not work as expected, many results are missing
    select DISTINCT(TRANS_NO) from "GL_TRANSACTIONS"

    //This DOES work as expected because it bypasses the indexes
    select * from "GL_TRANSACTIONS" where LTRIM(TRANS_NO) = '{insert known transaction #}'

    //This DOES work as expected because it bypasses the indexes
    select * from "GL_TRANSACTIONS" where LTRIM(TRANS_NO) = '{insert known transaction #}' ORDER BY CONVERT(RECNO, SQL_INTEGER)

    PROBLEMS WITH AP_TRANSACTIONS TABLE & INDEXES

    //This does not work as expected, many results are missing

    select * from AP_TRANSACTIONS where VEND = '{insert known vendor number}'

     

    PROBLEMS WITH AP_TRANSACTIONS TABLE/INDEXES

    //This does not work as expected, many results are missing

    select * from AR_TRANSACTIONS where CUST = '{insert known customer number}'

  • 0

    Hi   

    I see that this was posted by  6 months ago.

    I don't understand why this issue hasn't been taken seriously. This seems to be a critical issue in the way a client is able to, or in many cases, not able to retrieve data from a BusinessVision database. I have tested this over the past week and the issue still exists as of today, even in brand new data sets generated in a clean install from Sage BusinessVision CSE 2023 v7.93.05, running the latest patched version of Actian v15.21.006. 

    Several indexes particularly on GL_TRANSACTIONS, AP_TRANSACTIONS, and AR_TRANSACTIONS tables seem to be broken. Successful table queries are only possible by dropping indexes or bypassing them with LTRIM statements or by converting data types, and in some case by turning ANSI padding off.

    Although there were some issues in Pervasive, these problems have become significantly worse since the addition of Actian 15 with the BusinessVision 2023 release. 

    Try running these commands, and you will quickly confirm that there are problems, that should be of high priority to have resolved.

    PROBLEMS WITH GL_TRANSACTIONS TABLE & INDEXES

    //This does not work as expected, many results are missing
    //RECNO is an integer but appears to sort as if it was a string data type.
    select * from "GL_TRANSACTIONS" order by RECNO

    //This does not work as expected, many results are missing
    select * from "GL_TRANSACTIONS" where TRANS_NO = '{insert known transaction #}' 

    //This does not work as expected, many results are missing
    select DISTINCT(TRANS_NO) from "GL_TRANSACTIONS"

    //This DOES work as expected because it bypasses the indexes
    select * from "GL_TRANSACTIONS" where LTRIM(TRANS_NO) = '{insert known transaction #}'

    //This DOES work as expected because it bypasses the indexes
    select * from "GL_TRANSACTIONS" where LTRIM(TRANS_NO) = '{insert known transaction #}' ORDER BY CONVERT(RECNO, SQL_INTEGER)

    PROBLEMS WITH AP_TRANSACTIONS TABLE & INDEXES

    //This does not work as expected, many results are missing

    select * from AP_TRANSACTIONS where VEND = '{insert known vendor number}'

     

    PROBLEMS WITH AP_TRANSACTIONS TABLE/INDEXES

    //This does not work as expected, many results are missing

    select * from AR_TRANSACTIONS where CUST = '{insert known customer number}'

*Community Hub is the new name for Sage City