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.