I’m a SQL newbie trying to access data from our company’s ERP system called Global Shop Solutions (GSS). This software tracks work orders and times using Actian Zen (formerly Pervasive) as its backend.
My goal is to query this database remotely so I can build a web app to view and manage the data. I’ve heard OPENQUERY might work once I set up a linked server but I’m not sure how to proceed.
Has anyone successfully connected SQL Server to GSS or a similar Actian Zen/Pervasive database? What steps should I take to get the data into SQL Server Management Studio?
I know it’s doable because I’ve seen a post about someone connecting GSS to SQL Server but I’m lost on the specifics. Any help or tips would be much appreciated!
hey there. i worked with pervasive before. try installing the odbc driver on your sql server and set up a linked server in ssms using the odbc data source. openquery should then work to pull data. hope that helps!
ooh, connecting different databases can be tricky! have u considered using a middleware solution? it might make things easier. what kind of data are u hoping to pull from gss? maybe we could brainstorm some alternative approaches if the direct connection proves challanging?
I’ve dealt with similar challenges before. The key is setting up the appropriate ODBC connection. Make sure to install the Actian Zen ODBC driver on your SQL Server machine and then create a System DSN through the ODBC Data Source Administrator. After that, configure a linked server in SQL Server Management Studio using the DSN you created, which will allow you to employ OPENQUERY to retrieve data from GSS.
Keep in mind that large data sets may affect performance. It may be beneficial to limit the data pulled or to implement a synchronization mechanism to improve efficiency. Additionally, verify that you have the needed permissions on both systems.