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!

read/write version of ODBC driver

Sage 50 general help forum - Free help and support for all general issues
Post Reply
RubenHood
User
User
Posts: 2
Joined: 09 Feb 2021, 18:40
Sage Version: v2018 24 UK/Europe/Africa

read/write version of ODBC driver

Post by RubenHood » 09 Feb 2021, 18:57

Hi,
I have some data corruption on the database (sage 50 accounts uk). It was caused by an external tool we use to import sales orders. It screwed up discounts and tax.

My accounts' team can't be bothered to check data or take any ownership. In short, if it's plugged to the mains, it's an IT problem. So nobody noticed, nobody cared.

Fast forward years, the 2000+ warnings combined with the last update to v26 will turn into errors. Sage say the only way is to delete the transactions (including invoices, there you go people in touch with the reality of the product they sell). And Brexit wants v27. Talk about rock and hard place.

We use the ODBC driver extensively to read data and drive some reports, so I am familiar with the data structure. I would like to run some update queries to fix the inconsistencies.

I have been trying high and low to find an odbc driver, to no avail. I have found something from cdata, but it connects to the data service and is considerably slower than the ODBC driver (and that's saying something). Also, it won't allow me to correct sales order/invoice lines, only headings. Otherwise I need to delete said invoices.

What's the best way to approach this? Ideally I'd like to avoid having to consume an all new API via REST or similar where I'll have to spend a fair amount of time to achieve the same result as I would with a couple of update queries.

Thanks
R

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

Re: read/write version of ODBC driver

Post by brucedenney » 10 Feb 2021, 11:18

Hi Ruben

Sage is not online, it is not always available so a restful api is not going to happen. Sounds like a lot of work as well.

The ODBC drivers are read only, to write to the data you need third party import tools or to join the developer program (starts at about £4,000 per year)

If you want to get data into sage, I have some third party tools I sell that can enable this.

I also offer a data fix service, it is no-fix no-fee, so why not give me a try. It is unclear from your description what sort of errors you get, Sage may say it is unfixable, they may say, you need to delete them, but that is not always the case.
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

RubenHood
User
User
Posts: 2
Joined: 09 Feb 2021, 18:40
Sage Version: v2018 24 UK/Europe/Africa

Re: read/write version of ODBC driver

Post by RubenHood » 10 Feb 2021, 12:28

Hi Bruce,

thanks for your reply.

we use a tool to import sales orders from e-stores. Some of these are internal to clients, so the products are sold at a "points" value, but when these go into sage, the value needs to be negated. The cost of shipping remains (the stock belongs to the client, the shipping is accrued and charged month in arrears).

For a space of time, the mechanism we used for this was to apply a 100% discount to sales orders on items. The import tool was not working properly, so it applied the discount to the net price, not to the tax portion.
The total tax on the sales order (and subsequent invoice) is zero, that meant it took a long while to discover the issue.

So now we have a bunch of orders with tax on the lines, but not on the total. This would appear as a warning, but now has been reclassed as error (as of the last v26 update).

The data fix is simple. I can successfully run a query on orders and invoices and isolate all these with a discount on order items, but a tax value being non-zero. In any application where I had access to data, I would execute a simple update query and fix it in 3 minutes flat. On sage I would need to go one order at a time correcting the entries. There's 1000 of them. Not having a write-enabled odbc driver is frustrating.

Entering the developers programme is not an option. Sage is a legacy application that has been on departure lounge for years.

I am trying a little experiment for now, if it doesn't pan out I will reach out for the data fix service.

Thanks again
R

SomeGuy
User
User
Posts: 15
Joined: 20 Jul 2020, 06:14
Sage Version: Other

Re: read/write version of ODBC driver

Post by SomeGuy » 11 Feb 2021, 07:43

brucedenney wrote:
10 Feb 2021, 11:18
Sage is not online, it is not always available so a restful api is not going to happen. Sounds like a lot of work as well.
You don't have to be online to have a restful API. Sage has support for Sdata baked in to it and this works locally - https://sage.github.io/SData-2.0/pages/core/0100/. From what I've seen it's fairly limited though, so probably wouldn't help here anyway and a lot of work in any event as you say.
RubenHood wrote:
10 Feb 2021, 12:28
Not having a write-enabled odbc driver is frustrating.
I was reading a post on Sage's own support forum the other day where they were saying that they don't even support reading via the ODBC driver and that it only exists to run reports - https://my.sage.co.uk/forums/default.as ... ts&m=29330
RubenHood wrote:
10 Feb 2021, 12:28
I am trying a little experiment for now, if it doesn't pan out I will reach out for the data fix service.
Good luck. If it doesn't pan out I'm sure Bruce can help you.

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

Re: read/write version of ODBC driver

Post by brucedenney » 11 Feb 2021, 10:50

One of the daftest things about the ODBC driver is that you can specify the delimiter.

Sage chose the comma, which makes things like SQL Queries ambiguous in some contexts. Hence the not officially supported.

I am not sure how Sdata would return any result if Sage was in administrative mode doing check data or something like that. Opening the port on your network and allowing the internet direct access to your accounting system might not be a good idea, but we all know the internet drops in and out, so it would be a pretty unreliable solution even if theoretically possible to do.
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

Onion RS
User
User
Posts: 5
Joined: 13 Feb 2020, 10:49
Sage Version: v2018 24 UK/Europe/Africa

Re: read/write version of ODBC driver

Post by Onion RS » 11 Feb 2021, 17:42

SomeGuy wrote:
11 Feb 2021, 07:43

I was reading a post on Sage's own support forum the other day where they were saying that they don't even support reading via the ODBC driver ...
Eeek!!

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

Re: read/write version of ODBC driver

Post by brucedenney » 12 Feb 2021, 11:15

I think this is due to the use of a comma as a delimiter resulting in ambiguous SQL, this is why the data pilot in open office does not work.
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

Onion RS
User
User
Posts: 5
Joined: 13 Feb 2020, 10:49
Sage Version: v2018 24 UK/Europe/Africa

Re: read/write version of ODBC driver

Post by Onion RS » 12 Feb 2021, 12:05

Hi Bruce,

It's just a little concerning when our product line relies on the reliable operation of the ODBC drivers! Maybe we are geniuses at SQL (without knowing it) or lucky that we haven't had any issues?

I know it's a bit off topic (and a bit cheeky on my part - sorry) but I'm intrigued by what you mean by "ambiguous SQL". Would you have any simple examples in mind that would illuminate the issue? Have you ever come across it "in the wild"?

Great forum. Keep up the good work and keep safe.

Kind regards,

Ian

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

Re: read/write version of ODBC driver

Post by brucedenney » 12 Feb 2021, 12:15

I think you use Excel and it is compatible.
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

Onion RS
User
User
Posts: 5
Joined: 13 Feb 2020, 10:49
Sage Version: v2018 24 UK/Europe/Africa

Re: read/write version of ODBC driver

Post by Onion RS » 12 Feb 2021, 12:21

brucedenney wrote:
12 Feb 2021, 12:15
I think you use Excel and it is compatible.
Phew! Many thanks.

SomeGuy
User
User
Posts: 15
Joined: 20 Jul 2020, 06:14
Sage Version: Other

Re: read/write version of ODBC driver

Post by SomeGuy » 15 Feb 2021, 07:57

brucedenney wrote:
11 Feb 2021, 10:50
One of the daftest things about the ODBC driver is that you can specify the delimiter.

Sage chose the comma, which makes things like SQL Queries ambiguous in some contexts. Hence the not officially supported.
Can't believe I find myself defending Sage here but you can't specify the delimiter character in any ODBC driver as far as I'm aware and the comma is used as the delimiter in pretty much all ODBC drivers out there. In fact I'm not aware of any that don't use a comma. SQL Server, Oracle, Access, MySQL, etc - they all use a comma as the delimiter.

What is non-standard with Sage is their choice of qualifier character as they use an apostrophe whereas most other ODBC drivers use a quotation mark. However it is part of the ODBC standard that you can use any character you want but that a consumer can detect the qualifier character by interrogating the driver. Sage do return the apostrophe character as their qualifier so it is adhering to the standard. Open office is being slightly lazy in that it is assuming that because most drivers use a quotation mark as the delimiter that they all do, and does not perform any checks to see what the actual delimiter character is.

Wow, defending Sage twice in a single post! I'd better check what's in this water!
brucedenney wrote:
11 Feb 2021, 10:50
I am not sure how Sdata would return any result if Sage was in administrative mode doing check data or something like that. Opening the port on your network and allowing the internet direct access to your accounting system might not be a good idea, but we all know the internet drops in and out, so it would be a pretty unreliable solution even if theoretically possible to do.
To be honest I've never tried querying Sdata whilst a check data operation was happening so not entirely sure. Having said that I've never tried querying the ODBC driver when a check data was happening either so I'd guess the situation would be the same.

Using Sdata (or RESTful services in general) does not inherently mean opening up your network to the Internet. If all you need is access on the local network then you can do this without needing to expose anything externally. Obviously it still requires local network access but then so does the ODBC extract route, or SDO (Sage's software development kit). Of course if you lost local network access then you've probably issues than the Sync with Sage not working :)

All a bit moot though as I think we both agree that Sdata is probably not the way to go here in any event as it would be a lot more work.

Post Reply

Who is online

Users browsing this forum: No registered users and 5 guests