Solved

SQL Table default not being created when I publish my project

  • 8 June 2023
  • 1 reply
  • 112 views

Userlevel 6
Badge +3

I am working in 2022R1.

I created a custom table with a default to one of the fields.  I created the table in MSSQL Management Studio.  After I create the table and create a script of the table it shows that the default is there.  I tested it by adding a dummy record and “AV” is filled into the Module field.

This is the script to create the table:

CREATE TABLE [dbo].[ICSMergeLock](
    [CompanyID] [int] NOT NULL,
    [Module] [char](2) NOT NULL,
 CONSTRAINT [ICSMergeLock_PK] PRIMARY KEY CLUSTERED 
(
    [CompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ICSMergeLock] ADD  DEFAULT ((0)) FOR [CompanyID]
GO

ALTER TABLE [dbo].[ICSMergeLock] ADD  DEFAULT ('AV') FOR [Module]
GO

In the project editor, I ADD CUSTOM TABLE SCHEMA for the table.  In Edit Project Items, this is what the project is storing for this table:

<Sql TableName="ICSMergeLock" TableSchemaXml="#CDATA">
    <CDATA name="TableSchemaXml"><![CDATA[<table name="ICSMergeLock">
  <col name="CompanyID" type="Int" default="Zero" />
  <col name="Module" type="Char(2)" raw-default="'AV'" />
  <index name="ICSMergeLock_PK" clustered="true" primary="true" unique="true">
    <col name="CompanyID" />
  </index>
</table>]]></CDATA>
</Sql>

Using a clean instance, I publish the project.  This is the table it creates.

CREATE TABLE [dbo].[ICSMergeLock](
    [CompanyID] [int] NOT NULL,
    [Module] [char](2) NOT NULL,
 CONSTRAINT [ICSMergeLock_PK] PRIMARY KEY CLUSTERED 
(
    [CompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ICSMergeLock] ADD  DEFAULT ((0)) FOR [CompanyID]
GO

Note that the command to create the AV default for the Module field is missing.

I can add this as a separate SQL script, but why is the publish process not creating my default?
 

 

icon

Best answer by Joe Schmucker 12 June 2023, 19:28

View original

1 reply

Userlevel 6
Badge +3

Well, it doesn’t look like anyone has had this issue.  Here is my solution to getting my additional Default to update my table.  I simply added another script to the project.  I know that the default will contain “Modu” in it, so I see if it exists and if not, I add it.

It is a workaround, but it worked.

IF NOT EXISTS (SELECT 
    OBJECT_NAME(OBJECT_ID) AS NameofConstraint
        ,SCHEMA_NAME(schema_id) AS SchemaName
        ,OBJECT_NAME(parent_object_id) AS TableName
        ,type_desc AS ConstraintType
       FROM sys.objects
    WHERE 
        OBJECT_NAME(parent_object_id) = 'ICSMergeLock'
        AND OBJECT_NAME(OBJECT_ID) LIKE '%Modu%')
    ALTER TABLE [dbo].[ICSMergeLock] ADD  DEFAULT ('AV') FOR [Module]
GO

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