read/write version of ODBC driver

[RubenHood]

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

[brucedenney]

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.

[RubenHood]

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]

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.

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

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.

[brucedenney]

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.

[Onion RS]

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!!

[brucedenney]

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.

[Onion RS]

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

[brucedenney]

I think you use Excel and it is compatible.

[Onion RS]

I think you use Excel and it is compatible.

Phew! Many thanks.

[SomeGuy]

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!

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.