Connection to Sage Data with postgresql fails

SUGGESTED

Any help is greatly appreciated. 

Using Sage 100cloud Standard. 

My company uses Access to query Sage data. This works well, but I'd like to run more advanced and rapid queries. My system administrator has allowed me to install Postgresql on my computer, as a sort of trial. I have been able to import Access tables into pgAdmin, and I've created a few queries there that I would very much like to implement into the company's daily operations. I've created a pass-through query in Access from Postgresql that works well. Problem is, I am having great trouble reaching the Sage data in postgresql. I have the odbc_fdw extension. The settings, I believe, are correct: connecting to the 64-bit MAS driver with Postgresql 13. But I have been trying to run a very simple query, just to get this up and running. I'm trying: 

SELECT ci_item

FROM ci_item

LIMIT 5;

This crashes the postgresql server and spawns the error: 

LOG:  server process (PID XXXX) was terminated by exception 0xC0000005

DETAIL:   Failed process was running: SELECT ci_item
FROM ci_item
LIMIT 5;

HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.

I've done some research on this issue. I have run memory diagnostics and inspected the hardware: all that seems to be fine. My guess is that this has to do with some kind of permission error vis a vis Sage. One thought I had: could the fact that Sage runs on a file server and Postgresql only runs on a local machine impact communication between the two?

Also, I do not have administrator privileges in Sage, but it seems to me that with my Sage login and password I should still be able to access in Postgresql the data I'm able to access when I run Sage itself. 

Does anyone have experience querying Sage data with Postgresql? Again, any help would be much appreciated. Thanks.

  • 0

    You might want to try MS SQL Express instead.  There are instructions posted in a few places on how to set up OpenQuery connections to Providex data using MS SQL.  We use it often.

  • FormerMember
    0 FormerMember
    SUGGESTED

    I wrote a Sage 100 to Postgres SQL mirroring application that creates the tables from 100 definitions and keeps things updated as a threaded process. I have a screenshot posted on the OpenSage.org forum.

    I was a big MySQL fan but PSQL changed my mind. The 32 bit ODBC driver works great with the 64 bit server.

    Postgres SQL has a very powerful server side function  / join environment. You can create stored procedures you could only dream about with the ProvideX ODBC library.

  • 0 in reply to FormerMember

    Thank you John! I'll check that out. Sounds like it could be very useful

  • FormerMember
    0 FormerMember in reply to Kevin M

    Be forewarned that OLE DB can create some huge memory bound record sets if your tables have a lot of rows. I'm using the ODBC C library directly which gives me the addition of connection pooling.

    My understanding is member Modflander's goal is speed which he can obtain doing direct queries on the PSQL server rather than through an OLE automation link to the ProvideX data with OpenQuery. He is basically back at square one with Access.

  • 0 in reply to FormerMember

    That's exactly right. I came across PSQL when looking for an alternative to querying in Access, because a rather simple query with subquery took about three minutes to run. Thanks again, John. Your responses have been very helpful. I have Sage data now set up in PSQL server!

  • 0 in reply to FormerMember

    We use this technique in many places, adding filters where required, most commonly bringing data into Mirror temp tables in MS SQL to then do report processing from there.  For large transaction tables, filtering by date help, and I rarely have a query that runs more than a few seconds.

    I don't have anything against Postgres, but find it generally easier to get approval for installing Microsoft software on customer machines when everyone running Windows servers trust M$ software already. 

    Plus SQL Express is free and works fine.  Set up a silent DSN with linked server and query it just like any table.  Easy.

    select * from MAS_ABC64...SY_ActivityLog