Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

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

Sage 50 general help forum - Free help and support for all general issues
Post Reply
CJCHILLING
User
User
Posts: 3
Joined: 15 Jun 2020, 07:51
Sage Version: v2018 24 UK/Europe/Africa

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

Post by CJCHILLING » 15 Jun 2020, 08:02

Morning Bruce, is it possible to set up Sage 50 UK v26 as an ODBC linked server on MS SQL (2014)?
Christopher Hilling
________________________________________
SpaDental Group | 6 The Calvert Centre | Woodmancott | Hampshire | SO21 3BN
Main: 0845 539 0849 | Direct: 01962 798280

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4408
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

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

Post by brucedenney » 15 Jun 2020, 09:12

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.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

CJCHILLING
User
User
Posts: 3
Joined: 15 Jun 2020, 07:51
Sage Version: v2018 24 UK/Europe/Africa

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

Post by CJCHILLING » 15 Jun 2020, 09:17

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!)
Christopher Hilling
________________________________________
SpaDental Group | 6 The Calvert Centre | Woodmancott | Hampshire | SO21 3BN
Main: 0845 539 0849 | Direct: 01962 798280

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4408
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

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

Post by brucedenney » 15 Jun 2020, 09:26

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.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

CJCHILLING
User
User
Posts: 3
Joined: 15 Jun 2020, 07:51
Sage Version: v2018 24 UK/Europe/Africa

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

Post by CJCHILLING » 15 Jun 2020, 09:35

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
Christopher Hilling
________________________________________
SpaDental Group | 6 The Calvert Centre | Woodmancott | Hampshire | SO21 3BN
Main: 0845 539 0849 | Direct: 01962 798280

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4408
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

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

Post by brucedenney » 21 Jun 2020, 15:46

Is it looking for a DSN string ?

viewtopic.php?f=1&t=3657&p=10865&hilit=dsn#p10865
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4408
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

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

Post by brucedenney » 09 Aug 2021, 09:46

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.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests