Wednesday, December 24, 2014

Data Cleaning Techniques (SQL Server)

This will be a constantly updated library of all the data cleaning techniques I come up with, mostly for my own reference and convenience. You use this code at your own risk, and always put it in a select first before making any updates!

LastName, FirstName to FirstName LastName

REVERSE(SUBSTRING(REVERSE(Player),1,PATINDEX('% %',REVERSE(Player)))) + ' ' + SUBSTRING(Player,1,PATINDEX('%,%',Player)-1)

Sunday, June 1, 2014

Turn SQL Server on using Powershell

This is a neat little script that toggles on or off the default SQL Server service, as well as SQL Server Agent and SQL Writer. This is good if you keep an instance installed to play around with that is off 90% of the time. You should set the services as manual to free up RAM and then just toggle it on with this script when you need to use it.

How to use this script:

1) Save as a .ps1 file to My Documents, right click file and choose Send To .. Desktop (Create Shortcut).
2) Right click the shortcut on the desktop and replace the Target with this:

%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -File "C:\Users\Vince\Documents\TOGGLE_SQL.ps1" -WindowsStyle Hidden

Modify the path (bolded) accordingly based on where you saved the file.

$sqlserv = Get-Service -Name MSSQLSERVER
$agentserv = Get-Service -Name SQLSERVERAGENT
$writerserv = Get-Service -Name SQLWriter

if($sqlserv.Status -eq "Stopped")
    Start-Service MSSQLSERVER
    if($agentserv.Status -eq "Stopped")
        Start-Service SQLSERVERAGENT
    if($writerserv.Status -eq "Stopped")
        Start-Service SQLWriter

    if($agentserv.Status -ne "Stopped")
        Stop-Service SQLSERVERAGENT
    if($writerserv.Status -ne "Stopped")
        Stop-Service SQLWriter

Wednesday, January 15, 2014

How to download private/embedded Vimeo videos

Disclaimer: This post does not explain how to circumvent Vimeo's protection to access private videos. You will still need access to the private video you are trying to download, presumably by paying for it.

I paid for access to a video tutorial site and I wanted to archive their video collection to my computer for future viewing. The videos were all embedded from Vimeo and private but fortunately I figured out how to download them .. there are 2 ways:

Sometimes they will embed the .mp4 stream directly, sometimes they will embed the Vimeo window as a browser object. To find out which method is being used, open the page where the video is on and go to View Source ..

Do a search for .mp4 and/or vimeo. If you find a .mp4 link like this:

Then skip the next step, however if you don't find anything ending in .mp4 but instead can only find something like this:

Then click that link (note you have to click the link directly from view source, pasting it into a new browser window will not work) and you should see your video in full screen.

From that page, right click and choose View Source and search for .mp4, you should find multiple links to .mp4 streams. The links will have within the code the 'profile' for example SD or h264, you should also be able to see the width/height of the video. Select the .mp4 link with the quality settings you require.

For the final step, create a .html file with a hyperlink in it and paste your .mp4 link in there.


<a href="">download</a>

Now open that .html file in your web browser and right click 'Save link as ..' and you will be able to save the Vimeo video as an MP4 to your computer.

Alternate Method

I received a ton of emails saying the above method doesn't work, it appears that Vimeo has different ways of delivering and protecting these videos. Fortunately some of these people struggling with the above method sent me the link and login so I was able to figure out how to download those too.

Step 1) Open Google Chrome Developer Tools
Step 2) Click the Elements tab, this will show the code of the page. Expand the body tag and get to the <div id="main">

Follow the below screenshot and expand the lines I have highlighted in red until you get to an .mp4 link.

Step 3) Once you see the .mp4 link, click it and you will see in Chrome the following link, right click this and choose 'Open link in new tab' .. this will open the video in a new tab.

Step 4) Once the video is opened , you can right click it and choose Save Video As .. 

Tuesday, January 14, 2014

Missing aspnet_regiis.exe

You will sometimes need to run aspnet_regiis.exe with the -i flag to properly register a version of the .NET Framework on a server.

This file should be in C:\Windows\Microsoft.NET\Framework\versionNumber\aspnet_regiis.exe, however if you can't find it try the following steps:

1) Repair your installation of .NET Framework, you can do this through the Programs and Features menu in Control Panel.

2) Make sure you have installed the full version and not just client profile version of the Framework. The client profile does not come with this aspnet_regiis.exe program. If you have client profile, uninstall it and install the full version of the Framework. None of my Google searches mentioned #2, so I thought I would share that additional solution.

