Tag Archives: SqlServer

Invoke-SqlCmd throws exception when trying to set the database via parameter

This was properly doing my nut today! Basically I created a generic script to fix orphaned users and replaced the database with the standard SqlCmd variable syntax:

use $(DatabaseName);

When running in SSMS setting the variables like this:

:setvar DatabaseName MyDatabase

works fine, as it does when setting as a param to the sqlcmd.exe:

sqlcmd -i C:\PathToScript.sql -v DatabaseName=MyDatabase

however when using the PowerShell Invoke-SqlCmd Cmdlet from the SqlServer PowerShell extensions it throws an exception that gives you absolutely no clue as to why:

Invoke-Sqlcmd : Object reference not set to an instance of an object.
At C:\Users\mayesr\Desktop\PowerShellScratchPad.ps1:22 char:14
+ Invoke-Sqlcmd <<<< -InputFile "$g_ScriptLocation\FixUser.sql" -ServerInstance "localhost" -Verbose + CategoryInfo : InvalidResult: (localhost:PSObject) [Invoke-Sqlcmd], NullReferenceExcep tion + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.GetScriptComm and

I eventually noticed that the Invoke-SqlCmd has a parameter that you can set the database with. Once that was set and the use statement removed, the script worked fine! Luckily it didn’t take me too long to notice but hopefully if you come across this post it will save you some time!!

UPDATE

After I thought I had fixed this I realised that it wasn’t the Database param at all and in-fact was any param! I eventually found out there is a bug in the MSDN docs (and/or code)!! The docs say that the following format should be used:

$MyArray = "MyVar1 = 'String1'", "MyVar2 = 'String2'"
Invoke-Sqlcmd -Query "SELECT `$(MyVar1) AS Var1, `$(MyVar2) AS Var2;" -Variable $MyArray

However this doesn’t work and the following should be used instead (remove the spaces around the ‘=’):

$MyArray = "MyVar1='String1'", "MyVar2='String2'"
Invoke-Sqlcmd -Query "SELECT `$(MyVar1) AS Var1, `$(MyVar2) AS Var2;" -Variable $MyArray

I just wish I’d scrolled down to the bottom of the page as other people had added comments saying this!! 😡