David Haycox
asked on
ODBC connection error after upgrading to Sage 2012
We have an ASP application that pulls data from Sage 50 Accounts for display on web page. We recently upgraded from Sage v15 (2009) to v18 (2012). After setting up the System DSN in ODBC with the new Sage driver, we now get the error as listed in the image.
Although it states "User ID or Password invalid" that is not the case - it's definitely correct.
Could the section "The driver doesn't support the version of ODBC behavior that the application requested" be more relevant? Sage must have changed the way their ODBC driver works, but they don't support it.
Sage 2012 is patched with updates 1,2,3 and the ODBC driver update.
Any assistance or advice would be much appreciated.
Sage-ODBC-error.JPG
Although it states "User ID or Password invalid" that is not the case - it's definitely correct.
Could the section "The driver doesn't support the version of ODBC behavior that the application requested" be more relevant? Sage must have changed the way their ODBC driver works, but they don't support it.
Sage 2012 is patched with updates 1,2,3 and the ODBC driver update.
Any assistance or advice would be much appreciated.
Sage-ODBC-error.JPG
When you update Sage, make sure you have installed the ODBC drivers on the webserver. They are not compatible with different versions. We have similar issues with the new driver.
To overcome this we created a new System DSN, using the new Sage Driver. From memory you have to enter the directory that the Sage data is stored in - this is the ACCDATA folder.
From my ASP app I then reference the following:
Obviously not my real username and password! The solved the problem for me.
To overcome this we created a new System DSN, using the new Sage Driver. From memory you have to enter the directory that the Sage data is stored in - this is the ACCDATA folder.
From my ASP app I then reference the following:
Set SageConn = Server.CreateObject("ADODB.Connection")
strCnn = "DSN=mySagev18; Uid=User; Pwd=Password; UseDataPath=No"
Obviously not my real username and password! The solved the problem for me.
ASKER
Sorry, I did mention that we already installed that...
You need to install the ODBC V 2012 hotfix, This is not pushed out on the Automatic updates, you have to do it manually.
www.sage.co.uk/accountsupdates
it is in the Misc section
www.sage.co.uk/accountsupdates
it is in the Misc section
Can you check the version of C:\WINDOWS\system32\S18DBC 32.dll
ASKER
It's 18.1.0.8
Currently looking into JEaston's answer, watch this space.
Currently looking into JEaston's answer, watch this space.
Setting up a DSN is a good idea as when they change in future, you can change the DSN once and not all your apps spreadsheets, databases and so on that use the ODBC driver.
In this case though it looks like you are already using the V18.1 driver.
So you would have to have forgotten to code the data path and relied on the default data path the ODBC driver used. It may well be pointing at the demo or practice data, try username MANAGER and no password and see what you connect to.
In this case though it looks like you are already using the V18.1 driver.
So you would have to have forgotten to code the data path and relied on the default data path the ODBC driver used. It may well be pointing at the demo or practice data, try username MANAGER and no password and see what you connect to.
ASKER
No joy so far, we were using the same syntax apart from "UseDataPath=No", but adding that makes no difference.
I also tried pointing at the demodata; again I get the same error regardless of whether I use correct credentials or not.
Any other ideas?
I also tried pointing at the demodata; again I get the same error regardless of whether I use correct credentials or not.
Any other ideas?
The other issue I came across when doing this was to do with user permissions. However I believe this was an issue for both old and new versions. However, if you data is located on another server we have issues with NT authentication.
To resolve this we had to add an ImpersonateUser object as detailed below.
If your data is on the same server, and hasn't moved, this is not likely to be the issue. However, just in case I thought I would post it.
To resolve this we had to add an ImpersonateUser object as detailed below.
Set objLogon = Server.CreateObject("LoginAdmin.ImpersonateUser")
objLogon.Logon "AdminUser", "AdminPassword", "Domain"
session.lcid=2057
If your data is on the same server, and hasn't moved, this is not likely to be the issue. However, just in case I thought I would post it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hmmn, when you upgraded the installation on the server it should have upgraded .net so seems strange.
ASKER
Thanks for the tips. Answered question myself in the end though.
www.sage.co.uk/accountsupdates
it is in the Misc section