SQL weekday datepart different depending on locale

Depending on which language your user is running, the following query will return a different result.

SELECT DATEPART(weekday, GETDATE())

Using the English language (US), this would return 2 if run on a Monday, as Sunday is allocated 1.
If using British English, this would return 1 for Monday, as Sunday is now allocated 7.

I've just had to write some queries that could be run using either locale. Tested locally it all worked fine. Deployed to live server, all my dates were out. Took me a while to understand that the locale of the user was different.

I Google'd for an answer and found multiple solution, the one that worked for me was the following:

SELECT (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7 + 1

There are many other solutions to this problem that may be beneficial to others.

SQL Jobs fail when run on a mirrored server

We have jobs that run on a mirrored server. When these jobs are running on the principal server, no problems, but when they ruin on the mirror nothing works.
Ok, there are several solutions to this problem. Disable jobs on the mirror, but you have to remember to enable these when the mirror fails over and then disable the jobs from the other. Yes you could script this all out.

The current approach I am using is to add a new step to the job, making it the first step. This executes the following SQL.

DECLARE @mirroring_role INT

SET @mirroring_role = (SELECT mirroring_role FROM msdb.sys.database_mirroring WHERE database_id = db_id('Database Name'))

IF @mirroring_role = 2
   raiserror('The database is running as a mirror',11,1)


Ensure that you set the steps "On failure action" to "Quit the job reporting success"

Reset to original script directory in PowerShell

I've been write several PowerShell scripts lately to help automate repetitive tasks. When I've been testing these scripts, I will often change my current location with in PowerShell, either cd to another location or fire up the SqlServer shell.

When the script finished during test, I would then make another change, add the next step etc, then go to my PowerShell console, but it's not in the original directory for me to re-run my script.

I've taken to adding the following to the bottom of my scripts to return the console back to the original location.

# Switch back to disk prompt
$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
Set-Location -Path $scriptPath

I know there are many ways of doing this in PowerShell, but I'm still learning.

Manage User Mapping With ALTER USER

I previously posted about how to use the SQL stored procs to auto fix a user.

This stored proc is going to be deprecated in new versions of SQL Server. So how do we map a user, we can use the ALTER USER function.

For Example:

EXEC sp_change_users_login 'Auto_Fix', 'username', NULL, 'password';

now becomes

ALTER USER [username] WITH LOGIN = [username], PASSWORD = 'password';

Microsoft Certified Solutions Developer: Web Applications

I would like to say that today I passed my next exam 70-492, Upgrade your MCPD: Web Developer 4 to MCSD: Web Applications.

Over the next few months I will try to blog more, now that I've stopped my studying for the time being.

Microsoft Specialist in Programming in HTML5 with JavaScript and CSS3

For my 100^th post, I would like to say that today I passed the Microsoft Specialist exam 70-480, Programming in HTML5 with JavaScript and CSS3.

MCPD Web Developer 4

Yesterday I successfully passed the upgrade MCPD exam 70-523. Got a great pass mark, so glad that all the studying paid off.

SSIS on 64bit Dev machine

You have an "Execute SQL Task" to return a "Full result set" but when executing you get the following error.

[Execute SQL Task] Error: Executing the query "SELECT * FROM Table" failed with the following error: "Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Are you running on Windows 7 64bit? A possible fix if you are is to set the run time to 32 bit. You can do this by:

1. Go to Project Properties
2. Configuration Properties -> Debugging
3. Set Run64BitRuntime False

Visual Studio 2010 not opening CSS files

Recently I have had a problem where Visual Studio 2010 wouldn't open any CSS files. I can't say for sure if this started happening just after I installed SP1 as I don't tend to edit many CSS files.

The solution I found was to install the following extension.
Extension Manager -> Online Gallery
Search for: "Web Standards Update for Microsoft Visual Studio 2010 SP1"

This information I found on the social MSDN site here.

Unit testing MVC routes

If you are using the MVC Routing engine, you can test your routes to ensure that they work as expected.

To test the default route:

[Test]
routes.MapRoute(
    "Default", // Route name
    "{controller}/{action}/{id}", // URL with parameters
    new { controller = "Home",
          action = "Index",
          id = UrlParameter.Optional }
);

The example test is:

[Test]
public void DefaultRouteTest()
{
    var routes = new RouteCollection();
    var application = new Application();
    application.RegisterRoutes(routes);

    var context = new Mock<httpcontextbase>();
    context.Setup(p => p.Request.AppRelativeCurrentExecutionFilePath)
                                   .Returns("~/");
    var routeData = routes.GetRouteData(context.Object);

    Assert.AreEqual(((Route)routeData.Route).Url, 
                    "{controller}/{action}/{id}");
    Assert.AreEqual("Home", routeData.Values["controller"]);
    Assert.AreEqual("Index", routeData.Values["action"]);
}

This came from an original post by Scott Gu, that we had to change slightly. http://weblogs.asp.net/scottgu/archive/2007/12/03/asp-net-mvc-framework-part-2-url-routing.aspx

The original code was causing the first parameter to drop the first character. i.e. For the default route, the controller was return 'ome' instead of 'home'.