Blog

Umbraco IP2Location Integration Package
Recently I found a package that would help people that want to separate content of their site depending on the geografical location. It will allow you to distinguise your visitors location and therefore the content they will see.

It can be user to differentiate or hide content depending on the users location.Let’s for example say that you have an eshop and want to promote different products to different countries this package will allow you to filter your products by country.

An IP2Location database is required for this package to function properly which can be found on any of the sites below:
A Database with a table filled using any of the geolocation database above is required!

There is a trial mode that can be used on by having a sub-domain starting with development, dev, stage, test or demo.

You can download the package and see more details here.
October 11, 2011
Win a lifetime Dropbox Pro account for life ($99/year)

I believe you all know dropbox and most of you i’m sure are using it as a free account.
Now there is a chance to win a Pro subscription for life!!! All you have to do is enter your email at the contest below…

http://appsumo.com/ (Expires 10/13/2011)

Good luck to you all…

October 7, 2011
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
September 20, 2011
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');
```
August 8, 2011
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)
```
August 8, 2011
XSLTSearch extension error when saving XSLT file in Umbraco 4.6 beta
If you have problems when using XSLTsearch extension for umbraco on version 4.6 installation the easiest way to resolve it is to simply complile the .cs file to a .dll using Visual Studio and then declare it as an xslt extension using the line below:
```
<ext assembly="XSLTsearch" type="PS.XSLTsearch" alias="PS.XSLTsearch" />
```
To make this even easier for you here is the .dll you need.
April 4, 2011
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 !
December 8, 2010
MobileMe free alternative with Google

I have been using MobileMe and paying 79$ for a couple of years now. Couple of days ago I show an article saying that you could sync all your Contact,Calendar and Notes with Gmail. This is done by connecting your iPhone to Gmail as an Exchange Server instead of as a regular Gmail Account.

Before continuing further you should backup your iPhone since in many steps you will be prompted to delete all your contacts locally from the iPhone and you should do so!

The first thing you need to do is connect your iPhone to your PC and sync your contacts with Google. After doing so you should be able to see all your contacts through Gmail. You must verify that the number of contacts match in order to be sure not to lose any contact. Having your contacts safe on Gmail and on your iTunes backup you remove any gmail account from the iPhone and setup a new one but instead of selecting Gmail Account you choose Microsoft Exchange. Give your Gmail credentials and when prompted for a server use m.google.com. Then select all three possible syncs (Mail,Contacts,Calendar) and continue. When prompted to delete all your contacts from your iPhone you should do so to avoid duplicate values.

That’s all! You are now synced with Google!

November 17, 2010
Convert UTF-8 string To ASCII using C#
Ever wanted to convert a string from UTF-8 to ASCII in order to use it in a text file or to post it on another on another website? Below you will find a static function that you can use from anyware in your Visual Studio solution to convert a string from UTF-8 to ASCII. It takes as a parameter the UTF-8 string and return the same string in ASCII.
```
public static string UTF8toASCII(string text)
{
	System.Text.Encoding utf8 = System.Text.Encoding.UTF8;
	Byte[] encodedBytes = utf8.GetBytes(text);
	Byte[] convertedBytes =
			Encoding.Convert(Encoding.UTF8, Encoding.ASCII, encodedBytes);
	System.Text.Encoding ascii = System.Text.Encoding.ASCII;

	return ascii.GetString(convertedBytes);
}
```
August 4, 2010

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

July 29, 2010