Sage ODBC Driver's SQLSetConnectAttr failed

[billshankley]

Hello,

I have installed the Sage v19 (2013) on a test server and i'm having some issues connecting a client via ODBC. When creating a connection from Excel I can use the wizard but when returning records back to the spreadsheet I get the three below errors one after the other.

1. User ID or Password Invalid
2. [Micorsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
3. The driver returned invalid (or failed to return) SQL_DRIVER_VER: 19.0

Despite this the connection credentials appear to work because when accessing the ODBC via Microsoft Query I can execute queries and return record sets but when trying to return the record set to Excel I get the above errors.

My server is running Windows Server 2008R2 64bit and my client pc is running XP SP3 32bit.

Has anyone had any experience of this or know of what might cause such an issue?

Thanks,m

[brucedenney]

What sort of DSN are you using to connect?

Typically the drivers get confused about which set of data you are looking at and only by creating a separate dsn can you be clear about the path to the correct data set.

Is the path to the data using mapped drive or a UNC path?

[billshankley]

Currently using a UNC path (\\SAGESERV\company.000\ACCDATA\), i've tried setting up both user and system DSN in the ODBC Data Source Administrator with no luck.

I can create queries and return them in MSQuery but when clicking "Return Data to Microsoft Office Excel" and selecting where to place the data I get those errors.

[brucedenney]

Can you post the SQL statement please.

[billshankley]

I'm just trying to get it working so using a simple select statement as shown below.

Code: Select all

SELECT * FROM COMPANY

[brucedenney]

I am unable to reproduce this error.

However, I think I might have an idea as to what might be causing the issue.

I think that there may be a field that is being returned with a different data type to that the odbc is reporting, this could be the odbc or it could be damaged data.

To see if this is the case, start by running your query against a known good set of data in this case I suggest you use the local demo data on the workstation.

XP or earlier
C:\Documents and Settings\All Users\Application Data\Sage\Accounts\2013\Demodata\ACCDATA

Later Windows
C:\Programdata\Sage\Accounts\2013\Demodata\ACCDATA

Assuming this works, try running a more simple query for just a couple of fields add fields until you find out if ther eis one that is causing the failure.

Let me know what happens.

[billshankley]

Bruce thanks, I plugged away for a good couple of hours trying connections to demo data etc and checked everything to no avail.

In the end I uninstalled Excel 2003 and installed a copy of Excel 2010 that I had laying around and it worked as expected without any changes to the ODBC, a workaround and not a solution unfortunately.

I can only imagine there is an issue with the version of MS Query packaged with Excel 2003 and the latest version of Sage, either that or my installation was corrupted somehow.

I will test this when I have time to see whether I can reproduce the error and post back with my findings.

Thanks for all your help.