Mobilink - getting errors by email

[former_member329524]

Hello, all

I know it is possible to log the Mobilink errors and send them by email automatically.

Yet, I cannot find the instructions on how to do it in the help.

Can someone, please, point me to a link where it is all explained?

Thank you

Arcady

[jeff_albion · Accepted solution]

Hi Arcady,

I believe what you're thinking of specifically is the SQL Anywhere Monitor - it will allow you to monitor your MobiLink server and you can set up e-mail alerts for specific events (errors, outages, etc.).

Otherwise, the MobiLink server does not have a built-in capability to e-mail administrators, outside of what is supported by the consolidated database it is connected to - you can potentially create custom stored procedures if your consolidated database supports this.

For instance, if I had a SQL Anywhere consolidated database, I could create a script like the folowing that uses the 'report_error' connection event to call 'xp_startsmtp' / 'xp_sendmail' / 'xp_stopsmtp' (warning: untested code!):

CREATE FUNCTION f_ml_email_error(IN @action_code INT, IN @error_code INT, IN @error_message LONG VARCHAR, IN @user_name VARCHAR(128), IN @table_name VARCHAR(128))
    RETURNS int
    BEGIN
        DECLARE @SMTP_SENDER                LONG VARCHAR;
        DECLARE @SMTP_SERVER                LONG VARCHAR;
        DECLARE @SMTP_SENDER_NAME           LONG VARCHAR;
        DECLARE @SMTP_AUTH_USERNAME         LONG VARCHAR;
        DECLARE @SMTP_AUTH_PASSWORD         LONG VARCHAR;
        DECLARE @RECIPIENTS                 LONG VARCHAR;
        DECLARE @MAIL_SUBJECT               LONG VARCHAR;
        DECLARE @REPORT                     LONG VARCHAR;
        DECLARE @SMTP_RETURN_CODE           INT;
        SET @SMTP_SENDER = 'firstname.lastname@server.com';
        SET @SMTP_SERVER = 'smtp.server.com';
        SET @SMTP_SENDER_NAME = 'Jeff Albion';
        SET @SMTP_AUTH_USERNAME = 'username';
        SET @SMTP_AUTH_PASSWORD = 'password';
        SET @RECIPIENTS = 'administrator@server.com';
        SET @MAIL_SUBJECT = 'MobiLink Error Reporting';

        set @REPORT = "MobiLink Error hit by user " || @user_name || " on table " || @table_name || "." || "\n   Action Code: " || @action_code || "\n   Error Code: " || @error_code || "\n\nError Message:\n\n" || @error_message;

        @SMTP_RETURN_CODE = CALL dbo.xp_startsmtp( smtp_sender = @SMTP_SENDER, smtp_server = @SMTP_SERVER, smtp_sender_name = @SMTP_SENDER_NAME, smtp_auth_username = @SMTP_AUTH_USERNAME, smtp_auth_password = @SMTP_AUTH_PASSWORD );
        IF @SMTP_RETURN_CODE <> 0 THEN
            SET @MSG = 'dbo.xp_startsmtp() failed: (return code = ' || @SMTP_RETURN_CODE || ')';
            SET @REPORT = @REPORT || @MSG || '\n';

            MESSAGE @REPORT;

            RETURN 0;
        END IF;
        @SMTP_RETURN_CODE = CALL dbo.xp_sendmail( recipient = @RECIPIENTS, subject = @MAIL_SUBJECT, "message" = @REPORT );
        IF @SMTP_RETURN_CODE <> 0 THEN
            SET @MSG = 'dbo.xp_sendmail() failed: (return code = ' || @SMTP_RETURN_CODE || ')';
            SET @REPORT = @REPORT || @MSG || '\n';

            MESSAGE @REPORT;

            RETURN 0;
        END IF;
        @SMTP_RETURN_CODE = CALL dbo.xp_stopsmtp();
        IF @SMTP_RETURN_CODE <> 0 THEN
            SET @MSG = 'dbo.xp_stopsmtp() failed: (return code = ' || @SMTP_RETURN_CODE || ')';
            SET @REPORT = @REPORT || @MSG || '\n';

            MESSAGE @REPORT;

            RETURN 0;
        END IF;

        RETURN 1;
    END;

    CALL ml_add_connection_script( 'v1', 'report_error',
      'CALL f_ml_email_error( {ml s.action_code}, {ml s.error_code}, {ml s.error_message}, {ml s.username}, {ml s.table} )'  );

    COMMIT;

This will then call the custom e-mail function in the context of a MobiLink error for script version 'v1'.

Hope this helps.

Regards,

Jeff Albion

SAP Active Global Support

[former_member329524 · Accepted solution]

Thank you, Jeff

This was very helpful

For some reason I seem to be unable to start the Monitor on my dev license machine...

