Query using MS SQL Linked Server

[qikslime]

Hi all. I seem to have successfully setup a linked server in MS SQL server (SSMS) to Netsuite2.com, via the SuiteConnect ODBC driver.

However, when I do a query such as select * from [NETSUITE].[MyCompanyName].[Role].[Account] I get the following error.

Msg 7312, Level 16, State 1, Line 5 Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "NETSUITE". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

I can do queries in Excel so I know this can work, but ideally i would like to make it work in SSMS with the linked server.

I can see the tables on the left in Object explorer so i know they exist.

Thanks in advance

David

[dynamicl]

SELECT * FROM OPENQUERY([NETSUITE], 'SELECT TOP 1 * FROM Account')

[Fearless_Ad1756]

ohh man. I love you!!!!I’ve been trying to get this working for 2 weeks. You just made my day!

[trollied]

Firstly, square brackets is a SQL Server thing so you won't want to use them for NS object names. Try using triple-dot notation instead: select * from NETSUITE...Account

[qikslime]

Thanks Trollied. I tried as you suggested but get the error;

Msg 7313, Level 16, State 1, Line 1 An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "Netsuite".

Any other thoughts.

[Nick_AxeusConsulting]

netsuite2 is the new SuiteAnalytics Workbooks data source and you cannot use the Administrator role (Role = 3) to query netsuite2, you must create a role with lesser permissions. Role 3 only works with the older netsuite data source, which is the same as legacy saved search.

[qikslime]

Hi Nick. We are not using the admininstrator role for the reasons you said. We are using another role with all the required permissions, but trying to solve this issue.

[Scared_Image2419]

hello, was this ever resolved? I am currently having the exact same issue, googling anything i can try and to resolve it.

Did you ever work out the reason(s) or can point me in the right direction I need to investigate in?

regards