Blog

Recently a client came to me with a problem where Publisher Explorer was freezing up on them while they were attempting to edit a particular content item. The first thing that we tried was to copy the content item to see if the copy was still behaving badly. It was and  the copy of the content item was also causing Publisher Explorer to freeze (we copied the problem over as well...um duh?). While tinkering, I did notice that the content item would open just fine and that you could scroll up and down without issue, that is until you focused on a particular field. In this case it was "body text" which contained an HTML table with some hyperlinks to some documents. This led me to believe that there was something not quite right with the content stored in this field. The client could have deleted the content item and started over recreating any links pointing to it, but that was not something they wanted to do without first attempting to troubleshoot the problem.

I decided to take a peek directly into the Publisher database to dig up the HTML content that lived in the "body text "  field for that content item.  The Publisher database is not structured in the most intuitive manner. Drilling down to a given field within a content item requires several steps. Anyhow so I thought to myself...we're looking for a particular content item, let's start with inspecting the PCSCONTENTITEMS table within the Publisher database right? NOT! The first table you'll need to inspect is called PCSDIRECTORY. You can do this by running the following SQL query:

SELECT [DIRECTORYID]
,[FOLDERID]
,[ITEMTYPE]
,[ITEMNAME]
FROM [PublisherDB].[PCSDIRECTORY]
WHERE [FOLDERID]=<PARENT FOLDER's DIRECTORYID>

The FOLDERID column contains the DIRECTORYID of the folder or content item's parent.

To get all of the folders and content items within the root of your Publisher hierarchy replace <PARENT FOLDER's DIRECTORYID> with the value 1000 and run the sql query. To drill down further, to the content item in question, simply replace <PARENT FOLDER's DIRECTORYID> (which should now be 1000) with the [DIRECTORYID] for the folder that's one level closer to the content item you're interested in inspecting. Continue running the SQL in this manner until you have located the content item in question. It's just like expanding and drilling down in Publisher Explorer except you're doing it with SQL.

Once you have located the final [DIRECTORYID] for the content item in question jot down it's value (DIRECTORYIDs exist for BOTH Folders and content items in the [PCSDIRECTORY] table! Is that confusing enough for you?). You can run the following SQL query to retrieve all of the data for the SINGLE content item ordered in the sequence in which they appear on screen  in the "properties" tab of the Content Item Editor:

SELECT [ITEMID]
,[ITEMPOSITION]
,[PROPERTYID]
,[PROPERTYTYPE]
,[SHORTVALUE]
,[LONGVALUE]
,[REVISION]
,[LOADID]
FROM [PublisherDB].[PCSVALUES]
WHERE [ITEMID]=<final DIRECTORTYID from previous exercise>
ORDER BY [ITEMPOSITION] ASC

In Content Item Editor for this particular content item, the "body text" field is the 9th "properties" field from the top down or stated another way, it's the row from the previous sql result set with [ITEMPOSITION]=8 (Start your index count with the value zero beginning at the second field in "properties" ie skip "Content Item Name"). The values for the actual content related to this property will be present in the [SHORTVALUE] or [LONGVALUE] column depending on the data type. In the case of rich_text, as was my case, the corrupt  HTML was found in the [LONGVALUE] column. A simple copy and paste of the HTML content from the DB query tool into a text editor revealed pages of strange characters (yes pages with an s!) that Function1's enhanced rich text editor could not handle causing the browser to have a fit. A simple SQL statement to replace the HTML content minus the strange characters was all that was required to prevent Publisher Explorer from going into a tail spin. Hopefully you don't have to directly manipulate Publisher content like this, but if you do, now you know and knowing is (more than) half the battle.

-Mitul