Wednesday 30 September 2015

MCSE: Business Intelligence


I have just recently finished studying for my MCSE: Business Intelligence certification and I am pleased to say that I have now passed the two required exams 70-466 and 70-467.

The last exam has to be the hardest Microsoft exam I have taken due to the large range of knowledge required. I can now relax for 3 years before I have to take a recertification exam for this.

Wednesday 22 July 2015

MCSA: SQL Server certifications


I have just recently finished studying for my MCSA: SQL Server 2012 certification and I am pleased to say that I have passed the three required exams 70-461, 70-462 and 70-463.

Over the next few months I will now to study for the next level, the MCSE: Business Intelligence.

Friday 15 May 2015

Missing tabs and menu bar in Task Manager

I've just loaded up Task Manager and the tabs and menu bar are missing.

To restore the tabs, you need to double click on the outside border of the window.

Friday 24 April 2015

Error in any_na(as_list(RHS)) : object 'CanyNA' not found

I'm learning R and playing with the UI and Server components for Shiny.

A simple example below errors out:

  func = function(input, output){
    output$Order<-renderDataTable(
      if(is.null(input$Account)|input$Account=="")
      {
        Order  
      }else{   
        Order[AccountNumber==input$Account]
      }
    )

The error is

Error in any_na(as_list(RHS)) : object 'CanyNA' not found


This only started to happen only after I had updated the data.table package.
The resolved this by reinstalling  this package.

install.packages("data.table")

Wednesday 22 April 2015

The replacement token 'id' has no value

When trying to manually build a NuGet package from the command line with the following command

nuget pack Trial.nuspec

You may recieve the following error:
Attempting to build package from 'Trial.nuspec'.
The replacement token 'id' has no value.
This will be likely if you are using tokens in the nuspec file. Example:
<?xml version="1.0"?>
<package >
  <metadata>
    <id>$id$</id>
    <version>$version$</version>
    <title>$title$</title>
    <authors>$author$</authors>
    <owners>$author$</owners>
    <licenseUrl>http://localhost</licenseUrl>
    <projectUrl>http://localhost</projectUrl>
    <iconUrl>http://locahost</iconUrl>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <description>$description$</description>
    <releaseNotes>Initial release.</releaseNotes>
    <copyright>Copyright 2015</copyright>
    <tags></tags>
  </metadata>
  <files>
    <file src="bin\$configuration$\*" target="" />
  </files>
</package>

If this is the case, run the same command prompt but pass in the csproj file instead.

nuget pack Trial.csproj

Tuesday 21 April 2015

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.

Monday 20 April 2015

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"

Wednesday 15 April 2015

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.

Wednesday 19 March 2014

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';

Monday 28 October 2013

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.