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