After I click the program in the start menu (as instructed in the help), the browser opens up with 404 error and the samonitor.exe process is not even running.

Do you, by any chance, know where I could get help with that?

[jeff_albion · Accepted solution]

Hi Arcady,

Which version of the developer software are you trying - version 16? I assume you're just launching:

SQL Anywhere 16 > Administration Tools > SQL Anywhere Monitor ?

What's the command-line that the shortcut refers to and are the paths okay? It should be something like:

"C:\Program Files\SQL Anywhere 16\Bin64\samonitor.exe" -d "C:\Users\Public\Documents\SQL Anywhere 16\Monitor\samonitor.db"

Regards,

Jeff Albion

SAP Active Global Support

[former_member329524 · Accepted solution]

Yes, the path is correct

But the problem appears to be somewhere else. Is there a known issue in dev environment where the monitor is being run on the tested machine itself? Becasue I successfully started it on a different machine, a simple Win7 station, where no other DB servers were running. But I could not start it on my testing server where there were other network servers started already.

Arcady

[jeff_albion · Accepted solution]

Hi Arcady,

Is this a development deployment that you're still testing or a production deployment? There are different switches to samonitor for development / production use (-d / -s).

Also, the development version should be using the installed version of SQL Anywhere to run the monitor database - see: DocCommentXchange

But I could not start it on my testing server where there were other network servers started already.

Is it okay if you start the monitor by itself when there aren't network servers running?

Regards,

Jeff Albion

SAP Active Global Support

[former_member329524 · Accepted solution]

Thank you, Jeff

On my development machine, where the network servers are not running, the monitor starts fine.

But it fails on the testing server, where they are running.

[jeff_albion · Accepted solution]

Hi Arcady,

But it fails on the testing server, where they are running.

Does it suceed if you stop the testing server database servers first? Are you using a development or production license on your testing server?

Are you always attempting to start it through the start-menu shortcut? What happens if you type the command I posted above directly into a Command Prompt - does it launch in that case? Is there a specific error provided when starting it directly?

Regards,

Jeff Albion

SAP Active Global Support

[former_member329524 · Accepted solution]

I am trying to use this script, but even though the server log shows error, no mail is being sent.

Is there a way to debug the scripts?

this is the error, it does not appear that either 'report_error' or 'report_odbc_error' are triggered.

//---------------------------------

E. 2014-11-03 08:11:15. <2> [-10233] Table 't027_room_price_codes_master' does not have a column named 'c_promo_code_only'

E. 2014-11-03 08:11:15. <2> [-10058] Unable to open upload_update

//--------------------------------

[jeff_albion · Accepted solution]

Hi Arcady,

The MobiLink server does not always call the report_error or the report_odbc_error script, when an error occurs. It will always report the error if the error is recoverable and the MobiLink server is planning to call the handle_error or the handle_odbc_error script.

For instance, if an error occurs when it is trying to upload an insert, the MobiLink server will report this error and call the hande_error script.  If the action returned from the handle_script is 1000, then the server will ignore the error and continue the operation.  However, if the MobiLink server detects an error before sending anything to the consolidated database, the server may not report the error, because this error is not recoverable.  More precisely, the MobiLink server will report the errors generated by the ODBC driver and the consolidated database.

In your specific case, we have detected a mismatch in columns in your scripts, which is a MobiLink environment error - this is a runtime error that will always be generated with this set of scripts, so this is not a recoverable error (and handle_error is also not called). In this case, we expect the developer to fix the synchronization scripts before resuming user synchronizations with this script version.

If you would really like to action on all errors reported by MobiLink, you can use the Java/.NET LogMessage API. Please see:

• LogMessage Class (Java)
• LogMessage Class (.NET)

Regards,

Jeff Albion

SAP Active Global Support

[former_member329524 · Accepted solution]

Hi, Jeff

Can you give me a URL with examples on how to use this .NET class?

Does this mean that an external app needs to be written, or can those classes be used within the Mobilink server itself?

[jeff_albion · Accepted solution]

Hi Arcady,

The classes can be used within the MobiLink server itself. We will load a .NET CLR instance into the MobiLink memory space and run the classes, passing information from the MobiLink server (in this case, the error log) to it.

See:

• DocCommentXchange - Synchronization Scripts in .NET
• DocCommentXchange - MobiLink .NET API Reference

Here's the example .NET Code:

• DocCommentXchange - MobiLink server error handling in .NET

Regards,

Jeff Albion

SAP Active Global Support

[jeff_albion · Accepted solution]

Hi Arcady,

I probably should also mention here that if you have the ability to send e-mail via .NET or Java, you could also use Java/.NET synchronization sripts and e-mail that way directly from MobiLink also.

Regards,

Jeff Albion

SAP Active Global Support