SQL Server 7 comments

Removing Null Characters (0x00) From Values in SQL Server

By on July 4th, 2012

I recently came across a problem while crawling a BCS data source in SharePoint 2010. Most of the 130,000 records were getting indexed, but I was getting an error on 25 records:

Error while crawling LOB contents. ( ‘.’, hexadecimal value 0x00, is an invalid character. Line 25, position 10. )

When I first took a look at the offending records in the database I couldn’t see any actual problems.  The issue was that the null character in the data was being treated as a string terminator, so it was not being returned with a standard select query. I was finally able to detect the null values by casting the suspected offending column to varbinary:


SELECT CAST(TheColumn AS varbinary(max)) FROM TheTable WHERE TheID = 123

Casting to varbinary returns the character values in the column as hexadecimal.  Once the values were converted to hexadecimal I could see the null values represented as 0000 (In my case the offending column was nvarchar, if your column is varchar you’ll be looking for 00).

Now that I could see the problem, I needed a way to quickly and easily remove the characters from any records that contained them in the database. I found a post on StackOverflow that finally pointed me in the right direction:

http://stackoverflow.com/questions/2828333/what-is-the-null-character-literal-in-tsql

Simply searching for char(0) didn’t return any values in my case. However if the data is cast from nvarchar to varchar and the collation is set to ‘SQL_Latin1_General_CP1_CI_AS’ it does work. Note that the cast to varchar was required for the null character to be detected. Luckily in my case the offending records didn’t contain Unicode characters.  Here’s what I finally ended up with to search for and remove the null characters:


UPDATE TheTable
SET TheColumn = REPLACE(CAST(TheColumn as varchar(max)) COLLATE SQL_Latin1_General_CP1_CI_AS, CHAR(0), '')
WHERE CAST(TheColumn as varchar(max)) COLLATE SQL_Latin1_General_CP1_CI_AS like '%' + CHAR(0) + '%'
HTML, CSS & Javascript 7 comments

Encode h.264 and WebM videos for MediaElement.js using FFmpeg

By on June 14th, 2012

I recently added a video player to a client’s site. I found John Dyer’s MediaElement.js to be an excellent solution for doing this. As long as you provide both an h.264 and WebM encoded version of the video, it will play natively on almost all browsers. For unsupported browsers it will fall back to Flash.

The client’s videos were all wmv’s, so they would need to be converted to h.264 and WebM. Luckily John also provided some directions for encoding to these formats using FFmpeg:

http://johndyer.name/ffmpeg-settings-for-html5-codecs-h264mp4-theoraogg-vp8webm/

Unfortunately FFmpeg has changed since the commands were published, so some slight modifications were required. I also made some modifications so that the aspect ratio of the video was preserved and to encode the video at a lower bit rate and faster speed.  As well, some of the videos being converted were really short, and would be finished before the 10 second mark that the thumbnail is created at. To solve this problem I modified the script to attempt to capture the thumbnail at the 1, 2, 3, 5 and 10 second mark – with each successful capture overwriting the last.

Here’s the updated batch file that I used:


REM mp4 (H.264 / AAC)
"c:\program files\ffmpeg\bin\ffmpeg.exe" -y -i %1 -vcodec libx264 -pix_fmt yuv420p -vprofile high -preset fast -b:v 500k -maxrate 500k -bufsize 1000k -vf scale=trunc(oh*a/2)*2:480 -threads 0 -acodec libvo_aacenc -b:a 128k %1.mp4

REM webm (VP8 / Vorbis)
"c:\program files\ffmpeg\bin\ffmpeg.exe" -y -i %1 -vcodec libvpx -quality good -cpu-used 5 -b:v 500k -maxrate 500k -bufsize 1000k -vf scale=trunc(oh*a/2)*2:480 -threads 0 -acodec libvorbis -f webm %1.webm

