Invoke-Sqlcmd : Object reference not set to an instance of an object.

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.


Leave a Reply

Your email address will not be published. Required fields are marked *