[galaxy]

After months of debate, we just added the odbc (“Enterprise Reporting”) module and can’t get the driver to do even basic things with Excel.

1. The setup doesn’t match the documentation updated 9/24/07.

a) the doc’s refer to a link to install SSL, but the link is not there

Is SSL no longer required?

b) the doc’s say to open port 1706 in our firewall, but the driver uses 1707

Which is it, 1706 or 1707?

2. When I briefing had it working, it was only working partially.

a) creating a Excel external data definition, I built a simple query to list customers, and I could view the results within the msquery tool, but when selecting the result to be returned to Excel – I get these errors:

[SimbaLNA][unixODBC]Unable to EstConn

Driver’s SQLSetConnect Attr Failed

3. After attempting to adjust the firewall, uninstall and reinstall the driver, now nothing works. I get the error:

There are no tables available for the data source

What am I missing here? This should be so simple, but it’s not.

Galaxy
This is a cached copy. Click here to see the original post.

[Former UG member – changed by Mod 2784]

RE: ODBC setup & service doesn’t work as advertised

Have you called support to troubleshoot your connection?

[galaxy]

RE: ODBC setup & service doesn’t work as advertised

Thanks Angela,

As it turns out there are several issues.

The first is I didn’t check for more current documentation, because when the last update was applied, new doc’s for ODBC were also introduced which changed – the driver, the SSL link, and the firewall port (from 1706 to 1707).

Second, the driver works with Visual Basic for Excel but not for the built-in Excel Query builder (at least not completely). So trying to access the standard feature of “get external data” does not work, at least with my version of Excel 2003.

But, the same configuration works with Excel VBA. Go figure.

The part that’s broken is the connection between Query Builder and populating the spreadsheet, because I could see the dataset come back to the Query builder by choosing to see the result before sending the data to the spreadsheet, but it would barf all over itself if I send the data in one step or two steps.

Has anyone tested versions of Excel and standard built-in tools to give us an indication of what works and what does not?

Galaxy

[smudgiebottom]

For us, we occasionally need to click on the Options button in Excel when adding the Netsuite.com connection to a workbook and toggle the System Tables check box. I don’t know why it sometimes shows the tables, and sometimes shows none, and sometimes just shows 10 admin-type tables. We just change the ‘Show System Tables’ and try again and it works fine. That’s in Excel 2003 and Excel 2007.

Otherwise, it works fine – just a bit slow to refresh all data (use the filters in the Query Builder and that decreases the amount of data coming down, hence makes it quicker to refresh), and may need a few password entries.

Try that anyway…

Matt

[mhuffman]

Hi Galaxy,

In the last week, we released a new version of the ODBC driver (see my post in https://usergroup.netsuite.com/users…ad.php?p=47681 for more details.) As you noticed in the new version of the help doc, the separate Open SSL installation is no longer necessary and the port has changed.

I would like for us to look into your problem with returning results in Excel in more detail; please open a case with customer support describing the circumstances as you did here, and exactly which views/fields you are trying to return, and we will investigate further.

[galaxy]

RE: ODBC setup & service doesn’t work as advertised

I have opened a case for the ODBC driver issue with Excel 2003 Database Query, case #647952.

By the way, the system option checkbox does not make any difference, but thanks for the suggestion.

Galaxy

[mhuffman]

Galaxy,

I looked at the error mentioned in your case and it sounds like you were connecting when the server was being restarted. This was an issue we were having last week during the beta test, but the system has since been stabilized. Are you still getting the same error message consistently when you go to login?

[galaxy]

RE: ODBC setup & service doesn’t work as advertised

Yes, I still see the problem consistently using Excel Database Query, which is the primary built-in method of retrieving external data from ODBC/DSN data sources.

If it worked, I should be able to build MSQueries and save them for future use within a spreadsheet, including the ability to join multiple tables into a result view that the query results are returned to the spreadsheet.

What is working is building the same QueryTable using VBA code, so I am looking at this approach as a work around, but it is entirely code-based and not adhoc like the query wizard provides.

I have not tested Excel alternatives to QueryTables, which are ADO, DAO w/ODBCdirect, and VBA.NET, but these have their own object classes and the syntax is different and difficult to validate they work. Maybe someone has examples they will share, because I have searched for several days and can not find good ADO examples using ODBC data sources.

On the plus side, I did get a simple Ruby and ODBC connection working, so that’s a more promising option I am investigating, with hopes to move it to the Ruby on Rails framework and leverage it’s ActiveRecord functionality.

My first thought was getting some simple reporting solutions working quickly using Excel Query Wizard, with the long term plan of building both ODBC and Web Services connections for read and write operations. The web services interfaces are not easy to work with, while odbc provides a fairly easy way to get at the data.

Ruby on Rails using both ODBC and Web Services access seems to be that long term answer. So I seem to be working on the long term solution sooner than planned.

By the way, here’s the entire program to access Netsuite/ODBC with Ruby (it needed a User DSN, which I called ‘Netsuite’, plus username & password):

————

require ‘dbi’

DBI.connect(‘dbi:ODBC:Netsuite’, ‘username’, ‘password’) do | dbh |

dbh.select_all(‘select * from customers’) do | row |

p row

end

end

————

The ‘P row’ statement is printing each row that is received from the select statement, so it could just as easily be processing each row.

A Ruby on Rails version will be a simple connect, and then leverage the ActiveRecord functionality. At least that’s the theory. It doesn’t get much easier than this, and it is much easier than VBA, Java, .NET, or Javascript syntax.

I hope Netsuite adds a lot more support AND examples for Ruby on Rails in the future, because it would be a great tool for building Web 2.0 front end apps with Netsuite.

In the meanwhile, using the standard Excel features would be nice.

Galaxy

[longlam]

RE: ODBC setup & service doesn’t work as advertised

I would be a little hesitant to go for it all using just Ruby since if you are thinking about doing web services Ruby soap classes may not be sophisticated enough to handle the generation of soap messages to send to Netsuite correctly. I didn’t have such a great time hacking into php with nusoap to get things working as much as I needed and you will also find that many developers on the user forum have also run into this wall and eventually migrated everything into java. Although it took a much longer time to deploy, you will find the combination of jdbc/odbc and web services in java to be much more powerful.

[Author]

Posts