REM jpeg (screenshot at 10 seconds, but just in case of a short video - take a screenshot earlier and overwrite)
"c:\program files\ffmpeg\bin\ffmpeg.exe" -y -i %1 -ss 1 -vframes 1 -r 1 -vf scale=trunc(oh*a/2)*2:480 -f image2 %1.jpg
"c:\program files\ffmpeg\bin\ffmpeg.exe" -y -i %1 -ss 2 -vframes 1 -r 1 -vf scale=trunc(oh*a/2)*2:480 -f image2 %1.jpg
"c:\program files\ffmpeg\bin\ffmpeg.exe" -y -i %1 -ss 3 -vframes 1 -r 1 -vf scale=trunc(oh*a/2)*2:480 -f image2 %1.jpg
"c:\program files\ffmpeg\bin\ffmpeg.exe" -y -i %1 -ss 5 -vframes 1 -r 1 -vf scale=trunc(oh*a/2)*2:480 -f image2 %1.jpg
"c:\program files\ffmpeg\bin\ffmpeg.exe" -y -i %1 -ss 10 -vframes 1 -r 1 -vf scale=trunc(oh*a/2)*2:480 -f image2 %1.jpg

I also created a seperate batch file that will iterate over all wmv’s in a given directory and run the encoder batch against each file:


for /r %1 %%i in (*.wmv) do "c:\program files\ffmpeg\CreateWebVideos.bat" %%i
HTML, CSS & Javascript 1 comment

Improving the MediaElement.js Loading Animation

By on June 13th, 2012

I’ve been using MediaElement.js recently, to display videos on a client’s site. One thing that bothered me was the loading animation that played. It looked unfinished – showing up in a small dark square – almost like it was supposed to be completely transparent, but wasn’t.  Here’s an image of how it is in the current release:

I looked into it, and the reason for it is because the loading animation is an animated gif. Gif’s don’t support alpha transparency, so the edges don’t blend well with the background video, which is the reason for the transparent box around it.

I updated the css for MediaElement.js to make the dark background square fill the video screen.  I found that helped the loading animation blend in better, and not seem so out of place. Here’s how it looks after my changes:

With MediaElement.js being open source and on GitHub, it made it very easy for me to share my changes. I forked the code, applied my changes and created a pull request. Hopefully these changes will make their way into a MediaElement.js release in the future. In the meantime, you can grab the updated version here:

https://github.com/ccoulson/mediaelement/commit/866b1d8ac725ed0997a12232fb4446041a249415

.Net, ASP.NET 32 comments

Easy Handling of Http Range Requests in ASP.NET

By on June 6th, 2012

I was recently working on adding video playing capabilities to a client’s existing ASP.NET application. They had video files that had been uploaded by users, and they wanted to allow the users to be able to preview the files on the web. I found MediaElement.js to be a excellent solution for this: As long as there were WEBM and H.264 encoded versions of the video to be played, it would handle playing the video on most browsers/devices and would fall back to a Flash video player if html video was not supported.

I did up a sample page, encoded some sample videos and everything worked great. However when I finally implemented it on the ASP.NET application only the Flash fallback player would play the videos. The HTML 5 player would not play the video.  I finally narrowed down the culprit to the way the video files were returned to the browser. Chrome and the iPhone/iPad require Http Range Requests to be supported for HTML5 video support.  Range requests allow the browser to only request a portion of the file from the server. So for videos, the user could drag the seek slider to the middle of the video and the browser would request the video from that point on, instead of downloading the full file from the server.

IIS 7 supports range requests natively, so if you are simply serving the video files directly from IIS you won’t have any problems. In this case, all file requests were being served by an ASPX page – it looked up information in a database as well as did some authorization checking before returning the file.  Unfortunately range requests are not natively supported from aspx responses – they have to be handled manually.

Scott Mitchell wrote a great article and HTTP Handler to handle this exact scenario, and it looks like a great solution. It’s very detailed and looks like it handles pretty much every edge case. Unfortunately it would require a lot of changes to fit into my client’s existing solution (and I didn’t want to rewrite the client’s solution – as it worked well for every other case), so I searched for a simpler solution.

After a lot of searching I finally found the VideoStreamer project at CodePlex by cipto0382. It includes a great little function that handles Http Range Requests.  I was able to include it in my project with very few modifications and it worked great. Chrome, Iphone and the IPad now played the video served by the ASP.Net Application.

