Solved

Custom Table Schema Incorrect Syntax Near ','

  • 20 September 2022
  • 3 replies
  • 270 views

Userlevel 2
Badge +1

I’m getting an error when deploying a package with a custom screen to another instance of Acumatica.

You can see in the screenshot below, that an extra comma is being added to the create table statement. I have other tables in the package that do not seem to have this issue.

I deleted the table from the package and re added it to trouble shoot, but I got the same error.

I did not see anything odd in the customization item.  I included the xml below (slightly modified to rename the table)

 

 

<Sql TableName="MyAddress" TableSchemaXml="#CDATA">
    <CDATA name="TableSchemaXml"><![CDATA[<table name="AHSVendorCoreAddress">
  <col name="CompanyID" type="Int" default="Zero" />
  <col name="AddressID" type="Int" identity="true" />
  <col name="MyNbr" type="NVarChar(15)" />
  <col name="IsDefaultAddress" type="Bit" />
  <col name="AddressLine1" type="NVarChar(50)" nullable="true" />
  <col name="AddressLine2" type="NVarChar(50)" nullable="true" />
  <col name="AddressLine3" type="NVarChar(50)" nullable="true" />
  <col name="City" type="NVarChar(50)" nullable="true" />
  <col name="CountryID" type="NVarChar(2)" nullable="true" />
  <col name="State" type="NVarChar(50)" nullable="true" />
  <col name="PostalCode" type="VarChar(20)" nullable="true" />
  <col name="IsValidated" type="Bit" />
  <col name="PseudonymizationStatus" type="Int" nullable="true" default="Zero" />
  <col name="NoteID" type="UniqueIdentifier" />
  <col name="CreatedByID" type="UniqueIdentifier" />
  <col name="CreatedByScreenID" type="Char(8)" />
  <col name="CreatedDateTime" type="DateTime" />
  <col name="LastModifiedByID" type="UniqueIdentifier" />
  <col name="LastModifiedByScreenID" type="Char(8)" />
  <col name="LastModifiedDateTime" type="DateTime" />
  <col name="tstamp" type="Timestamp" />
  <col name="Latitude" type="Decimal(10,6)" nullable="true" />
  <col name="Longitude" type="Decimal(10,6)" nullable="true" />
  <index name="[MyAddress_PK" clustered="true" primary="true" unique="true">
    <col name="CompanyID" />
    <col name="MyNbr" />
  </index>
  <index name="MyAddress_NoteID" unique="true">
    <col name="NoteID" />
    <col name="CompanyID" />
  </index>
</table>]]></CDATA>
</Sql>

icon

Best answer by Brian Stevens 20 September 2022, 00:54

View original

3 replies

Userlevel 6
Badge +4

@scottstanaland12 

Maybe you are missing a bracket

 

Userlevel 6
Badge +4

Not sure if it has anything to do with it, but this seems to be a stray left bracket on the index name of your primary index.  Might be a typo when you replaced your actual index name.

 <index name="[MyAddress_PK"

For comparison, here’s one of my tables.

    <Sql TableName="SSSOWorkOrder" TableSchemaXml="#CDATA">
        <CDATA name="TableSchemaXml"><![CDATA[<table name="SSSOWorkOrder">
  <col name="CompanyID" type="Int" default="Zero" />
  <col name="WorkOrderID" type="Int" identity="true" />
  <col name="WorkOrderCD" type="NVarChar(30)" />
  <col name="BranchID" type="Int" />
  <col name="Descr" type="NVarChar(256)" nullable="true" />
  <col name="IsActive" type="Bit" />
  <col name="CreatedByID" type="UniqueIdentifier" />
  <col name="CreatedByScreenID" type="Char(8)" />
  <col name="CreatedDateTime" type="DateTime" />
  <col name="LastModifiedByID" type="UniqueIdentifier" />
  <col name="LastModifiedByScreenID" type="Char(8)" />
  <col name="LastModifiedDateTime" type="DateTime" />
  <col name="tstamp" type="Timestamp" />
  <col name="NoteID" type="UniqueIdentifier" nullable="true" />
  <col name="DeletedDatabaseRecord" type="Bit" default="Zero" />
  <index name="SSSOWorkOrder_NoteID" unique="true">
    <col name="NoteID" />
    <col name="CompanyID" />
  </index>
  <index name="SSSOWorkOrder_PK" clustered="true" primary="true" unique="true">
    <col name="CompanyID" />
    <col name="WorkOrderID" />
  </index>
</table>]]></CDATA>
    </Sql>

 

Could be stray punctuation, or perhaps the error is either just before or after this code in your project.xml.

Userlevel 2
Badge +1

@Brian Stevens 

Good Eye, that extra bracket is in the original project item.  Turns out I must have fat fingered it when I added the primary key in SSMS .  The Extra bracket was actually in the name of the primary key in the database which must throw off the Acumatica customization editor. 

My project is publishing ok now, thanks for the quick response.

Scott

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved