[ShaneMartin]

Trying to setup a SQL Server 2005 linked server to the NetCRM database using the ODBC driver.

I installed the SSL and ODBC components according to the NS instructions, I then created a linked server in a SQL Server 2005 database using the following syntax:

EXEC sp_addlinkedserver

@server=’NETCRM’,

@srvproduct=’NetSuite.com’,

@provider=’MSDASQL’,

@datasrc=’NetSuite.com’,

@provstr=’Database=NetSuite.com;Uid=xxxxxx@xxx.com ;Pwd=xxxxxx’

When I attempt to query small views its slow but works fine (example administrator.browser ~ 20 rows, takes 11 seconds to return a select *). However, whenever I attempt to query the transactions views the query runs for about 20 – 30 minutes then returns the following error:

OLE DB provider “MSDASQL” for linked server “netcrm” returned message “[Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed”.

OLE DB provider “MSDASQL” for linked server “netcrm” returned message “[OpenAccess][OpenRDA ODBC]Connection exception – Server lookup Failed – Database:NetSuite.com”.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “netcrm”.

After getting this error I cannot query any of the views, they all will return the same error message. To clear up the error I have to restart the SQL Server database engine, then I can query the small views again but still get the same message when trying to query the transactions views. I found that even if I do a “select top 1” query I still run into the same error. Here are two examples that timed out after 20+ minutes:

select top 1 * from netcrm.dbo.Administrator.TRANSACTIONS

select top 1 * from netcrm.dbo.Administrator.TRANSACTION_LINES

Has anyone else run into a similar issue? From what I’ve read in the usergroup other users have had performance issues with a SQL Server linked server. Unfortunately we are trying to automate some data comparisons between NetSuite data and some SQL Server data sources so Access/Excel aren’t really an option to view the NS data.
This is a cached copy. Click here to see the original post.

[ivan.parra]

RE: ODBC Driver error using SQL Server 2005 linked server

Sounds like the db connection is internally disconnecting from NetSuite, leaving the connection worthless and unusable. If configurable, try increasing the timeout to > 20 minutes.

Select top 1 * from transaction, while returns 1 row, still queries all the rows of the rows and does not gain any advantage over “select * from transactions”

There are various reasons why the performance for a simple table may take a long time, custom fields primarily.

[ShaneMartin]

RE: ODBC Driver error using SQL Server 2005 linked server

I increased the timeout for both of these settings in SQL Server–>

remote login timeout

remote query timeout

to an obscenely high amount (300 hours) but still got the same error message. Is there a timeout setting in the SSL or ODBC layer that I can modify? Could there be some other cause?

[ShaneMartin]

RE: ODBC Driver error using SQL Server 2005 linked server

FYI, NS Support has entered this as a defect.

[jswift]

RE: ODBC Driver error using SQL Server 2005 linked server

Has anyone successfully used Reporting Services with Netsuite to pull Balance and Income Statements?

Thanks

[Author]

Posts