Tuesday, November 19, 2013

Downloading protected files from a WebBrowser object

When you navigate using the WebBrowser control to a downloadable file, the control gives the standard IE download dialog box. To automate this and download the file programmatically we need to use the HttpWebRequest class. This sends a GET request to the server, we can then save the response stream as a file. One problem is if the website you are downloading from requires a login. As the WebRequest you create is outside of the WebBrowser object, it will not be authenticated so we need to attach the cookies to the request headers. The WebBrowser control doesn't allow you to get HTTPOnly cookies from the page, so we need to implement a workaround to get these cookies. Credit to Microsoft Technet for this code:

[DllImport("wininet.dll", CharSet = CharSet.Auto, SetLastError = true)]
static extern bool InternetGetCookieEx(string pchURL, string pchCookieName, StringBuilder pchCookieData, ref uint pcchCookieData, int dwFlags, IntPtr lpReserved);
const int INTERNET_COOKIE_HTTPONLY = 0x00002000;

public static string GetGlobalCookies(string uri)
    uint datasize = 1024;
    StringBuilder cookieData = new StringBuilder((int)datasize);
    if (InternetGetCookieEx(uri, null, cookieData, ref datasize, INTERNET_COOKIE_HTTPONLY, IntPtr.Zero)
        && cookieData.Length > 0)
        return cookieData.ToString();

        return null;

This method GetGlobalCookies allows you to get the cookies you received after logging in with your WebBrowser object and attach them to your WebRequest. To automate the WebRequest, we need to create an Event Handler for the WebBrowser's Navigating event like so:
webBrowser1.Navigating += new WebBrowserNavigatingEventHandler(webBrowser1_Navigating);
In the EventHandler, we first check if the URL has a specific ending. In my example I am looking for torrent files. We then cancel the download dialog and get the cookie string using the GetGlobalCookies method. After that, we create a WebRequest using the URL of the torrent file we are downloading. We then attach the cookies to the header, as well as specify that it's a GET request. The ContentLength = 0 is to overcome 411 errors. We then simply feed the response into a file and you should now have your file downloaded to disk.

void webBrowser1_Navigating(object sender, WebBrowserNavigatingEventArgs e)
    if (e.Url.Segments[e.Url.Segments.Length - 1].EndsWith(".torrent"))
        e.Cancel = true;
        string filepath = filepath = @"C:\Downloads\" + e.Url.Segments[e.Url.Segments.Length - 1];

        string _cookies = GetGlobalCookies("");

        WebRequest wr = HttpWebRequest.Create(e.Url);
        wr.Headers.Add("Cookie", _cookies);
        wr.Method = "GET";
        wr.ContentLength = 0;

        WebResponse resp = wr.GetResponse();

        using (var fileStream = System.IO.File.Create(filepath))

Friday, August 30, 2013

Select all doesn't work in Text Box

TextBox's do not by default support the CTRL+A shortcut when the MultiLine property is set to true (documentation).

One way to get around this is to write a custom event handler for your text box, like so:

private void textBox7_KeyDown(object sender, KeyEventArgs e)
    if (e.Control && e.KeyCode == Keys.A)
You then need to go to your Form.Designer.cs file and add the following line:
this.textBox7.KeyDown += new System.Windows.Forms.KeyEventHandler(this.textBox7_KeyDown);

This tells the form to use the custom event handler you just created.

If this still doesn't work, you can also check the ShortcutsEnabled property on your text box, make sure it is set to true.

Thursday, August 22, 2013

Pointing your domain name to your blog

In order to point your domain name to your blog you need to modify the zone file which can be tricky if you've never done this before and it's very easy to get wrong as I have just learned the last few days!

There are 2 pieces of information you need to provide in your zone file which will have your domain point to your blog:


kqtj3btu4kfq 300 IN CNAME

The kqtj3btu4kfq  tag depends on your blog, you can find in the settings which tag you need to use, the part also depends on your blog, again look in your settings.

www 300 IN CNAME

This is standard for all blogs. VERY IMPORTANT: the trailing . at the end of both addresses is extremely important and without it this will not work.

A records 

@ 300 IN A
@ 300 IN A
@ 300 IN A
@ 300 IN A

These can be set as is, they are standard settings for all blogs

You will need to consult your domain registrar's documentation to find out how to make these changes as well. Once you have made the settings give it about 2-3 hours max for the changes to propagate and your blog should then be working.