Publishing troubleshooting for iMIS EMS on premise

Case Scenario: On-demand publishing is not working for your iMIS EMS instance.

Audience: iMIS EMS (20.3.xx) on-premise users (not hosted by ASI)

If you recently upgraded to iMIS EMS and are using on-premise hosting, but are experiencing issues with publishing, please consider the following recommendations.

Clearing the Task Queue of old items

Check the task queue to determine if there are old items still in the queue. If there are older items in the task queue, you must remove them. To begin, run the following:

select * from TaskQueuePublishDetail
select * from TaskQueue
select * from TaskQueueTriggerDetail
select * from PublishMessageLog
select * from PublishRequestDetail
select * from PublishRequest
select * from TaskQueueAdvancedEmailEventDetail
select * from TaskQueueAdvancedEmailSendDetail

If there are results, especially data from previous years (old items), you must remove those items.

Do the following to remove these items from the task queue:

1. Back up the iMIS database.
2. Download TaskQueueFlushOldTasks.sql and PurgePublishRequestTaskDetailTables.sql from this article.
3. Run TaskQueueFlushOldTasks.sql. This will remove old tasks and publish entries but nothing recent.
4. If publishing issues still persist, run PurgePublishRequestTaskDetailTables.sql. This will actively purge all Publish Queue and Task Queue tables. This does not purge all history of task and publish requests.

Updating Documents with the same status code and DocumentVersionKey

Documents should not have the same Status Code and DocumentVersionKey. To identify if you have any documents with the same Status Code and DocumentVersionKey, run the following:

SELECT DB_NAME() AS [DBName],
[DocumentVersionKey], [DocumentStatusCode], MAX([DocumentTypeCode]) AS DocumentTypeCode, COUNT(DocumentVersionKey) AS [VersionCount]
FROM [dbo].[DocumentMain]
WHERE [DocumentStatusCode] <> 50
GROUP BY [DocumentVersionKey], [DocumentStatusCode]
HAVING COUNT([DocumentVersionKey]) > 1
ORDER BY [VersionCount] DESC

If there are results, do the following:

1. Add the DocumentVersionKey(s) from the above results to the following by replacing XXX with each returned DocumentVersionKey, then run the script:

   SQL

   Select * from DocumentMain where DocumentVersionKey IN ('XXX', 'XXX')

   📘

   Note

   If there was only one DocumentVersionKey returned, change IN to =.

2. Review the list, then identify which document was updated the longest ago (UpdatedOn property).

3. Once you have identified which document was updated the longest ago, update the oldest one to a StatusCode of 50 (archived) using the following:

   SQL

   Update DocumentMain set DocumentStatusCode = '50' where DocumentKey = 'XX'

Ensuring there are no system business objects unpublished

All system business objects must be published. Run the following to determine if there are any iMIS system business objects that are not published:

Select * from DocumentMain where IsSystem = '1' and DocumentStatusCode < '40'
and DocumentTypeCode = 'BOD'

If there are any results, you must publish all business objects. Do the following to identify all unpublished BOs:

1. From the Staff site, go to RiSE > Dashboard.
2. Click the Business objects tab.
3. From the Select a query drop-down, choose Working.
4. Open each business object individually and click Publish.

Validating there is only one publishing server being used

In iMIS EMS, there can only be one publishing server. Run the following to determine if there is more than one publishing server being used:

Select * from PublishServerRef

This should only return one row for the A publish server code. You can delete publishing servers from RiSE > Maintenance > Publishing servers.

Remaining steps

Once the above is validated and fixed as necessary, reset IIS on the server. Ensure on-demand publishing is now working.