Component Presentations in SQL 2012 DB shows up as a different encoding with 2013

Question

I'm publishing Component Presentations to the Content Delivery DB and it shows up as a different encoding only in the content field of the COMPONENT_PRESENTATIONS table.

The content field in the PAGE_CONTENT table shows up correctly. Has anyone seen this before and/or have a fix?

I'm running Tridion 2013 on Windows Server 2012 with SQL Server 2012 SP1.

Answer 1

As @Likhan is mentioning in his answer, it seems that the information is saved in Unicode/UTF16 so it needs to be decoded using other Encoding like iso-8859-1.

I developed a workaround for it developing a SQL CLR Stored Procedure

SQL CLR STORED PROCEDURE

public partial class StoredProcedures {
[SqlProcedure]
public static void GetComponentPresentationContent(SqlInt32 publicationId, SqlInt32 componentId, SqlInt32 templateId) {
    using (SqlConnection connection = new SqlConnection("context connection=true")) {
        try {
            connection.Open();

            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText =
                string.Format("SELECT CONTENT FROM COMPONENT_PRESENTATIONS WHERE PUBLICATION_ID = {0} AND COMPONENT_ID = {1} AND TEMPLATE_ID = {2}",
                    publicationId, componentId, templateId);

            SqlDataReader reader = command.ExecuteReader();
            if (reader.HasRows) {
                SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("CONTENT", SqlDbType.VarChar, -1) });
                SqlContext.Pipe.SendResultsStart(record);

                while (reader.Read()) {
                    string content = reader.GetString(0);

                    using (MemoryStream ms = new MemoryStream()) {
                        byte[] encoded = Encoding.Unicode.GetBytes(content);
                        ms.Write(encoded, 0, encoded.Length);

                        ms.Seek(0, SeekOrigin.Begin);
                        StreamReader tr = new StreamReader(ms, true);
                        Encoding targetEncoding = Encoding.GetEncoding("iso-8859-1");

                        content = targetEncoding.GetString(ms.ToArray());
                        record.SetSqlString(0, content);
                        SqlContext.Pipe.SendResultsRow(record);
                    }
                }

                SqlContext.Pipe.SendResultsEnd();
            }
        }
        catch (Exception ex) {
            throw ex;
        }
        finally {
            if (connection.State != ConnectionState.Closed) {
                connection.Close();
            }
        }
    }
}
}

SQL ASSEMBLY CREATION

CREATE ASSEMBLY [TridionBroker]
FROM '[YOUR ASSEMBLY PATH]'

SQL STORED PROCEDURE CREATION

CREATE PROCEDURE GetComponentPresentationContent
    @PUBLICATIONID AS INT, 
    @COMPONENTID AS INT, 
    @TEMPLATEID AS INT
AS
EXTERNAL NAME [TridionBroker].StoredProcedures.GetComponentPresentationContent

TEST

EXEC GetComponentPresentationContent 11, 190, 200

It will return the component presentation in a readable format from your database. I hope this helps.

Answer 2

As far as I know since Tridion 2013 the encoding in database for content field has changed for mssql. You no longer will be able to do select on database to see the content anymore. I have seen around floating somewhere a query which can decode it for human readable view. Will try to look it up. However, even if the content cannot be seen properly in database it should be proper using broker api and/or odata webservices, so, end result should be fine.

UPDATE

To get content from COMPONENT_PRESENTATION table you can use following C# Expression in LINQPad

from c in COMPONENT_PRESENTATIONS
where c.COMPONENT_ID == 11567 
      let buf = Encoding.Unicode.GetBytes(c.CONTENT)
      select Encoding.UTF8.GetString(buf)

Or if the Default Code Page of the Publication Target is set to Unicode then in database it should also be human readable, as Content Delivery DB is using UTF16.

Answer 3

You can get readable content from the COMPONENT_PRESENTATION table directly in sql by double converting the content field like this:

SELECT 
  CONVERT(varchar(max),CONVERT(varbinary(max),content)) 
  FROM COMPONENT_PRESENTATIONS
  WHERE <where_clause>