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 must 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 to 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) Would you 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 the 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 published database, which can only be done within the SQL server.
Note: It is highly recommended that a PlanetTogether support engineer assists you with this process
In your Publish database, there is a table titled SystemData. This table contains three columns:
- Version - Your PlanetTogether 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 publishing 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
To automatically create the UDF column, you will need to set the ValidateDB field in the SystemData table to True. However, this may also cause you to 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 the set to True after the UDF column is created. Another thing to note is that if the UDF column is removed, the column will be removed from the table if the ValidateDB field is set to True.