After adding a UDF, the field will need to be added to appropriate table in the SQL publish database
When a User Defined Field (UDF) is added in PlanetTogether, you will need to add it to the Publish database. Failure to do so will cause the following or similar error message:
....... START ENTRY 2/5/2020 01:42:04 PM PT Version=11.34.2 PT Serialization Version=714 .......
Error while attempting go open a SQL Connection to Server=. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Please verify the Publish connection string in Config Manager | Extra Services.
....... START ENTRY 2/6/2020 10:51:39 AM PT Version=11.34.2 PT Serialization Version=714 .......
Error while inserting records into the APS Database in table: Jobs. The given ColumnMapping does not match up with any column in the source or destination.
The given ColumnMapping does not match up with any column in the source or destination.
To resolve this, you will need to enable the ValidateDB feature for the publish database, which can only be done from within SQL server.
Note: It is highly recommended that a PlanetTogether support engineer assists you with this process
In your Publish database there is table titled SystemData. This table contains three columns:
- Version - Your APS version number
- ValidateDB - When marked as True, the publish routine checks the database before trying to push data to see if all tables being published match what is in the database
- PrepareData - When marked as True, the publish routine will check the data types of the fields in the publish tables and validate that they match the data being pushed before running the publish
In order to allow the automatic creation of the UDF column, you will need to set the ValidateDB field in the SystemData table to True. This may also cause your publish to run longer than expected, as the system needs to validate every column in every table that is being published, so it is not recommended to leave set to True after the UDF column is created. Another thing to note is that if the UDF column is ever removed, if the ValidateDB field is set to True, the column will be removed from the table.