Pages

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
    }
}

else
{
    if($agentserv.Status -ne "Stopped")
    {
        Stop-Service SQLSERVERAGENT
    }
     
    if($writerserv.Status -ne "Stopped")
    {
        Stop-Service SQLWriter
    }
 
    Stop-Service MSSQLSERVER -FORCE
}

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:

http://av.vimeo.com/80855/175/132116203.mp4?token2=1389805898_5f3e6395d93416a77a23967383decb08&aksessionid=abc26dd0445dfe42

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

http://player.vimeo.com/video/5111113

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.

Example:

<a href="http://pdl.vimeocdn.com/75104/591/193692236.mp4?token2=1389805784_db0a211d242e2455cce477bed7cdb5fd&aksessionid=102bf3e4fc2656f0">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.

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();
    }

    else
    {
        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("http://www.WEBSITE.com");

        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))
        {
            resp.GetResponseStream().CopyTo(fileStream);
        }
    }
}

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)
    {
        textBox7.SelectAll();
    }
}
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 Blogger.com blog

In order to point your domain name to your Blogger.com 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 Blogger.com blog:

CNAME

kqtj3btu4kfq 300 IN CNAME gv-d2g1xvsvkdnnme.dv.googlehosted.com.

The kqtj3btu4kfq  tag depends on your blog, you can find in the Blogger.com settings which tag you need to use, the gv-d2g1xvsvkdnnme.dv.googlehosted.com. part also depends on your blog, again look in your settings.

www 300 IN CNAME ghs.google.com.

This is standard for all Blogger.com 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 216.239.32.21
@ 300 IN A 216.239.34.21
@ 300 IN A 216.239.36.21
@ 300 IN A 216.239.38.21

These can be set as is, they are standard settings for all Blogger.com 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.

Sunday, May 13, 2012

View all the CONVERT formats for a date

SQL Server allows us to convert DATETIME values to different stylistic formats using the CONVERT function. There are 36 different formats we can convert to and depending on which format you require at any given point in time – it can be difficult to know off the top of your head exactly which style number to use.

This script allows you to quickly preview all the different CONVERT styles for dates in SQL Server, simply plug in your desired date in the first line and run it.
You will then know which number to use based on the output. Enjoy!

DECLARE @DATE DATETIME; SET @DATE = (SELECT GETDATE());
SELECT N [Style], CONVERT(VARCHAR,@DATE,N) [Date]
FROM ( SELECT ROW_NUMBER() OVER(ORDER BY sc1.name) [N]
       FROM sys.columns sc1) NUMS
WHERE N BETWEEN 1 AND 14 OR N IN (20,21) OR N BETWEEN 100 AND 114 OR N IN (120,121,126,127,130,131)