Store Connect: error “Multiple-step operation generated errors”

Area: Retail\Store
Connect

Affected product:

Dynamics AX 2009 SP1 RU6 RETAIL R2

Dynamics AX 2009 SP1 RU7 RETAIL R2 Refresh

Problem:

When deploying the data from AX HQ to the POS database, you
might encounter a problem with inserting the data to the POS database. In the
Retail Store connect database in the outgoing message the following error
message occurs.

Example

Multiple-step operation generated errors. Check each status
value., table ‘ADDRESS’: 5637213327

Reason:

When inserting the data to the POS tables it is not allowed
to insert data that exceed the maximum string size of the column. There are
around 150 columns on the POS that are less in size than in the HQ
database. 

An example is CITY in the Address table. In the HQ database it is 60 characters and in POS database 30
characters. Adding a CITY with more than 30 character and then transfer the
N-1010 job that includes the ADDRESS table, the Store connect will fail.

Solution:

The solution is not out of the box, but here are two options.

1. In the Retail POS Plug-ins for partners, there is a
service which can be modified to include the correct databasescript. The
service is called CreateDatabase. The script can be replaced with one from this
blog post. You need to use Visual Studio to compile a new dll file.

  

2. Second option is to create the AxRetailPos database with a changed script.

The script provided in aligned to the R2 Refresh version.

 

If you want to gather information about the schema difference between the HQ DB and the POS DB

• First, export the SQLDICTIONARY table from the HQ to the AXRETAILPOS
• Run this job and see the difference in the string sizes

SELECT o.name as TABLENAME, c.name as “COLUMN NAME”,   d2.strsize as HQ, c.max_length/2 as POS FROM sys.objects o, sys.columns c, SQLDICTIONARY d1, SQLDICTIONARY d2 WHERE      o.type_desc =   ‘USER_TABLE’ AND                     o.object_id = c.object_id AND                     d1.fieldid = 0 AND                     d1.SQLNAME = o.name AND                     d2.fieldid > 0 AND                     d1.tableid = d2.tableid AND                     d2.SQLNAME = c.name AND                     c.system_type_id=231 AND                     d2.strsize > (c.max_length/2) ORDER BY o.name, c.name

 

 3. Fields on HQ DB that are smaller

Tables with columns that are larger on POS than in
the HQ database.

No script provided, since changes made to the AX application, could break code or relations.

The same procedure as described above.

SELECT o.name as TABLENAME, c.name as “COLUMN NAME”, d2.strsize   as HQ, c.max_length/2 as POS FROM sys.objects o, sys.columns c, SQLDICTIONARY d1, SQLDICTIONARY d2 WHERE      o.type_desc =   ‘USER_TABLE’ AND                     o.object_id = c.object_id AND                     d1.fieldid = 0 AND                   d1.SQLNAME   = o.name AND                     d2.fieldid > 0 AND                     d1.tableid = d2.tableid AND                     d2.SQLNAME = c.name AND                     c.system_type_id=231 AND                     d2.strsize <> (c.max_length/2) ORDER BY o.name, c.name

 

Output:

TABLENAME	COLUMN NAME	HQ	POS
POSFUNCTIONALITYPROFILE	CENTRALTABLESERVERPORT	10	50
POSHARDWAREPROFILE	DRAWERDEVICENAME	30	60
POSHARDWAREPROFILE	DUALDISPLAYIMAGEPATH	259	260
POSHARDWAREPROFILE	MSRDEVICENAME	30	60
POSISERRORS	CODEUNIT	100	250
POSISFORMLAYOUT	TITLE	30	50
POSISKEYBOARDBUTTONCONTROL	DEFAULTCOLOR	10	20
POSISKEYBOARDMAPPINGTRANS	KEYCHAR	1	10
POSISLANGUAGETEXT	LANGUAGEID	5	7
POSISLOG	LOGSTRING	0	1000
POSISTILLLAYOUT	RECEIPTID	10	18
RBOLOYALTYCUSTTABLE	STREET	60	250
RBOSTAFFTABLE	FIRSTNAME	20	30
RBOSTAFFTABLE	LASTNAME	20	30
RBOSTORETABLE	CULTURENAME	7	10
RBOTRANSACTIONSALESTRANS	CREATEDBY	5	10
RBOTRANSACTIONSALESTRANS	DISCGROUPID	10	50
RBOTRANSACTIONSALESTRANS	FILELOGID	10	20
RBOTRANSACTIONTABLE	CREATEDBY	5	10

Author: Kim

Editor: Kim

Date: 18. August 2011

CreateDatabaseR2Refresh.txt