ODBC Connection Error
Hi when I build the connection string and test it is successful.
When I test in the database lookup it is successful and returns a result
.
When I run the automation the lookup fails with this error: Failed to open database using connection: provider=ODBC;Dsn=TM_REPORTING_00001;User Id=;Password=**; ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
- SStephen Parker @stephenparker
Check that the odbc dsn is a system dsn - not a user dsn. The message processor service will not have access to a user dsn unless the service is configured to run under that users account. Testing the connection using the Studio will work with a user dsn because the Studio is running under the logged in user context whereas the message processor service runs under the SYSTEM account by default.
Thanks
- RIn reply tomatty⬆:@readar
Hi Matt, I see you're a fellow truckmate user. I could never get the ODBC connector or the DB2 connector to work in ThinkAuto with any of the built in functions. Any tips?
Hi @readar, Yes!
With version 5 it was a bit tougher to get it to work as it wouldn't accept my connection strings from v4.
So far I haven't been able to get the DB2 Connector working but I was successful with ODBC. As soon as I set up the ODBC under system DSN and ensured it was available as 64 bit it worked. I had to use the connection string builder in TA.
Alright, thanks for the info. I played around a little more and got the ODBC connector to work. I needed to pre-populate the username on the Windows ODBC connector, the ThinkAuto connection string builder didn't seem to pass through the username for some reason.
- In reply toreadar⬆:
Hi @readar, Question for you, was the ODBC connection sufficient for your purposes? Unfortunately for me I need to retrieve a CLOB (notes) field and ODBC can not do this...I haven't been able to get OLEDB or the DB2 Connector to work...
Just thought I'd see if you made any progress beyond the ODBC connection.
Thank you,
I couldn't get the clob type to work with the odbc connector. If you want to retrieve a clob value, a cast should work [cast(clob_val as varchar(5000)) my_val], up until you hit some length limitations (around 16,000). ODBC has worked for everything I need to do, so haven't looked much at the DB2 .net provider.