SAGE Line 100 ODBC driver (hmmm)

16 12 2009

OK I am not a SAGE professional, nor am I a SAGE developer, however, I would like to be able to access correct documentation online and actually speak to others / find out if something is correct…

The requirement

The requirement is simple, connect to a SAGE line 100 system via ODBC (DSN), then run a simple query on the data (stock information – read only of course) and then do something with that information (in my case call a secure web service and update it with the stock information). Simple, half a days task you would think…

It is a tad more complicated than that, as the requirement is for this to run as a windows service, scheduled in to run a number of times during the day. This in itself isn’t a problem, however checking up on error messages can be an issue – as you are reliant on interacting with the Event log (again nothing wrong with this. However you will see why this added to my problems)

The SAGE ODBC problems…

Ahh here we go. Well first off the SAGE ODBC drivers aren’t great (well not for SAGE 100). This caused no end of issues with just trying to connect to the database. There isn’t that much help online and to top it off, many discussion groups etc post questions but no one any real answers. My first problem was that the System DSN that had been set up didn’t appear to work. Using .NET 3.5 to connect to this just wasnt working. My event log was stating a number of issues, which when following the calling stack highlighted an “unsupported driver request”. With this in mind, I then adopted a DSN less connection approach, using a connection string.

My next problem isn’t really to do with SAGE that much, more lack of communications. You see the SAGE line 100 connection string isn’t your typical connection string, rather it needs pointers to particular files. I unfortunately had been given some slightly misleading information on the location of these files (though this may be more of a break down in communication than anything else).

At last I got connected. However, my next problem was that I couldn’t set the SQL command, nor more importantly execute the SQL. Again the SAGE driver was very very particular about its SQL and format, to the point that my SQL went through numerous iterations when to be frank, it was fine in the first place.

The big problem…ODBC read / fill

Everything was now finally moving along or so I thought, but then more problems. This time an unhandled error being raised in my code with the event log only giving me the following message “Event log messages can only be 32766 characters long”. Now thats quite an error message that wasnt getting displayed. I am very particular about my error handling in code so was surprised to see that I had an untrapped error. Anyway, I have spent many hours adding event log debug code after event log debug code and added error messages only to find none of them were getting called…

This meant I would start to post information messages to the event log stating where I was in the code (not great as this service will run forever and a day dealing with 1000s of items a number of times during a day). As I followed my event log I could see that my code was executing and behaving as expected almost all of the way through, then out of the blue an unhandled error. Refering back to my code to the location, it all seemed handled correctly. I have looked at this so hard that I even started to think maybe a timing issue was causing the problem…

However, no…The error is simple. All of my code works fine, but the SAGE line 100 drivers don’t. Why you ask. Well simple, I have read out all of the records placed them in my dataview and returned this to the calling function, all fine, but then there it is my un-handled error….Its great really, basically in my finally statement, all my ODBC objects are disposed and set to nothing. Alas, without any additional error handling around them. It appears that the ODBC reader once disconnected and disposed from SAGE (.dispose)  triggers an error message for every item that it has read, hence my event log unable to display me my message (maybe if it could, I would have had this solved earlier – but that’s not my point). This then caused a further error which meant my lovely windows service stopped working…

Something to remember

If you are using a SAGE Line 100 ODBC driver with .NET basically you need to trap every call and handle everything. This includes simple dispose and setting objects to nothing. The ODBC driver is ODBC 2 compliant so only use ODBC 2 compliant calls (this may mean some of the features you want to use from ODBC within .NET are unavailable). However, sticking to these and things should work fine(ish).  You can use the standard ODBC.open and then ODBCReader.read methods, but remember to trap all your code especially when destroying connections and objects….

About these ads

Actions

Information

12 responses

8 04 2010
Phil Smith

Hi Andrew, that is a great post and has given me some things to think about as I have just been talking to someone about uploading price list info from Sage Line 100 to a website. I have successfully managed this with Sage 50 Accounts however I HAD to use a DSN. Did you end up using a DSN or full connection string for your project sorry? It does seem that Sage is also some kind of secret society as well as software company because no-one lets their tricks out!

8 04 2010
Andrew Smith @onedegree

HI Phil,

Well we had no end of issues with the DSN connection, as in we couldnt get it to work at all. In the end, we opted for a full connection string which worked just fine (once you find out what that string should be). You are right, integration with SAGE is a well kept secret. I believe this is because SAGE want developers to join a little club and pay for the rights to gain the knowledge. I understand this for using an API and maybe extending SAGE or embedding it within another system, I get it, but for simple things such as exporting price lists or reading some data, there really should be more information on this and more importantly, some real world examples. Otherwise, what is the point of having an ODBC driver???

