Kentico 10 Export: "Conversion failed when converting from a character string to uniqueidentifier"

Christopher Conley asked on October 12, 2017 01:15

Hello,

We are running into an issue when attempting to export a Kentico 10 web site. The symptoms are identical to what is described here:

https://devnet.kentico.com/articles/conversion-failed-when-converting-from-a-character-string-to-uniqueidentifier

We determined that there was two field names that were shared between custom objects by executing the following SQL query (pulled from the above article) against the database:

 SELECt name, count(*) as countX
FROM
(
Select  s.name
    ,sh.name+'.'+o.name AS ObjectName
    ,o.type_desc AS ObjectType
    ,CASE
         WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
         WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
        WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
         ELSE t.name
     END AS DataType

    ,CASE
         WHEN s.is_nullable=1 THEN 'NULL'
        ELSE 'NOT NULL'
    END AS Nullable
    ,CASE
         WHEN ic.column_id IS NULL THEN ''
         ELSE ' identity('+ISNULL(CONVERT(varchar(10),ic.seed_value),'')+','+ISNULL(CONVERT(varchar(10),ic.increment_value),'')+')='+ISNULL(CONVERT(varchar(10),ic.last_value),'null')
     END
    +CASE
         WHEN sc.column_id IS NULL THEN ''
         ELSE ' computed('+ISNULL(sc.definition,'')+')'
     END
    +CASE
         WHEN cc.object_id IS NULL THEN ''
         ELSE ' check('+ISNULL(cc.definition,'')+')'
     END
        AS MiscInfo
FROM sys.columns                           s
    INNER JOIN sys.types                   t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
    INNER JOIN sys.objects                 o ON s.object_id=o.object_id
    INNER JOIN sys.schemas                sh on o.schema_id=sh.schema_id
    LEFT OUTER JOIN sys.identity_columns  ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
    LEFT OUTER JOIN sys.computed_columns  sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
    LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
    WHERE o.name LIKE 'custom_%' and o.name not like 'customtable%'
    ) as X
    group by name
order by countX desc

We promptly changed the field names to absolve the conflict. However, the "Conversion failed when converting from a character string to uniqueidentifier" error persists. What are we missing here?

Thanks so much for your assistance.

Correct Answer

Matt Nield answered on October 12, 2017 10:34

Do you have nay custom objects that are not prefixed with 'custom_'. Typically, we prefix with a short version of our company name. It may be worth modifying the query to cature more items in the system.

1 votesVote for this answer Unmark Correct answer

Recent Answers


Juraj Ondrus answered on October 12, 2017 13:24

Matt is right - as it is also described in the article, the sample script checks given prefixes only - you need to change it according your custom page type table names. Or, other option is to use the KInspector - the module in this tool is made more generic and dynamic.

0 votesVote for this answer Mark as a Correct answer

Christopher Conley answered on October 12, 2017 16:30

Thank you, gentlemen! You were absolutely right. It turns out one of our developers based a page type off of an existing CMS pagetype and modified it's field properties without changing the name. They also failed to follow the "custom" naming convention for the table.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.