I’ve included the function for reference below. It checks if the HTTP Request is a range request and returns a partial file with the necessary headers if it is. If it’s not a range request, it simply returns the complete file:

RangeDownload by cipto0382


private void RangeDownload(string fullpath,HttpContext context)
{
	long size,start,end,length,fp=0;
	using (StreamReader reader = new StreamReader(fullpath))
	{

		size = reader.BaseStream.Length;
		start = 0;
		end = size - 1;
		length = size;
		// Now that we've gotten so far without errors we send the accept range header
		/* At the moment we only support single ranges.
		 * Multiple ranges requires some more work to ensure it works correctly
		 * and comply with the spesifications: http://www.w3.org/Protocols/rfc2616/rfc2616-sec19.html#sec19.2
		 *
		 * Multirange support annouces itself with:
		 * header('Accept-Ranges: bytes');
		 *
		 * Multirange content must be sent with multipart/byteranges mediatype,
		 * (mediatype = mimetype)
		 * as well as a boundry header to indicate the various chunks of data.
		 */
		context.Response.AddHeader("Accept-Ranges", "0-" + size);
		// header('Accept-Ranges: bytes');
		// multipart/byteranges
		// http://www.w3.org/Protocols/rfc2616/rfc2616-sec19.html#sec19.2

		if (!String.IsNullOrEmpty(context.Request.ServerVariables["HTTP_RANGE"]))
		{
			long anotherStart = start;
			long anotherEnd = end;
			string[] arr_split = context.Request.ServerVariables["HTTP_RANGE"].Split(new char[] { Convert.ToChar("=") });
			string range = arr_split[1];

			// Make sure the client hasn't sent us a multibyte range
			if (range.IndexOf(",") > -1)
			{
				// (?) Shoud this be issued here, or should the first
				// range be used? Or should the header be ignored and
				// we output the whole content?
				context.Response.AddHeader("Content-Range", "bytes " + start + "-" + end + "/" + size);
				throw new HttpException(416, "Requested Range Not Satisfiable");

			}

			// If the range starts with an '-' we start from the beginning
			// If not, we forward the file pointer
			// And make sure to get the end byte if spesified
			if (range.StartsWith("-"))
			{
				// The n-number of the last bytes is requested
				anotherStart = size - Convert.ToInt64(range.Substring(1));
			}
			else
			{
				arr_split = range.Split(new char[] { Convert.ToChar("-") });
				anotherStart = Convert.ToInt64(arr_split[0]);
				long temp = 0;
				anotherEnd = (arr_split.Length > 1 && Int64.TryParse(arr_split[1].ToString(), out temp)) ? Convert.ToInt64(arr_split[1]) : size;
			}
			/* Check the range and make sure it's treated according to the specs.
			 * http://www.w3.org/Protocols/rfc2616/rfc2616-sec14.html
			 */
			// End bytes can not be larger than $end.
			anotherEnd = (anotherEnd > end) ? end : anotherEnd;
			// Validate the requested range and return an error if it's not correct.
			if (anotherStart > anotherEnd || anotherStart > size - 1 || anotherEnd >= size)
			{

				context.Response.AddHeader("Content-Range", "bytes " + start + "-" + end + "/" + size);
				throw new HttpException(416, "Requested Range Not Satisfiable");
			}
			start = anotherStart;
			end = anotherEnd;

			length = end - start + 1; // Calculate new content length
			fp = reader.BaseStream.Seek(start, SeekOrigin.Begin);
			context.Response.StatusCode = 206;
		}
	}
	// Notify the client the byte range we'll be outputting
	context.Response.AddHeader("Content-Range", "bytes " + start + "-" + end + "/" + size);
	context.Response.AddHeader("Content-Length", length.ToString());
	// Start buffered download
	context.Response.WriteFile(fullpath, fp, length);
	context.Response.End();

}
Development, Sharepoint 4 comments

Adding Breadcrumb Navigation to SharePoint 2010 Application Pages

By on November 16th, 2011

The application pages in my SharePoint 2010 FBA Pack were not properly displaying the breadcrumb navigation. They didn’t include ‘Site Settings’ in the navigation – they went straight from Home to the application page.

