How to link Sage 50 Accounting database to Microsoft SQL Server 2014?

I tried to make an ODBC connection to Sage 50 Accounting using the Pervasive driver. It is successful. When I tried to connect it to the SQL Server using the Linked Server, there are some errors. Wonder if there is another workaround to link Sage 50 to SQL?
JOYV23Asked:
Who is Participating?
 
Bill BachConnect With a Mentor PresidentCommented:
I believe that SQLServer 2014 is ONLY 64-bit.  Have you installed the 64-bit PSQL Client onto the SQLServer machine ALSO?  (You should install the 32-bit Client AND the 64-bit Client.)  Then, make sure that you set up a 64-bit ODBC DSN -- not a 32-bit.  This should work, then.
0
 
mirtheilCommented:
What errors?
Can you access the data through the Pervasive ODBC interface?
0
 
Bill BachPresidentCommented:
Many versions of SQL Server are 64-bit -- is yours?  If so, then you need a 64-bit ODBC Driver and 64-bit DSN.  This is ONLY available on PSQLv11 or newer.  Depending on your version of Sage, you may still have PSQLv10, which does NOT have 64-bit ODBC drivers.  In that case, you will need to use either 32-bit ODBC, or wait until Sage gets you up to PSQLv11 or PSQLv12.  

If you do have PSQLv11 already and a 64-bit SQL Server, install the 64-bit PSQL Client onto the SQLServer box, create a 64-bit DSN, and you should be working.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Bruce DenneySage 50 Consultant and IntegratorCommented:
Which Sage 50?  UK, US, CAN or rest of the world?
0
 
JOYV23Author Commented:
Here's what I did.

1. Open odbcad32.exe ( Created a data source name "BarrFab" under User DSN - selected the Pervasive ODBC Engine Interface driver )
- Supply password - test connection - successfull.

Version of Sage is Sage 50 Accounting 2015 US  and the Pervasive is PSQL 11

2. Open SQL Server Mngmt. Studio 2014 (32 bit) - Selected New Linked Server
Selected provider:  Microsoft OLE DB Provider for ODBC driver
Product Name: BarrFab
Data Source:  BarrFab
Leaved blank for Provider string, location and catalog.

Supply security password. After it gives me an error of :

TITLE: Microsoft SQL Server Management Studio
------------------------------

The linked server has been created but failed a connection test. Do you want to keep the linked server?

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "BARRFAB".
OLE DB provider "MSDASQL" for linked server "BARRFAB" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

------------------------------
BUTTONS:

&Yes
&No
------------------------------
0
 
JOYV23Author Commented:
Thank you BillBach  :)
That solves the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.