[savethepennies]

We are just starting into using ODBC with NetSuite and are having a heck of a time getting queries that work. I have looked through the training videos, which look old, and the user guide, which lists a few restrictions. Are there other resources I am not aware of that would help me understand the unique requirements of using the connector? What tools do others use for developing queries against the ODBC?

Here is an example of something I am trying to do:

select S.Name, Ten.OppCount as [Ten Count], Ten.OppTotal as [Ten Total]

from Sales_Reps S

left outer join (

select O.sales_rep_id as [RepID], count(1) as [OppCount], sum(projected_total) as [OppTotal]

from Opportunities O

where O.Probability = 10

group by O.Sales_rep_id

) Ten on Ten.RepID = S.sales_rep_id

As far as I can find this doesn’t break any rules and yet I get an error that says, “NLODBC Driver does not support role mixing.” And that means…?
This is a cached copy. Click here to see the original post.

[savethepennies]

So the thread has views but no responses. I guess others are wading through the same challenges. Has anyone been able to set up a linked server using the v6 driver?

[Tomas Dermisek]

Hi,

I would like to start using ODBC too – where can I find those training videos and the guides you mentioned?

Thanks. Tomas

[savethepennies]

The guide is available in the user guide section of the help. The videos are in the Self-Paced Training Videos section of SuiteAnswers, which you can get to through the Support tab. Once you’re there look in the Saved Searches/Reports category.

[ivan.parra]

RE: Resources for understanding NetSuite ODBC

“Mixed role” occurs when a query is done across 2 tables that exists on 2 different company/role. For example, if the odbc login has 2 roles, odbc will generally select the default login role. If a default does not exist, it chooses the first role. When table resolution occurs, ODBC will attempt the first role before attempting the other roles.

A few solutions, select a default role that you know has access to both tables. This is done through the NS UI. That should solve 90% of the issues.

Alternatively, you can fully qualify each table reference in the query using a company.role.table syntax. For example “Acme Company”.”My ODBC Role”.mycustomrecord

[savethepennies]

I’m very glad to see a response. I am the Administrator on our account and that is my default role so that should give me access to everything, based on your description. I will try qualifying the table references and see if I can get that to work.

Where are things like this documented? I have had several things cause errors that are standard syntax. For example, adding an alias to a returned field causes an error, starting an aliased name with a numeric causes an error, joining on a nested select.

[Marty Zigman]

RE: Resources for understanding NetSuite ODBC

I spent a fair amount of time trying to get ODBC connected to multiple companies via one account.

See my article here on it.

Connecting ODBC to Multiple NetSuite Accounts

http://blog.prolecto.com/2012/04/30/…uite-accounts/

Marty Zigman

Prolecto Resources, Inc.

http://blog.prolecto.com

[Author]

Posts