FBA User Management Title Before Changes

I used the following blog entry to add the breadcrumb navigation:

http://weblogs.asp.net/jan/archive/2008/04/16/adding-breadcrumb-navigation-to-sharepoint-application-pages-the-easy-way.aspx

Unfortunately after making the changes, I still didn’t see a single change on the page.  I found a few other people on the forums wondering how to do breadcrumbs in SharePoint 2010, but nobody with a solution.  I decided to dig into the out of the box Site Settings application pages and see how they did it, as their breadcrumb navigation was displaying flawlessly, with the Site Settings as part of the navigation:

Site Columns Application Page Title

Looking at the Site Columns application page, mngfield.aspx, I realized the problems.  First, the SharePoint 2010 master page v4.master uses SPSiteMapProvider and SPContentMapProvider for the breadcrumb site map providers. These don’t build the breadcrumb from the layouts.sitemap file that define the breadcrumb in 2007.  The mngfield.aspx application page overrides v4.master and uses SPXmlContentMapProvider for the site map provider, which does read from the layouts.sitemap file. The second thing the out of the box application page does differently is override the PlaceHolderPageTitleInTitleArea content and hard codes the breadcrumb navigation. What I had mistaken for the bread crumb navigation was actually the title area.  The breadcrumb navigation in SharePoint 2010 is accessed with the ‘Navigate Up’ folder icon.

SharePoint 2010 Breadcrumb Control

SharePoint 2010 Title Area

So here are the steps required to get breadcrumb navigation working in SharePoint 2010:

SPFarm.Local.Services.GetValue<SPWebService>().
                    ApplyApplicationContentToLocalServer();
  • Add the following PlaceHolderTitleBreadcrumb section to your application page:
<asp:Content contentplaceholderid="PlaceHolderTitleBreadcrumb" runat="server">
  <SharePoint:UIVersionedContent UIVersion="3" runat="server"><ContentTemplate>
	<asp:SiteMapPath
		SiteMapProvider="SPXmlContentMapProvider"
		id="ContentMap"
		SkipLinkText=""
		NodeStyle-CssClass="ms-sitemapdirectional"
		RootNodeStyle-CssClass="s4-die"
		PathSeparator="&#160;&gt; "
		PathSeparatorStyle-CssClass = "s4-bcsep"
		runat="server" />
  </ContentTemplate></SharePoint:UIVersionedContent>
  <SharePoint:UIVersionedContent UIVersion="4" runat="server"><ContentTemplate>
	<SharePoint:ListSiteMapPath
		runat="server"
		SiteMapProviders="SPSiteMapProvider,SPXmlContentMapProvider"
		RenderCurrentNodeAsLink="false"
		PathSeparator=""
		CssClass="s4-breadcrumb"
		NodeStyle-CssClass="s4-breadcrumbNode"
		CurrentNodeStyle-CssClass="s4-breadcrumbCurrentNode"
		RootNodeStyle-CssClass="s4-breadcrumbRootNode"
		HideInteriorRootNodes="true"
		SkipLinkText="" />
  </ContentTemplate></SharePoint:UIVersionedContent>
</asp:Content>
  • Replace your PlaceHolderPageTitleInTitleArea section with the following:
<asp:Content ContentPlaceHolderId="PlaceHolderPageTitleInTitleArea" runat="server">
	<a href="settings.aspx"><SharePoint:EncodedLiteral runat="server" text="<%$Resources:wss,settings_pagetitle%>" EncodeMethod="HtmlEncode"/></a>&#32;<SharePoint:ClusteredDirectionalSeparatorArrow runat="server" />
	My Application Page Title
</asp:Content>

After those changes my application pages display as they should, with the proper breadcrumb navigation:

FBA User Management Title Area After

FBA User Management Breadcrumbs After

Administration, Sharepoint 4 comments

Bulk Delete SharePoint Site Users with PowerShell

By on November 16th, 2011

Below is a PowerShell script for deleting a filtered list of users from a SharePoint site.  Simply copy the script to a .ps1 file, adjust the $SITEURL to the url of the site and adjust the $USERNAMEFILTER to a lowercase string that is contained in all of the usernames you would like to delete.

