Warning: SQL Server Linked Server to NetSuite Issues

Disappointment has set in. ¬†After working on a project to move financial data from NetSuite to a local data warehouse, we discovered that there is a bug using SQL Server’s Linked Server technology through the NetSuite ODBC ¬†driver.

What’s important to note is that it may not be obvious there’s a bug. ¬†For the most part, everything appears to work. ¬†You can connect and perform queries without complaint. ¬†However, we discovered something that looked like a data integrity problem. ¬†It appeared that we had accounts within the posting_account_activity table that were not in the accounts table. ¬†Yet, if we performed a native ODBC query, we would get the expected result: all transactions accounts were referenced in the accounts table.

Upon a fair amount of head scratching, we discovered that there is a bug in the ODBC driver to Oracle.  See this article:  http://itknowledgeexchange.techtarget.com/itanswers/linked-server-query-returns-incomplete-results/

Turns out that Microsoft suggests that the SQL Server community connect to Oracle with Oracle drivers, not the ODBC drivers. ¬†That doesn’t help us because the drivers to connect with NetSuite are using DataDirect’s Open Access drivers.

At this point, we are devising a new solution to the way we retrieve NetSuite data which will likely require a custom SQL Server Integration Services package to pull down down using the ODBC driver direct.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to setup a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - Google Plus - YouTube

| Tags: , , , , , | Category: NetSuite, Technical | 20 Comments

18 Comments

  1. Scott Nei
    Posted March 7, 2013 at 1:46 pm | Permalink

    How has your new solution worked out? Any heads up insights to using SSIS to access NetSuite data?

  2. Posted March 8, 2013 at 7:18 pm | Permalink

    Hi Scott,

    The challenges are in the ODBC driver. I learned from DataDirect that NetSuite is solely responsible for the driver and any issues (bugs) need to be resolved by their team.

    Here is how I am seeing it at this point in time:
    1. Large downloads are likely going to be challenging to implement. Think trial-and-error. That may be acceptable in the initial data load.
    2. Incremental downloads wil likely be okay if you keep the data set small.
    3. Be sure to prepare for exceptions and plan to put energy in monitoring and resolution it until your reliability is proven.

    What kind of project are you working on?

    Marty

  3. Scott Nei
    Posted March 15, 2013 at 6:15 am | Permalink

    Our overall goal is to be able to build better reporting with the data.

    Right now we are going down the road of pulling the data using the API instead of a DB connection. The toughest part about that right now is that the data is so highly normalized, and it doesn’t come down with explicit relationships.

    I’ve considered just getting the NetSuite ODBC driver and using it just to quickly explore all the data and relationships, purely as a resource in our efforts to get the data through the API.

  4. Posted March 15, 2013 at 7:32 am | Permalink

    Scott,

    The Web Services API has some limitations if you need to produce financial statements. Most, but not all, of the data is there. The ODBC connection generally is much easier for reporting and less expensive than rolling your own extraction layer.

    A number of companies are making offers to get that data out via Web Services typically for migrating to other platforms and less for for reporting. Check out Explore Consulting, Sesame Software and Connection Cloud.

    Let us know how it goes and if we can help,

    Marty

  5. Scott Nei
    Posted March 15, 2013 at 12:05 pm | Permalink

    Thanks Marty. Any data off the top of your head that isn’t available through the API?

  6. Posted March 15, 2013 at 1:17 pm | Permalink

    Hi Scott,

    Yes, read this article I wrote. I think it will provide the guidance you need:

    http://blog.prolecto.com/2013/01/24/considering-producing-netsuite-financial-statements-using-third-party-tools/

    If you need help, I have consultants that are expert in both NetSuite and database technologies.

    Marty

  7. Ziad
    Posted March 21, 2013 at 10:37 am | Permalink

    Hey guys,

    I have downloaded and setup the NetSuite ODBC driver. currently working out some firewall issues to be able to communicated with the netsuite server on port 1708. But my question is, after I have this all setup how do extract the data from NetSuite into my oracle database? Is there some kind of tool that I need to install to be able to write SQL queries?

    Thanks for your help

    Best Regards,

  8. Posted March 21, 2013 at 10:49 am | Permalink

    Hi Ziad,

    After you get the driver properly setup, the NetSuite database can be queried with any tool that knows how to speak ODBC. For example, SQL Server Integration Services (SSIS) has an array of tools that allow you to connect to tables / queries and move the data to other databases. The world is rich with these types of tools. For example, Tableau (http://www.tableausoftware.com/) has a set of tools to help extract out data as well. You can also write you own with .NET.

    Marty

  9. Ziad
    Posted March 22, 2013 at 6:10 am | Permalink

    Marty,

    Thanks for the very helpful feedback. I finally have this setup but when I connect I get the message “This data source contains no visible tables”. Is there a switch or a configuration that needs to be done on NetSuite to expose the objects/tables?

    Best Regards,

  10. Ziad
    Posted March 22, 2013 at 6:37 am | Permalink

    Mart,

    Never mind I figured it out. There is an option to display system tables.

    Best Regards,

    Ziad

  11. Josh
    Posted January 8, 2014 at 11:10 am | Permalink

    Is this still an issue? We’ve just started to move to NetSuite. We have a small Netsuite implementation with which we are using the api to pull data for our Datawarehouse and other intergration points. We are now moving to a full blown implementation and the odbc driver looked interesting until I found this post.

  12. Posted January 8, 2014 at 3:41 pm | Permalink

    Hi Josh,

    I recommend you move with a posture of caution. Since your database is presumably small, I don’t think you will have much issues. I recommend keeping things as simple as possible and avoid in use of extra functions on the SQL calls.

    I also have connections to other parties that are doing a good job for others pulling down the entire NetSuite database in an ongoing fashion including two way sync. Be sure to read this article:

    http://blog.prolecto.com/2012/08/31/extract-netsuite-data-for-backup-and-reporting/

  13. Eddy Dhakal
    Posted July 15, 2014 at 1:24 pm | Permalink

    Has this ODBC issue been resolved? Anyone aware of this?

  14. Dustin Wells
    Posted February 18, 2015 at 2:01 pm | Permalink

    Has this ODBC issue been resolved? Anyone aware of this?

  15. Tom Dickson
    Posted October 5, 2017 at 2:24 am | Permalink

    Hiya, I’ve got a very odd issues that you may have come across and know a work around.
    I use SSIS and the NetSuite ODBC driver to copy tables to a local SQL server.

    No real problems, Transaction Lines is 1.5m records and takes 10 minutes. That’s 3 years of business.

    My main problem is that I can’t see any of the data in the ACCOUNTS tables, and can’t see any Supplier Invoice transactions. My admin says I’m set up for everything, but it’s either bad permissionning or a bug.

    Anyone seen that?

    Thanks for your selfless help.

  16. Posted October 8, 2017 at 6:07 am | Permalink

    Tom,

    Are you looking at the transaction_lines table? That’s where the supplier invoice is. The rest may have to do with permissions as NetSuite honors the hiding of data based on role?

    Marty

  17. Tom Dickson
    Posted October 13, 2017 at 11:43 pm | Permalink

    Hiya, it was indeed permissions issues. I needed to be set up with access to all the ledgers in order to be able to see Supplier Invoices in Transactions / Transaction Lines via ODBC access. Now done, it’s very powerful to set up proper reporting outside the walled garden of the UI.

  18. Posted October 14, 2017 at 2:26 pm | Permalink

    That’s great news! Thank you for the follow up.

2 Trackbacks

  1. […] Warning: SQL Server Linked Server to NetSuite Issues […]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>