6 Replies Latest reply: May 15, 2014 10:12 AM by elginreigner _ RSS

    Monarch ODBC Timeout

    elginreigner _

      I'm running SQL 2000, when I run any ODBC connections during the day, it times out during lookups and Monarch crashes.

       

      Is there a setting in the registry to adjust the timeout length for ODBC lookups in Monarch?

        • Monarch ODBC Timeout
          Gareth Horton

          Hi Elgin,

           

          What are the inputs?

           

          How many rows in the Monarch table? How many rows in the SQL table you are performing the lookup to?

           

          Is this a project or model?

           

          At which point in the process does the problem occur?

           

          Is there any error message?

           

          Do you have logging enabled? If so, could you provide the logs? If not, read the information on logging at the bottom of this post:

           

          Does the timeout occur if you import the same data using the same DSN, rather than using it as a lookup?

           

          Is there any difference when using the ODBC Compatibility setting, rather than Performance?

           

          Does the problem occur if you use OLE DB instead of ODBC? (Recreate the lookup(s) using the OLE DB Driver for SQL Server)  Note that OLE DB usually has superior performance compared to ODBC.

           

          When logging is enabled, Monarch will create a new log for every Monarch session in the path specified by the following registry key, which must be manually added:

           

          HKEY_CURRENT_USER\Software\Datawatch\Monarch Pro\Settings\TraceLogFolder

           

          This is a String Value and must be a fully qualified path to a folder where the current user has create file and write privileges.

           

          The logs will be named starting with the string "MC_" and continue with a unique ID (GUID) and have a .log extension.

           

          The logging level can be enabled in the following place:

           

          HKEY_CURRENT_USER\Software\Datawatch\Monarch Pro\Settings\TraceLogLevel

           

          If the registry key does not exist, currently, a value of 1 will be assumed.

           

          It is a DWORD value with the following valid values:

           

          0 - Minimal: Monarch component versions and system environment information.

          1 - Normal: Messages included for the Minimal level plus application event logging.

          2 - Verbose: Minimal and Normal messages plus all performance and debug logging.

           

          Remember that with logging enabled, Monarch will create a new log every time you run it. This can lead to the accumulation of a large number of files and use up disk space.

           

          Using the logging levels of 1 and higher can have some performance implications.

           

          Thanks

           

          Gareth

            • Monarch ODBC Timeout
              elginreigner _

              Gareth

               

              Thanks for the reply.

               

              This is the scenario for reference:

               

              I have a file from a client that contains payments for our accounts. Brand new project and model. I simply drag the file in, import all columns. At that point I change data type from numeric to character (db field is character) and do my look. Upon hitting finish, Monarch will do it's thing for a few minutes, then I get the type crash/error box that just say Monarch with an ok button.

               

              Currently I do not have logging on. I will have to enable it and get you a log file. The table I am doing the lookup on currently is at 5,054,918 records. I have not tried OLE DB since 9.0 (never had too much success with it), but I work on this for better feedback. After reviewing the settings for OLE DB deeper, this may be direction since I can set the timeout string.

               

              System and Server specs if needed:

              System corei7-2600 (2nd gen), 12GB ram, win7 64bit, Monarch Pro 10.5

              Server Dual Quad Xeon, 32GB Ram, win2003 32bit,  SQL 2000 DB (<<this is mostly likely the choke point)

                • Monarch ODBC Timeout
                  Gareth Horton

                  Hi Elgin,

                   

                  That's a hefty lookup.

                   

                  Is the SQL box on 2003 Standard or Enterprise?  You can only address 4GB with Standard.

                   

                  http://msdn.microsoft.com/en-us/windows/hardware/gg487503.aspx[/url]

                   

                  If it's Enterprise, you can use the full 32GB, but SQL Server would have to be configured to take advantage of this as welll.

                   

                  SQL Server 2000 would have to be Enterprise Edition too.

                   

                  http://support.microsoft.com/kb/274750[/url]

                   

                  If not, you are going to be limited on the SQL Server side to a pretty small amount of RAM for a 5 million row left outer join. Especially if other users are hitting the server as well.

                   

                  Gareth

                   

                  Gareth

                   

                  Thanks for the reply.

                   

                  This is the scenario for reference:

                   

                  I have a file from a client that contains payments for our accounts. Brand new project and model. I simply drag the file in, import all columns. At that point I change data type from numeric to character (db field is character) and do my look. Upon hitting finish, Monarch will do it's thing for a few minutes, then I get the type crash/error box that just say Monarch with an ok button.

                   

                  Currently I do not have logging on. I will have to enable it and get you a log file. The table I am doing the lookup on currently is at 5,054,918 records. I have not tried OLE DB since 9.0 (never had too much success with it), but I work on this for better feedback. After reviewing the settings for OLE DB deeper, this may be direction since I can set the timeout string.

                   

                  System and Server specs if needed:

                  System corei7-2600 (2nd gen), 12GB ram, win7 64bit, Monarch Pro 10.5

                  Server Dual Quad Xeon, 32GB Ram, win2003 32bit,  SQL 2000 DB (<<this is mostly likely the choke point)[/QUOTE]

                    • Monarch ODBC Timeout
                      elginreigner _

                      Gareth

                       

                      Thanks for the info. I'm still learning about being a SQL server admin. We are on standard for SQL, dissappointing. We do have a enterprise license available. At a later point, after our collection software upgrade, we will look into upgrading to enterprise. Or if I get what I want, it will be 2008 SQL, fingerscrossed.

                       

                      On the OLE DB connection, it did work without timing out. However, it took a long time, 10 minutes or so. Probably related to the issue above.