XML API Tips: Internal-error When Executing Reporting Calls

Periodically when executing a reporting API call, you may get an unexpected return as shown below:

<results>
<status code=”internal-error”>
<exception>java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database “XXXXXXXXX” requested by the login. The login failed.</exception>
</status>
</results>

Where the ‘XXXXXXXXX’ would be the database name of the database your request was trying to hit.

This is expected if you are making one of the ‘reporting database API calls‘ during the exact time that the db is locked for a small restore.  As previously discussed, the reporting database is not real-time. It is synched occasionally and can be behind by as much as 24 hours.   That error (you would see it in the logs and in your response) is thrown when the DB is being restored.  When the db is being restored, the DB is locked down and the result will be a failed login (internal-error).  The reporting DB is log shipped every 15 minutes.  So every 15 minutes there will be a small restore.  All you’re application needs to do is retry when you get that message.  It could be as much as a minute of downtime, but most of the time is less.