SpyriadisNetworkBlob

Tag: TSQL

  • Umbraco Xml Exception – Error Publishing Invalid character

    A general problem that I recently encountered for the second time is an Umbraco site not being able to generate any page without displaying an error or timeout. After examining the log (umbracoLog table on SQL server) you will see the error below:

      Error Republishing: System.Xml.XmlException: '', hexadecimal value 0x1F, is an invalid character. Line 1, position 2166.
   at System.Xml.XmlTextReaderImpl.Throw(String res, String[] args)
   at System.Xml.XmlTextReaderImpl.ParseCDataOrComment(XmlNodeType type, Int32& outStartPos, Int32& outEndPos)
   at System.Xml.XmlTextReaderImpl.ParseCDataOrComment(XmlNodeType type)
   at System.Xml.XmlTextReaderImpl.ParseElementContent()
   at System.Xml.XmlLoader.LoadNode(Boolean skipOverWhitespace)
   at System.Xml.XmlLoader.LoadDocSequence(XmlDocument parentDoc)
   at System.Xml.XmlDocument.Load(XmlReader reader)
   at System.Xml.XmlDocument.LoadXml(String xml)
   at umbraco.content.LoadContentFromDatabase()

    This means that somehow (usually if you copy/paste text from other sources directly to the editor) an invalid character has been inserted on a node. That invalid character prevents the XML document created by Umbraco to be loaded and therefore no pages can be generated. To find which node has the error you can use the code below:

    DECLARE @invalidCharacter nvarchar(100) = convert(nvarchar(100), 0x1F)

SELECT * FROM [cmsContentXML]
WHERE
	xml like '%'+@invalidCharacter+'%'

    Now that you know where the problematic character is you can replace it either using Umbraco administrator or using the T-SQL below:

    DECLARE @invalidCharacter nvarchar(100) = convert(nvarchar(100), 0x1F)

UPDATE [cmsPropertyData] SET
	[dataNvarchar] = REPLACE(cast([dataNvarchar] as nvarchar(max)),@invalidCharacter,''),
	[dataNtext] = REPLACE(cast([dataNtext] as nvarchar(max)),@invalidCharacter,'')
WHERE
	[dataNvarchar] like '%'+@invalidCharacter+'%' OR
	[dataNtext] like '%'+@invalidCharacter+'%'

UPDATE [cmsContentXML] SET
	[xml] = REPLACE(cast([xml] as nvarchar(max)),@invalidCharacter,'')
WHERE
	[xml] like '%'+@invalidCharacter+'%'

    Depending on your error you need to change the hexadecimal value that creates the problem. In my case and the examples above I have used “0x1F“. This value is at the first line of the error message “.. , hexadecimal value 0x1F, …”.

     

  • Umbraco Fix – Content tree not loaded on very large sites

    Recently one of my sites that was created using umbraco has started to behave somewhat strange. There was nothing that seem to be wrong and all the errors produced were:
     No Document exists with Version ‘00000000-0000-0000-0000-000000000000’

    As it turned out the problem was due to extreme delay in the SQL Server. What made me look  into that was that the tree in the content area of the backend was not loading for the folder with all the articles in it.

    Also it seemed that the problem might have originated because I also use DateFolders extention. Adding the index below dramatically increased the speed of the sql that runs to generate the content trees (from 32sec to less that 2sec for more than 7000 records) and it seems to resolved all my other problems as well.

    /****** Object:  Index [IDX_cmsDocument_nodeId_versionId]    Script Date: 09/20/2011 13:13:20 ******/
CREATE NONCLUSTERED INDEX [IDX_cmsDocument_nodeId_versionId] ON [cmsDocument]
(
	[nodeId] ASC,
	[versionId] ASC
)
INCLUDE ( [published]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
    S3GCC5KF6B2M

  • Alter Schema Owner at Tables, Views and Stored Procedures

    Here is a quick and easy way to alter the schema of any of the objects in your sql server that has been created with any other user than the dbo. This has been giving me troubles when using TSQL and has been difficult to change it using the sql managment studio.

    SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name
FROM sys.tables
WHERE schema_id != SCHEMA_ID('dbo');

  • Umbraco Fix – No Document exists with Version

    Recently I had problems with a site I have created using umbraco. The problem was that for some reason in the version control of the application there were many orphan versions that where visible on the cmsContentVersion table but not on the cmsDocument. You can find those using the query below:

    SELECT * FROM cmsContentVersion
WHERE
	cmsContentVersion.VersionId NOT IN (SELECT VersionId FROM cmsDocument) AND
	cmsContentVersion.ContentId IN (SELECT nodeId FROM cmsDocument)

    Deleting those record fixed my problem and the problematic nodes where shown again in both the content tree view and edit view in umbraco CMS. To delete those versions in your database use the code below:

    DELETE FROM cmsContentVersion
WHERE
	cmsContentVersion.VersionId NOT IN (SELECT VersionId FROM cmsDocument) AND
	cmsContentVersion.ContentId IN (SELECT nodeId FROM cmsDocument)