Solved

Unable to connect ODBC in FME

  • 2 December 2019
  • 9 replies
  • 17 views

Badge +16

I am unsure as to why I'm not able to connect to NetSuite via the ODBC connector in FME. I have Windows 10 (64 bit), FME Desktop (2019.2 b 19801 64bit) with the ODBC (64bit) driver installed. I have verified that the connection works in ODBC Source Administrator and also in Excel.

 

 

The error I receive in FME is: 
Error connecting to database
Database Generated Error Information:
Database Error Message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Database SQL Statement Text:
SQL State: IM002
Variable Information:
ODBC 3.x Reader: Failed to connect to database

I have verified the data source name matches with the username and password in the reader parameters, but I am unable to connect. Any ideas as to what I could do to get it to connect?

 

0684Q00000ArBm6QAF.png

icon

Best answer by warrendev 2 January 2020, 01:24

View original

9 replies

Badge +16

I'd also like to add that I am able to connect to the database using the python library "pyodbc" successfully. Wasn't sure what might be the issue with the odbc reader in FME. Any help would be greatly appreciated.

Badge +10

Update: After working with @warrengis, we believe the issue is with having the @ symbol in the username or password. Safe is aware of this issue (internal reference FMEENGINE-9561) and we'll post any updates related to this issue here.

In the meantime, @warrengis has figured out how to make a connection using the pyodbc python library using the PythonCaller.

Badge +16

It appears an easy fix to this is to just escape the reserved characters and it will connect just fine. My username contained an @ symbol, so just escaping it with a back slash fixed the problem. \\@

Hi @Chris Warren​ 

I wanted to ask if the " @" symbol should be replaced by a"\\@" or should I replace @ with "\\", as I am facing the same issue and to make the matter worse both my username and password has "@" in it.

Any help is appreciated .

 

Cheers

Antony

 

Badge +16

Hi @Chris Warren​ 

I wanted to ask if the " @" symbol should be replaced by a"\\@" or should I replace @ with "\\", as I am facing the same issue and to make the matter worse both my username and password has "@" in it.

Any help is appreciated .

 

Cheers

Antony

 

Hi @antony123 ,

In my case I just escaped the character with the backslash. So you will still have the @ symbol, but with it escaped like "\\@". Some characters are reserved and need to be escaped.

Here is an article going over this issue that might be of use. https://community.microstrategy.com/s/article/Special-character-restrictions-of-username-and-password-in-ODBC-connection-for-Diamond-and-Platinum-gateways-new?language=en_US​

Hi @Chris Warren​ 

Thanks a lot for your reply.

I have a different question, can you tell me what you put for the database field in the ODBC reader?

Was it the service host?

imageimage 

I really appreciate your insights :)

Cheers

 

Update: After working with @warrengis, we believe the issue is with having the @ symbol in the username or password. Safe is aware of this issue (internal reference FMEENGINE-9561) and we'll post any updates related to this issue here.

In the meantime, @warrengis has figured out how to make a connection using the pyodbc python library using the PythonCaller.

Hi @nampreetatsafe​ 

Hope you are well and staying safe :)

I wanted to know if there has been any update to this ?

I am having the same issue were I cannot connect to the Netsuite using ODBC reader.

I have checked it with PowerBI and it works fine.

 

Any help is appreciated.

 

 

Badge +16

Hi @Chris Warren​ 

Thanks a lot for your reply.

I have a different question, can you tell me what you put for the database field in the ODBC reader?

Was it the service host?

imageimage 

I really appreciate your insights :)

Cheers

 

@antony123​ for the dataset you will use the name of the system data source you created in the ODBC administrator. In my case I have 2 connections. 1 for the sandbox environment and one for production. Below is an image showing the one for production.

netsuiteI have the datasource/database parameter set to the same name as my ODBC data source in FME. I'm using the SQLExecutor, but it's the same with the ODBC reader or in a feature reader.

netsuite2 Hopefully this helps you!

@antony123​ for the dataset you will use the name of the system data source you created in the ODBC administrator. In my case I have 2 connections. 1 for the sandbox environment and one for production. Below is an image showing the one for production.

netsuiteI have the datasource/database parameter set to the same name as my ODBC data source in FME. I'm using the SQLExecutor, but it's the same with the ODBC reader or in a feature reader.

netsuite2 Hopefully this helps you!

@Chris Warren​ 

 

Thanks a ton for your reply.

 

I was able to connect to Netsuite because of your help.

 

Cheers

Antony

Reply