I recently ran into an issue where a client was unable to insert Opportunities when logged in as particular users. When they clicked Save they would get an error similar to this:
The statement has been terminated. : String or binary data would be truncated.
Saleslogix Error Id: SLX2AECA524F481B1D7
Exception type: System.Data.OleDb.OleDbException
Source: System.Data.OleDb.OleDbCommand, System.Data, Version=22.214.171.124, Culture=neutral, PublicKeyToken=b77a5c561934e089
Using SQL trace I was able to see the SQL statement that was being attempted was:
INSERT INTO OPPORTUNITY
(ACTUALAMOUNT, ADDTOFORECAST, CLOSED, CLOSEPROBABILITY, CREATEDATE, CREATEUSER, DATEOPENED,
DESCRIPTION, ESTIMATEDCLOSE, EXCHANGERATE, EXCHANGERATECODE, EXCHANGERATEDATE, EXCHANGERATELOCKED,
MODIFYDATE, MODIFYUSER, SALESPOTENTIAL, STATUS, TYPE, OVERRIDESALESPOTENTIAL, ACCOUNTMANAGERID,
SECCODEID, ACCOUNTID, OPPORTUNITYID)
(0,’F’,’F’,50,’2014-11-20 21:15:28′,’ADMIN ‘,’2014-11-20 21:15:07’,
‘DESCRIPTION’,’2015-01-31 00:00:00′,1,’United States Dollar’,’2014-11-20 21:15:07′,’F’,
‘2014-11-20 21:15:28’,’ADMIN ‘,0,’Open’,’Existing Customer’,’F’,’U6UJ9A0000HI’,
While that SQL looked OK, I noticed that the ExchangeRateCode was being set to “United States Dollar”.
Looking at the data type definition ot the ExchangeRateCode field in the OPPORTUNITY table, I see it is a varchar(3) field.
Firing up Reflector I was able to see that this is being read on the Opportunity’s OnCreate Event and is being read from the UserOptions service.
This service essentially reads values from the USEROPTIONS table for the current user.
Running a SQL statement for the particular value that it was looking for to default the Currency Code is this:
select * from useroptions where CATEGORY=’opportunitydefaults’ and name=’lveCurrency’
In my case I found that several users had their default values set to “United States Dollar” rather than the correct “USD”. This is likely a left over from previous versions having set this value differently than what is expected in the past.
Using this statement finds all the rows with an issue:
select * from useroptions where CATEGORY=’opportunitydefaults’ and name=’lveCurrency’ and len(cast(optionvalue as varchar(96)))>3
Finally, using this statement correct the issue:
update USEROPTIONS set optionvalue=’USD’ where cast(OPTIONVALUE as varchar(64))=’United States Dollar’ and CATEGORY=’opportunitydefaults’ and name=’lveCurrency’
After the user logged out of the web client and back in (UserOptions are cached during log in) the user was now able to add Opportunities.