Articles in this section

See more

Common errors when requesting a report from CEO Juice in Sherpa's eAutomate interface

Avatar
Nathan
  • Updated
Follow

Summary of issue:

  • Error appears after clicking button to request a report, such as a QBR, from the ERP interface in Sherpa.
  • Error message may states login failed for user '<user name>' or state a conversion error, or could not open a SQL connection error.
  • This issue may also cause the eAutomate ERP interface to take a longer amount of time before it appears after clicking the button on the account.
  • The user name error is a SQL server login to the ERP SQL server for access to the ceojuice database.
  • The conversion error indicates a problem with the user's user profile in the ERP ID field.
  • The SQL connection error indicates Sherpa is unable to reach the specified server hosting the ceojuice database.
  • Solution is to ensure there are no typos in the username and password, and that the SQL server  SQL login used has db_owner rights to the eAutomate database and ceojuice database, that the specified server is correct and that the user profile has the appropriate Agent ID number and not the Agent Number.

 

Description of issues:

When in the eAutomate interface in Sherpa on an account, you may see one of the following errors - 

 

1) Could not open a connection to SQL server

 

2) Conversion from string "xxxx" to type 'Long' is not valid.

 

3) Login failed for user 'xxxxxx'.

 

Details and recommended solutions for errors:

 

Error - 1) Could not open a connection to SQL server - 

This issue may increase the time it takes for the eAutomate interface to appear after clicking the button on an account to look at the ERP data. The reason for this is if the CEO Juice reports integration is present, when clicking this button it attempts to connect to both the eAutomate database and the CEO Juice database as both are necessary for the data and for any reports requested.

To correct this, it will be necessary to work with IT as needed and go into the Administration > Control Panel> Third Party Tools > CEO Juice section in Sherpa, as seen below.

 

This section requires the information that make up an ODBC type connection. The SQL server must be specified as either the IP address, the FQDN, or the IP\SQL server instance name or FQDN\SQL server instance name (e.g. 192.168.0.10\SQLEXPRESS or SQLSERVER01\SQLEXPRESS).

The database is typically named 'ceojuice' and the username and password must be a SQL login (not SQL user, which is different) and that SQL login must have db_owner rights mapped to it for both the ceojuice database and the eAutomate SQL database. All the information in this area will most likely look similar to the information in the Administration > Control Panel > ERP area for the connection to the eAutomate SQL server since the ceojuice database is typically on the same SQL server.

When the information is added and saved, to test it - open the accounts window and go to the eAuto ERP interface to attempt to request a report from CEO Juice.

 

Error - 2) Conversion from string "xxxx" to type 'Long' is not valid. - 

When a report is being requested, it usually must be tied to an Agent in the eAutomate database. Sherpa uses the 'Agent ID' from the eAutomate database rather than the 'Agent Number' which may be on the user's user profile by mistake. If something besides the integer of the Agent ID is there, then the error can occur.

To correct this, go to the Administration > Manage Users > Add/edit users section and open the user's user profile for editing. The ERP ID field is the main concern that needs to be changed. To set the appropriate number in this field, click on the [?] button next to the field. This will display the list of Agents from the eAutomate database and show their Agent ID number as the ERP ID in the list.

 

Select the appropriate Agent and then click Submit. That will assign that number to the ERP ID field on the user profile. Then click submit to save the change to the user profile. Once completed, please test by going to the accounts section in Sherpa and open the eAuto ERP interface and attempt to request a report from CEO Juice.

 

Error - 3) Login failed for user 'xxxxxx'. -

This issue is related to the first error where the user indicated is the SQL login for the connection information found in Administration > Control Panel> Third Party Tools > CEO Juice.

This indicates that the server information is likely correct, but there is an issue with the SQL login information. Causes could be a typo in the username or password, or the SQL login is not enabled properly or mapped with proper rights to the appropriate databases.

To confirm the setup of the SQL login on the SQL server directly, locate the Security > Logins folder for the server instance and go to the properties on the specific user being used for this purpose. Example:

 

Verify that the login is both enabled and granted permission to connect.

 

 

Verify that the login has db_owner mapped to the appropriate database(s). Example using an eAuto database.

 

Once this is completed and that the SQL login username and password are correct, please then go to an account in Sherpa, open the eAuto ERP interface and attempt to request a report from CEO Juice.

If there are no error messages, the issues are resolved.

 

Last updated: 8/9/2023

Comments

0 comments

Article is closed for comments.