Today I was working on some PowerShell scripts where I ran into the following error message:
Invoke-Sqlcmd : Object reference not set to an instance of an object.
Below is the PowerShell code which was throwing the error.
Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $Query
I discovered the culprit was the $Query parameter. The $Query parameter contents contained a TSQL query to create a SQL Server Agent Job. The problem was that this Agent Job made use of tokens, which I suspect PowerShell confuses for variables due to the dollar sign symbol (ie $(SRVR)). The work around was to assign the tokens to TSQL variables, in a concatenated format:
SET @Server = '$' + '(SRVR)' SET @Message = 'The '+@Server+' SQL Server instance is unavailable.'
My PowerShell script would now run without errors.