The script is based on a combination of the scripts from:

http://blogs.msdn.com/b/vijgang/archive/2009/04/26/powershell-script-to-remove-all-users-from-a-sharepoint-group.aspx

and

http://nikspatel.wordpress.com/2010/08/10/delete-orphaned-ad-users-from-the-site-collection/

################################################################################################################

[System.Reflection.Assembly]::Load("Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("Microsoft.SharePoint.Publishing, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")

###########################
# "Enter the site URL here"
$SITEURL = "http://demo2010a:2114"

# "Enter the username filter (lowercase) here"
$USERNAMEFILTER = "member"

###########################

$site = new-object Microsoft.SharePoint.SPSite ( $SITEURL )
$web = $site.OpenWeb()
"Web is : " + $web.Title

$usersToDelete = @()

# Iterate through the site's users and add usernames to
# an array of users to delete if the name contains
# contains the username filter.
foreach ($user in $web.SiteUsers)
{
	if ($user.LoginName.ToLower().Contains( $USERNAMEFILTER ))
	{
		$usersToDelete += $user.LoginName
	}
}

# Delete each user selected from the SiteUsers array.
# The SiteUsers array can't be iterated through directly
# as it gets changed as users are removed from it.
foreach ($user in $usersToDelete)
{
	"Removing user : " + $user
	$web.SiteUsers.Remove($user);
}

$web.Update();

################################################################################################################
ASP.Net Membership, Forms Based Authentication, SQL Server 2 comments

Bulk Delete ASP.NET Membership Users

By on November 15th, 2011

Below is a SQL script that can be used to delete a list of users from an ASP.NET membership database.  It retrieves a list of users into a temporary table and then deletes the users from all the relevant tables in the database.

SELECT UserID, UserName
into #temp
FROM aspnet_Users
WHERE UserName in ('MEMBER10001','MEMBER10002','MEMBER10003','MEMBER10004','MEMBER10005')

-- Adjust the WHERE Clause to filter the users
-- for example WHERE UserName LIKE 'MEMBER%'
-- would delete all users whose username started with 'MEMBER'

DELETE FROM dbo.aspnet_Membership WHERE UserId IN (Select UserId from #temp)

DELETE FROM dbo.aspnet_UsersInRoles WHERE UserId IN (Select UserId from #temp)

DELETE FROM dbo.aspnet_Profile WHERE UserId IN (Select UserId from #temp)

DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE UserId IN (Select UserId from #temp)

DELETE FROM dbo.aspnet_Users WHERE UserId IN (Select UserId from #temp)
Configuration, Sharepoint 8 comments

Mixed Anonymous and Secure Content with SharePoint 2010

By on October 12th, 2011

In this tutorial i’m going to step you through how to set up a SharePoint 2010 publishing portal with both anonymous and secure content.

1. Create a new Web Application in Central Admin. Make sure that you select ‘Yes’ for Allow Anonymous.  All other values can remain at the defaults. For this example I’m going to use FBA Claims based authentication.

2. Using central admin, create a site collection on the web application you just created.  In this example I’m creating a publishing site.

3. Visit the site you just created. You will be asked to authenticate.  This is because even though ‘Allow Anonymous’ was turned on at the web application level, it still has to be specified at the site level. You will be shown the default home page.

4. First we’ll create a new Home page that will be the page anonymous users will see when they first access the site.  From Site Actions, choose New Page and call it “Home”. When the new page appears, just type in a quick welcome message and save it.

5. With a publishing site the content will need to be both published and approved before it will be visible by anonymous users. To publish the page, click ‘Submit’ on the ‘Publish’ tab and follow the wizard.  Once the submission is complete, approve the page by clicking ‘Approve’ on the ‘Publish’ tab and follow the wizard.  Note that to approve the page, you will first have to add your user to the ‘Approvers’ group under ‘People and Groups’ in ‘Site Settings’. The page is now published, and will be visible to anonymous users once we enable anonymous access.

Note that any resources that have been added to the page, such as images from the Images library, will also have to be published and approved before they can be viewed by anonymous users.

6. Under ‘Site Settings’, ‘Welcome Page’ set the welcome page to be the new ‘Home’ page we just created.

Now when we got to our site root page, we’ll be redirected to the ‘Home’ page we just published. If you sign out and visit Pages/Home.aspx, you’ll notice that you’ll be prompted to enter your credentials, so we still need to enable anonymous access to the page.

7. Sign back in to the site.  Go to ‘Site Settings’, ‘Site Permissions’. Click on ‘Anonymous Access’. From the ‘Anonymous users can access: ‘ dialog, choose ‘Lists and Libraries’ and click OK. Alternatively you can click ‘Entire Web Site’, which will make everything available anonymously, however I prefer to define exactly which resources have anonymous access.  By choosing ‘Lists and Libraries’ your entire web site is still secured.  You have to directly configure each List/Document Library to enable anonymous access. If you sign out and visit Pages/Home.aspx, you will notice that you will still be prompted to authenticate to view the page.

8. Now anonymous access needs to be enabled on the ‘Pages’ library. Go to ‘View all site content’, ‘Pages’. Click ‘Library Permissions’ on the ‘Library’ tab. Click ‘Stop Inheriting Permissions’. Click ‘Anonymous Access’. Select ‘View Items’ in the ‘Anonymous Access’ dialog and click OK. Now all of the content in the ‘Pages’ library will be accessible anonymously.  Note that you can ‘Manage Permissions’ and ‘Stop Inheriting Permissions’ for individual items (including folders) in the library, if you want to prevent them from having anonymous access. Unfortunately you cannot enable anonymous access on individual items, only the whole library.

Now if you sign out of the site and visit Pages/Home.aspx, you’ll be able to view it and won’t be prompted to authenticate.

9. There’s another problem you might notice.  Home.aspx is our default page, and allows anonymous access, however if we visit the site root we’re still prompted to authenticate.  This is because we chose the more secure option of allowing anonymous access on ‘Lists and Libraries’ instead of the ‘Entire Web Site’. PowerShell needs to be used to allow anonymous access to the site while keeping the ‘Lists and Libraries’ settings. Run the following PowerShell commands (based on instructions from http://stackoverflow.com/questions/1338809/anonymous-access-to-a-sharepoint-site-root) – substitute your own SPWeb address:


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
$web = Get-SPWeb http://demo2010a:42076
$web.AnonymousState = [Microsoft.SharePoint.SPWeb+WebAnonymousState]::Enabled
$web.AnonymousPermMask64 = "Open, ViewPages"
$web.Update()

Note that if you look at ‘Anonymous Access’ under ‘Site Permissions’, it will say ‘Entire Web Site’ – however you will still need to explicitly specify which lists and libraries have anonymous access, just as if it was set to ‘Lists and Libraries’.

Now if you sign out and visit the root of the site, you should be redirected to Pages/Home.aspx and not asked to authenticate. We finally have controlled anonymous access!

10. The other part of this tutorial is to show you how to have secure content mixed in with your anonymous content.  Now that you can control which content is available anonymously, adding secure content is very straightforward.  I had mentioned earlier that you can secure content within a library with anonymous access by clicking ‘Stop Inheriting Permissions’.  The problem with this is that by default a document will have anonymous access, and you will have to specify exactly which documents are secure.  For that reason it’s better to keep secure pages in their own library with anonymous access turned off.  For this tutorial we’re going to create a child site called ‘User’.  It’s ‘Pages’ library will not allow anonymous access, so all of it’s content will be secured.

Select ‘Site Actions’,’New Site’ and create a new ‘Publishing Site with Workflow’ called ‘User’. Click Create.

11. Edit the default page and add some custom content. Save, Publish and Approve the page.

As the new site’s Pages library is secured by default, nothing additional has to be done to secure it.  Notice that if you sign out and visit the site root, you can still access Pages/Home.aspx (although you won’t see a link to the User site). If you attempt to access ‘User/Pages/default.aspx’, you will be asked to authenticate.

Congratulations! You now have a SharePoint 2010 site with both mixed and secure content.

If you’d like to learn how to access your mixed content over both http and https see: Mixed Http and Https Content with SharePoint 2010