Acumatica custom db script not working

Question

I ran into an issue where a db script in an Acumatica package does not work. I see it run, but the change in the db just doesn’t happen. If I run the same script in SSMS, it works. I do make sure that I either tweak the script comment or publish with cleanup so that the script isn’t skipped during publish, and the log says it ran, but it doesn’t work. For example, I have a simple create table script...

IF (NOT EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'dbo' 
    AND TABLE_NAME = 'EEdiEntityType'))

BEGIN
    CREATE TABLE [dbo].[EEdiEntityType](
        [CompanyID] [int] NOT NULL,
        [EntityType] [int] NOT NULL,
        [Description] [nvarchar](50) NOT NULL,
    CONSTRAINT [PK_EEdiEntityType] PRIMARY KEY CLUSTERED 
    (
        [CompanyID] ASC,
        [EntityType] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
END
GO

I added this script to my package, deployed it, it says it runs, but no new table.

Acumatica v17.209.0028

I tried locally with build 17.209.0028 and found it working - publishing package containing this script is creating DB table. Once table is created after first publish, what changes are you making to your script? Are you dropping table and publishing again with DB Cleanup? — DChhapgar
I have 34 other scripts that create or alter tables during publish. This script never creates the table for me, and in fact none of the recent changes I made to scripts will do anything, include altering other tables. I ran this script directly in SSMS and it created the table. I deleted the table in SSMS so I could determine why the script is not working. I posted this particular script because I thought if I could figure out why it's not working, the rest of the changes would work also. — Tony Lanzer

DChhapgar DChhapgar · Accepted Answer · 2018-08-15T18:21:09

@tlanzer, please make sure you are verifying in SSMS against correct database. You can check connectionstring in web.config of Acumatica Site.

Acumatica custom db script not working

1 Answers