Page 1 of 1

Linking MS SQL to Sage 50 v26 (UK) with ODBC

Posted: 15 Jun 2020, 08:02
by CJCHILLING
Morning Bruce, is it possible to set up Sage 50 UK v26 as an ODBC linked server on MS SQL (2014)?

Re: Linking MS SQL to Sage 50 v26 (UK) with ODBC

Posted: 15 Jun 2020, 09:12
by brucedenney
You can read the data through odbc, it is however slow.

My suggestion if you are looking for performance is to copy the tables from sage using odbc into native tables in whatever product you are using.

Re: Linking MS SQL to Sage 50 v26 (UK) with ODBC

Posted: 15 Jun 2020, 09:17
by CJCHILLING
I was wanting to setup up a Linked SQL database to extract the data and sync to a SQL database so that I could extract the data rather faster than the ODBC link! Is it possible to directly link, using the Sage 50 v26 UK ODBC driver from MS SQL (2014), I am getting errors. Therefore wanted to check if it was possible before trying to debug the errors. (I know it was not possible years ago not tried for the last 10 years!)

Re: Linking MS SQL to Sage 50 v26 (UK) with ODBC

Posted: 15 Jun 2020, 09:26
by brucedenney
It has ALWAYS been possible to read the data out of sage via ODBC.

There are some fields that can cause issues as you need to get the data types right when copying form one to the other.

You cant copy a blob field into an integer.

There are a few fields that cause some tools to crash as well.

I suggest starting with just the main key fields and slowly adding them.

Re: Linking MS SQL to Sage 50 v26 (UK) with ODBC

Posted: 15 Jun 2020, 09:35
by CJCHILLING
Hi Bruce, I have not got that far. I am still having problems with setting up the Linked Server ...

Image
https://prnt.sc/szysxo

What would expect to find in the:
- Provider string
- Location

Re: Linking MS SQL to Sage 50 v26 (UK) with ODBC

Posted: 21 Jun 2020, 15:46
by brucedenney
Is it looking for a DSN string ?

viewtopic.php?f=1&t=3657&p=10865&hilit=dsn#p10865

Re: Linking MS SQL to Sage 50 v26 (UK) with ODBC

Posted: 09 Aug 2021, 09:46
by brucedenney
Yes and no.

You can link to have a copy of read only data, you can not write data back to sage this way.

If you want to try it do not link the whole tables, link individual fields because there are 1 or 2 fields that have issues with their data types and don't link properly and have to be skipped.

There is not much point in linking tables, the Sage tables are very slow to read through odbc, you might as well use odbc instead of ms sql.

A better strategy is to update the tables are regular intervals and then use the tables, this way you have near up to date info and you get the performance of SQL.