SpyriadisNetworkBlob

Category: SQL Server

  • 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 Clear Old Document Versions To Decrease Database Size And Improve Performance

    Recently I had a problem with an Umbraco 4.7.1 installation that had became 10GB in SQL Server and had about 20000 published pages. Since there is no way to do something like this from the umbraco administration panel I had to create a T-SQL to clear the database.

    DECLARE @createdDate Datetime = '2012-03-01'

DELETE FROM cmsPropertyData WHERE
	versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
	contentNodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsPreviewXml WHERE
	versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
	nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsContentVersion WHERE
	versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
	ContentId  IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsDocument WHERE
	versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
	nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)

    Warning if you do not have any TSQL experience be careful because you could easily delete all the data in your database. Always take a backup before making any changes this way.

  • Umbraco Fix – Empty recycle bin using SQL if backend fails

    Updated thanks to jeffreypriebe

    -- Uncomment below to verify the number of nodes returned is the same as the number of nodes that is in the Recycle Bin
-- SELECT * FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20

--Setup the temporary table
CREATE TABLE #temp ([id] int);
INSERT #temp select id from umbracoNode where path like '%-20%' and id!=-20

-- Delete all 'related' nodes and table contents...
DELETE FROM cmsPreviewXml WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsContentVersion WHERE contentId IN (SELECT id FROM #temp)
DELETE FROM cmsDocument WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsContentXML WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsContent WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsPropertyData WHERE contentNodeId IN (SELECT id FROM #temp)
DELETE FROM umbracoUser2NodePermission WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM umbracoRelation  where parentId in (select id from #temp) or childId in (select id from #temp)

-- remove fk constraint so that umbracoNode can delete
ALTER TABLE umbracoNode DROP CONSTRAINT FK_umbracoNode_umbracoNode
-- delete the XML nodes...
DELETE FROM umbracoNode WHERE id in (SELECT id FROM #temp)
-- re add fk constraint to umbracoNode
ALTER TABLE umbracoNode WITH NOCHECK ADD CONSTRAINT FK_umbracoNode_umbracoNode
FOREIGN KEY (parentId)
REFERENCES umbracoNode (id)

--Cleanup temp
DROP TABLE #temp;

  • Check replication synchronization status using Transactional SQL (TSQL)

    Below you will find a select that will help you check the state of all your publication on SQL server in order to know if all the subscribers are in synch or the syncronization agent has stopped running.

    SELECT
(CASE
    WHEN mdh.runstatus =  '1' THEN 'Start - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '2' THEN 'Succeed - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '3' THEN 'InProgress - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '4' THEN 'Idle - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '5' THEN 'Retry - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '6' THEN 'Fail - '+cast(mdh.runstatus as varchar)
    ELSE CAST(mdh.runstatus AS VARCHAR)
END) [Run Status],
mda.subscriber_db [Subscriber DB],
mda.publication [PUB Name],
right(left(mda.name,LEN(mda.name)-(len(mda.id)+1)), LEN(left(mda.name,LEN(mda.name)-(len(mda.id)+1)))-(10+len(mda.publisher_db)+(case when mda.publisher_db='ALL' then 1 else LEN(mda.publication)+2 end))) [SUBSCRIBER],
CONVERT(VARCHAR(25),mdh.[time]) [LastSynchronized],
und.UndelivCmdsInDistDB [UndistCom],
mdh.comments [Comments],
'select * from distribution.dbo.msrepl_errors (nolock) where id = ' + CAST(mdh.error_id AS VARCHAR(8)) [Query More Info],
mdh.xact_seqno [SEQ_NO],
(CASE
    WHEN mda.subscription_type =  '0' THEN 'Push'
    WHEN mda.subscription_type =  '1' THEN 'Pull'
    WHEN mda.subscription_type =  '2' THEN 'Anonymous'
    ELSE CAST(mda.subscription_type AS VARCHAR)
END) [SUB Type],

mda.publisher_db+' - '+CAST(mda.publisher_database_id as varchar) [Publisher DB],
mda.name [Pub - DB - Publication - SUB - AgentID]
FROM distribution.dbo.MSdistribution_agents mda
LEFT JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
JOIN
    (SELECT s.agent_id, MaxAgentValue.[time], SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB
    FROM distribution.dbo.MSrepl_commands t (NOLOCK)
    JOIN distribution.dbo.MSsubscriptions AS s (NOLOCK) ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
    JOIN
        (SELECT hist.agent_id, MAX(hist.[time]) AS [time], h.maxseq
        FROM distribution.dbo.MSdistribution_history hist (NOLOCK)
        JOIN (SELECT agent_id,ISNULL(MAX(xact_seqno),0x0) AS maxseq
        FROM distribution.dbo.MSdistribution_history (NOLOCK)
        GROUP BY agent_id) AS h
        ON (hist.agent_id=h.agent_id AND h.maxseq=hist.xact_seqno)
        GROUP BY hist.agent_id, h.maxseq
        ) AS MaxAgentValue
    ON MaxAgentValue.agent_id = s.agent_id
    GROUP BY s.agent_id, MaxAgentValue.[time]
    ) und
ON mda.id = und.agent_id AND und.[time] = mdh.[time]
where mda.subscriber_db<>'virtual' -- created when your publication has the immediate_sync property set to true. This property dictates whether snapshot is available all the time for new subscriptions to be initialized. This affects the cleanup behavior of transactional replication. If this property is set to true, the transactions will be retained for max retention period instead of it getting cleaned up as soon as all the subscriptions got the change.
--and mdh.runstatus='6' --Fail
--and mdh.runstatus<>'2' --Succeed
order by mdh.[time]

  • Change the SQL Server Name after Changing the Computer Name

    Recently I changed the name of my SQL Server computer from the generic one given by the Windows installation to ‘SERVER’. In the beggining there was no apparent problem in the connections since I used IP addresses. After a while I wanted to use Replication and tried to create an SQL Server Publication only to find that the name of the SQL SERVER has not changed and I could not create a publication unless I connected with the correct computer name. It turns out that changing the computer name does not update the sql server name and you need to change it manually in order to be able to use replication or any other Sql Server feature that requires you to connect using the computer name!

    Below is a simple code that you can use to change the name of the SQL Server.

    sp_dropserver @@SERVERNAME
GO
sp_addserver 'newServerName', local
GO

    You need to restart the SQL Server in order for the changes to take effect.

  • 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');

  • Convert a comma separated nvarchar to a list that can be used in any T-SQL

    Many times in many projects I had to filter some records in a list by selecting not one but multiple categories. This has been a nightmare to be done in a stored procedure since you had to have a limited numbers of parameters and it would be inefficient to have @category1, @category2, etc. What you wanted to do is have one value that you would name @categories and then pass there any number of values separated by a character.

    Now this can be achieved by using an intermediate stored procedute that converts a string delimited by a char to a table list of values. This stored procedure is the following:

    CREATE FUNCTION dbo.spListToTable
(
	@List VARCHAR(MAX),
	@Delim CHAR
) RETURNS @ParsedList TABLE ( item VARCHAR(MAX) )
AS
BEGIN
	DECLARE @item VARCHAR(MAX), @Pos INT

	SET @List = LTRIM(RTRIM(@List))+ @Delim
	SET @Pos = CHARINDEX(@Delim, @List, 1)

	WHILE @Pos > 0
	BEGIN
		SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
		IF @item <> ''
		BEGIN
			INSERT INTO @ParsedList (item)
			VALUES (CAST(@item AS VARCHAR(MAX)))
		END

		SET @List = RIGHT(@List, LEN(@List) - @Pos)
		SET @Pos = CHARINDEX(@Delim, @List, 1)
	END
	RETURN
END
GO

    Then in an example as the one described above you would use it like this :

    SELECT *
FROM [tblList]
WHERE [categoryID] IN (
	SELECT [item] FROM dbo.spListToTable('1,2,3',',')
)

    That would return you all the items that are in categories 1,2,3 !

  • Calculate Distance Between Locations in SQL Server 2008 using Geography

    Ever had a table in SQL Server with hundreds of locations with their longitude, latitude and wanted to retrieve only top 10 which are nearest to you or those that are within couple of Kilometers away? To do so in SQL Server 2005 you needed to convert long,lat into Radians and then make a large amount of calculations to do so.

    Old way:

    CREATE FUNCTION [dbo].[LatLonRadiusDistance]
(
	@lat1Degrees decimal(15,12),
	@lon1Degrees decimal(15,12),
	@lat2Degrees decimal(15,12),
	@lon2Degrees decimal(15,12)
)
RETURNS decimal(9,4)
AS
BEGIN

	DECLARE @earthSphereRadiusKilometers as decimal(10,6)
	DECLARE @kilometerConversionToMilesFactor as decimal(7,6)
	SELECT @earthSphereRadiusKilometers = 6366.707019
	SELECT @kilometerConversionToMilesFactor = .621371

	-- convert degrees to radians
	DECLARE @lat1Radians decimal(15,12)
	DECLARE @lon1Radians decimal(15,12)
	DECLARE @lat2Radians decimal(15,12)
	DECLARE @lon2Radians decimal(15,12)
	SELECT @lat1Radians = (@lat1Degrees / 180) * PI()
	SELECT @lon1Radians = (@lon1Degrees / 180) * PI()
	SELECT @lat2Radians = (@lat2Degrees / 180) * PI()
	SELECT @lon2Radians = (@lon2Degrees / 180) * PI()

	-- formula for distance from [lat1,lon1] to [lat2,lon2]
	RETURN ROUND(2 * ASIN(SQRT(POWER(SIN((@lat1Radians - @lat2Radians) / 2) ,2)
        + COS(@lat1Radians) * COS(@lat2Radians) * POWER(SIN((@lon1Radians - @lon2Radians) / 2), 2)))
        * (@earthSphereRadiusKilometers * @kilometerConversionToMilesFactor), 4)

END

    Now on SQL Server 2008 which is location aware this is much more efficient and easy to do using geography. Lets say that you have a table with locations called tblLocations which has stored in two columns the longitude(location_longitude), latitude(location_latitude) and you want to calculate the distance of those from a point.

    All you have to do is:

    CREATE PROCEDURE [dbo].[spGetNearLocations]
	@latitude decimal(18,14),
	@longtitude decimal(18,14)
AS
BEGIN
	SET NOCOUNT ON;
	-- @p1 is the point you want to calculate the distance from which is passed as parameters
	declare @p1 geography = geography::Point(@latitude,@longtitude, 4326);

	SELECT *
		,@p1.STDistance(geography::Point([location_latitude], [location_longitude], 4326)) as [DistanceInKilometers]
	FROM [tblLocations]
END

    Of course since you can have a column already defined in SQL Server 2008 with the geography of the location(location_geography) you can more efficiently have:

    CREATE PROCEDURE [dbo].[spGetNearLocations]
	@latitude decimal(18,14),
	@longtitude decimal(18,14)
AS
BEGIN
	SET NOCOUNT ON;
	-- @p1 is the point you want to calculate the distance from which is passed as parameters
	declare @p1 geography = geography::Point(@latitude,@longtitude, 4326);

	SELECT *
		,@p1.STDistance([location_geography]) as [DistanceInKilometers]
	FROM [tblLocations]
END

  • LINQ to SQL Get DateTime from Sql Server (getDate)

    Ever wanted to get the current time and date from the SQL Server to avoid different times between yous server and the clients (for example having a centralized application running on many countries) ?

    Using LINQ to SQL there is no way to get the time from the database build in the class. What you need to do is write your own code as the example below:

        using System.Data.Linq;
    using System.Data.Linq.Mapping;
    using System.Data;
    using System.Collections.Generic;
    using System.Reflection;
    using System.Linq;
    using System.Linq.Expressions;
    using System.ComponentModel;
    using System;

    partial class dbGTOnlineDataContext
    {
        [Function(Name = "GetDate", IsComposable = true)]
        public DateTime GetSystemDate()
        {
            MethodInfo mi = MethodBase.GetCurrentMethod() as MethodInfo;
            return (DateTime)this.ExecuteMethodCall(this, mi, new object[] { }).ReturnValue;
        }
    }

    Then anywhere in your code you can simple use the code below to get the time from the SQL Server database:

    myDatabaseDataContext db = new myDatabaseDataContext();
DateTime dtNow = db.GetSystemDate();