Glad you found the post helpfull…..

2 10 2013
Maha

Hi Andrew,

First of all, thanks a lot for this quite interesting article, especially with this lack of information regarding sage integration.
I am actually on my way to build my own application that shares the same intention as yours. So can you please provide me with some few extra details, like the structure of the connection string in instance.
I will be so gratefull.

2 10 2013
Andrew Smith @CloudZync

HI, sorry, I haven’t looked at this for a long long time. I will try to see if I have some code for this but off the top of my head, I don’t have a clue. I think you can set up an ODBC connection though and connect through that…

9 06 2010
Phil Smith

Hi Andrew,

I have been doing some more work for a client that uses Sage Line 100. I wrote a Console App a while ago that connected to Sage and copied data to an SQL database. It was working fine with a DSN connection. I am trying to do something similar now yet I am getting a variety of errors! If you have any advice on the connection string used to go direct to Sage I would be very appreciative!

Phil

9 06 2010
Andrew Smith @onedegree

HI Phil,

We basically you need to have the SAGE instance locally, or at least have mapped drives to it. This worked for us:

DRIVER={Sage Line 100};UID=userID;PWD=password;DIR=S:\SAGE\SVNDATA;SCH=S:\SAGE\SVNPROGS;LOG=S:\SAGE\SOVREIGN;

Remember you need a valid user id and password set up in sage itself…replace S and the location with whatever is correct for your setup. Hopefully this will solve your issue….

17 06 2010
Phil Smith

Hi Andrew,

Thanks for the advice. I have tried it, substituting the correct paths, however it is throwing a few errors, one of which is similar to the error from the System DSN I set up. “The driver doesn’t support the version of ODBC behavior that the application requested” however prior to the it’s giving me an invalid logon path, even though I got that from a DSN that’s working! Going in to see the client shortly to see if they can still connect from Excel!

17 06 2010
Andrew Smith @onedegree

Funny enough i am having a similar problem yet again. One of our applications has moved machines, thats all, and no longer on the same machine as the SAGE instance…Basically, nothing works yet again. I have the same errors as you have above. I would like to point out though that I have always had errors being thrown that state the driver does not support this ODBC command etc, and once the connection string was correct these errors just dissapear (I think the error handling / errors being raised are very poor in the driver)

It does appear that the driver does not support mapped drives correctly when being used from .NET. My DSN does work in Excel on the client but not through the .NET application we have…Hence using connection strings.

All in all, it is safe to say that SAGE LIne 100 drivers are very poor indeed. Luckily our client is moving to SAGE Line 200 and we can connect there with MS SQL drivers….thank the lord…

Good luck..

24 06 2010
Phil Smith

Hi Andrew,

I managed to solve my schoolboy error. The problem occurred because I got a little bit excited and decided that we should try for live Sage data to populate the web app. Went back to using a console app to retrieve the data and copy to SQL and the connection was working fine again.

I have managed to get a connection to a web app previously on Line 50 but that was using a web service on the server that Sage was installed on. That may help you until the migration to Sage 200 if it is an acceptable solution?

I suspect that the problem with Sage drivers is borne out of the permissions on the account running the application.

Cheers

Phil

24 06 2010
Andrew Smith @onedegree

Hi Phil,

I have come to the same conclusion. We have no issues when our app is running on the same server as SAGE, issues arrise once we are on a different machine trying to do the same things….

Once we move to SAGE 200 I do believe things will run a lot easier, simply because we can connect using MSSQL drivers (and they are compliant).

Glad all sorted for you…

17 09 2010
Andrew Smith @onedegree

Just to let any readers know. SAGE line 200 is soooooo much easier to deal with. Not because of its API or drivers or anything like that, simply because it is in MS SQL…Which means for simple extracts, you can set up a readonly user on a view in MS SQL and query that like you would any other MS SQL database…..

Thank the lord for that…..

16 07 2014
Josh Hallenbeck

Thank you for the article Andrew. I am running in to the same issues. Due to the advent of Win 8, connection to just query the database is a nightmare. To those of us who relied on the ease of use of ODBC without having to program a connection string was detrimental. I’m a bean counter, not a programmer. It would be nice if Sage could publish the connection string for their customers, but alas.

For us “laymen” who just report and occasionally need to populate we are at the mercy of manual reporting, which is horrible seeing how in previous versions of Win connections were a snap if you knew what to do. The tools I used to rely on for connectivity to data are now incredibly time consuming to research and test and there is no help.

Very, very frustrating.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 864 other followers

%d bloggers like this: