No internet connection
  1. Home
  2. General

ODBC Connection Error

By @matty
    2022-05-06 22:51:25.411Z2022-05-11 18:36:46.285Z

    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

    • 9 replies
    1. S
      Stephen Parker @stephenparker
        2022-05-10 07:50:25.500Z

        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.


        1. M
          In reply tomatty:
            2022-05-10 16:34:58.328Z

            Thanks Stephen this worked.

            1. R
              In reply tomatty:
                2022-05-11 16:46:44.433Z

                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?

                1. M@matty
                    2022-05-11 17:50:15.599Z2022-05-11 18:39:11.065Z

                    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.

                    1. R@readar
                        2022-05-11 18:22:36.165Z

                        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.

                        1. M@matty
                            2022-05-11 18:31:47.853Z

                            Ah yes come to think of it I also had to set the user in the ODBC settings as it wasn't being passed.

                        2. In reply toreadar:
                            2022-10-24 17:29:34.126Z

                            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,


                            1. R@readar
                                2022-10-24 17:45:31.125Z

                                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.

                                1. M@matty
                                    2022-10-24 19:55:15.128Z