Monday, June 10, 2019

NetSuite ODBC as SQL Server Management Studio Linked Server

SQL Management Studio can be used to access NetSuite via ODBC interface. This can be achieved by configuring NetSuite as "Lined Server".

Note: This article assumes that the SQL Management Studio has been already installed and new linked servers can be added.

Following are all needed information in detail:
1. Install 64-bit ODBC driver. To do so please consult 64-Bit ODBC Driver for Windows Setup Instruction
2. Create System DSN:
2.1 Go to Start > Control Panel > Administrative Tools > Data Sources (ODBC)
2.2 Choose "System DSN" card
2.3 Click Add
2.4 Fill following details:

Data Source Name: NetSuite.com Production x64
Service Host: odbcserver.na1.netsuite.com
Service Port: 1708
Service Data Source: NetSuite.com

2.5 Go to "Security" tab
2.6 Tick "Encrypted (SSL)"
2.7 Click OK
3. Open SQL Server Management Studio
4. On the left menu with name "Object Explorer" go to Server Objects > Linked Servers
5. Click with right mouse button on "Linked Servers"
6. Choose "New Linked Server"
7. Fill data on General tab (left menu):

Provider: Microsoft OLE DB Provider for ODBC Drivers
Linked server: NETSUITE
Data source: NetSuite.com Production x64
Provider string: DSN=NetSuite.com

8. Go to Security tab (left menu):
9. Tick "Be made using this security context"
10. Put your NetSuite remote login and password
11. Click OK

Now NETSUITE Linked Server represents NetSuite ODBC connection. All accounts that the customer has access to are listed.

To be able to query tables:
1. Go to Linked server "NETSUITE" (the one just created)
2. Expand Catalogs > Account you waht to query tables from > Tables
3. Click with right mouse button one table you want to query
4. Choose Script table as > SELECT to > New query editor window
5. A new window opens where the queries can be written and executed

Note: For executing a query click "!Execute" on the toolbar on the left side.

Note: To address the correct table (if you have more accounts available) please refer to it with name starting with name of the linked server.
Example: [NETSUITE].[MyNetSuiteAccount].[Administrator].[ACCOUNT_ACTIVITY]

Note: The [MyNetSuiteAccount] can be Company Name or Legal Name. Both can be found under Setup > Company > Company Information > Company Name.

Note: If Company Name or Legal Name (whatever used as [MyNetSuiteAccount]) contains dot character (.) the dot is replaced in SQL Management Studio with underscore character (_). However, use the dot instead as the Studio can't use the [MyNetSuiteAccount] with underscore.

4 comments:

  1. Hello!

    Thank you for posting this.

    I followed all of the steps but in steps 2.5-2.7 I do not see a security tab to complete step 2.6.

    Could you possibly provide a screen shot or any additional details on where this might be located?

    ReplyDelete
  2. For movie buffs who like to work with cinema on the web and love to compress cinema and change them into different formats, Media Cleaner, MovieWorks, and Livestage are the web tools that should suffice. Natnit Blog

    ReplyDelete
  3. The e-Commerce version of Drumbeat 2000 helps in flawless database management. Natnit Blog

    ReplyDelete