Fix Invalid Column Name Error When Opening Customer System in SedonaOffice

04/29/2024 9:52 am EDT

Due to an issue with the SedonaOffice 6.2.0.9 database update, it may be necessary to manually update the CS_TP_Setup table in the user's SedonaOffice companies.

Description of Issue: 

The problem is showing up if the customer was on a version number lower than the 6.2.0.8 release.

If a user selects to edit an existing system or tries to add a new system, The error Invalid Column Name AutoSync will be returned.

Graphical user interface, text
Description automatically generated with medium confidence

A secondary message is returned after clicking OK on this error.

Graphical user interface, text, application
Description automatically generated

Resolution:

To correct this issue, you will need access to the user's SQL server.

From a machine that has SQL Server Management Studio installed, type SSMS in Windows Search.

Log into the correct server using the SedonaUser SQL account.

Graphical user interface, text, application, email
Description automatically generated

Click the New Query button in SSMS.

Graphical user interface, application, Teams
Description automatically generated

Once the Query window opens, select the company with the problem.

You can use the script below to find the database names for the SedonaOffice companies.

Use SedonaMaster 
Select DataSource as 'SedonaOffice Databases' From Company

SQL Script to add the missing column:

This script will add the missing field to the table and import the records back into the table.

This will need to be executed against any of the user's SedonaOffice Databases.

Copy and paste the full script below into the Query window in SSMS

--- Check for the backup table and drop it if exists --- 
IF OBJECT_ID(N'CS_TP_Setup_Backup_AutoSyncFix', N'U') IS NOT NULL
      DROP TABLE CS_TP_Setup_Backup_AutoSyncFix  

--- Create Backup of table --- 
select * into CS_TP_Setup_Backup_AutoSyncFix from CS_TP_Setup 
GO  

--- Drop existing constraints ---  

DECLARE @SQL NVARCHAR(MAX) = N'';  

SELECT @SQL += N' ALTER TABLE ' + OBJECT_NAME(PARENT_OBJECT_ID) + ' DROP CONSTRAINT ' + OBJECT_NAME(OBJECT_ID) + ';'  
FROM SYS.OBJECTS 
WHERE TYPE_DESC LIKE '%CONSTRAINT' AND OBJECT_NAME(PARENT_OBJECT_ID) = 'CS_TP_Setup';  

--PRINT @SQL 
EXECUTE(@SQL)  

--- Rename Table old table --- 
EXEC sp_rename 'CS_TP_Setup', 'CS_TP_Setup_old' 
GO 
--- Create Table CS_TP_Setup ---  

SET ANSI_NULLS ON 
GO  

SET QUOTED_IDENTIFIER ON 
GO  

CREATE TABLE CS_TP_Setup ( 
[Id] [int] IDENTITY(1,1) NOT NULL, 
[IntegrationId] [int] NOT NULL, 
[Description] [nvarchar](50) NOT NULL, 
[UseUserLevelSecurity] [bit] NOT NULL, 
[UserName] [nvarchar](50) NOT NULL, 
[Password] [nvarchar](50) NOT NULL, 
[ServiceUrl] [nvarchar](150) NOT NULL, 
[ApiKey1] [nvarchar](50) NOT NULL, 
[ApiKey2] [nvarchar](50) NOT NULL, 
[ApiKey3] [nvarchar](50) NOT NULL, 
[CSSystemLabel] [nvarchar](100) NOT NULL, 
[DefaultAccountingCompany] [nvarchar](50) NOT NULL, 
[AutoSync] [smallint] NOT NULL,  
CONSTRAINT [PK_CS_TP_Setup] PRIMARY KEY CLUSTERED  ( 
[Id] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80 
) ON [PRIMARY] ) ON [PRIMARY] 
GO  

ALTER TABLE [dbo].[CS_TP_Setup] ADD  CONSTRAINT [DF_CS_TP_Setup_UseUserLevelSecurity] 
DEFAULT ((0)) FOR [UseUserLevelSecurity] 
GO  

ALTER TABLE [dbo].[CS_TP_Setup] ADD  DEFAULT ('') FOR [DefaultAccountingCompany] 
GO  

ALTER TABLE [dbo].[CS_TP_Setup] ADD  DEFAULT ((1)) FOR [AutoSync] 
GO  

--- Insert Records from Backup into New Table --- 
SET IDENTITY_INSERT CS_TP_Setup on  

INSERT INTO CS_TP_Setup
            ( Id
    ,IntegrationId
            ,Description
            ,UseUserLevelSecurity
            ,UserName
            ,Password
            ,ServiceUrl
            ,ApiKey1
            ,ApiKey2
            ,ApiKey3
            ,CSSystemLabel
            ,DefaultAccountingCompany)             

Select
              Id
   ,IntegrationId
           ,Description
           ,UseUserLevelSecurity
           ,UserName
           ,Password
           ,ServiceUrl
           ,ApiKey1
           ,ApiKey2
           ,ApiKey3
           ,CSSystemLabel
           ,DefaultAccountingCompany 
From CS_TP_Setup_Backup_AutoSyncFix 
GO 
SET IDENTITY_INSERT CS_TP_Setup off 
GO  
Select * from CS_TP_Setup

Drop the backup and old table if the data is correct.

Once you have verified the data is correct and the new column has been added, you can remove the backup and old tables.

Copy the statement below to a new query window and execute it on the correct SedonaOffice database where the change was made.

IF OBJECT_ID(N'CS_TP_Setup_Backup_AutoSyncFix', N'U') IS NOT NULL        
DROP TABLE CS_TP_Setup_Backup_AutoSyncFix     
GO     
IF OBJECT_ID(N'CS_TP_Setup_Old', N'U') IS NOT NULL      
DROP TABLE CS_TP_Setup_Backup_Old

Was this article helpful?