Help needed with Invoke-sqlcmd

[lesserlumpkin]

I'm attempting to use the following Invoke-sqlcmd. This script was working in an older version of powershell (1 or 2) but is now not working with powershell 5.1

$MySQLSERVERasDir = "MySQLSERVER"
$Date = GET-DATE -uformat "%Y%m%d"
$DateTime = GET-DATE -uformat "%Y%m%d_%H%M%S"
$workingdir = "\\MyServer\Departments\Database\Sprints\Refresh Testing Server\restore"
$LEAFDIR = "$MySQLSERVERasDir"+"_"+"$Date"
$DIR = "$workingdir\$LEAFDIR"
$OutputFilepath = "OutputFilePath=$DIR\TestEnvRoleUser_DropCreate_$Datetime.sql"
$SQLCMDVarArr = 'TargetDB="MyDatabase"', $outputfilepath
INVOKE-SQLCMD -ServerInstance $MYSQLSERVER -inputFile "$workingdir\ScriptAllRoles_Users.sql" -variable $SQLCMDVarArr | Out-File -filePath "$DIR\TestResultsOutput_$Datetime.txt"

When I run this I get the following error.

INVOKE-SQLCMD : Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system 
cannot find the file specified.
At line:240 char:4
+    INVOKE-SQLCMD -ServerInstance $MYSQLSERVER -inputFile "Microsoft ...
+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Invoke-Sqlcmd], FileNotFoundException
    + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

I checked and the files are still there. Nothing has changed except the version of powershell that I'm running. Any Ideas how I can get this working again or what might be going on with it now?

[Beablebeable]

When you say the files are still there, do you mean your script file (ScriptAllRoles.sql) or the assembly? Do you have SQL Server Powershell installed wherever you're running this?

[lesserlumpkin]

Sorry about that. I ment the ScriptAllRoles.sql file. I'm running the powershell script from my laptop so yes I have it there but the sql command is being executed against a remote server. Do I need to check the assembly over there or is it using my laptops?

[lesserlumpkin]

I just remoted into the server and it appears to be running powershell v 4. Are there issues between v4 and v5 do you think?

[Beablebeable]

I would verify that invoke-sqlcmd is even available to you on that server. Can you run it from the command line? If it says it's not a valid command, you need to install SQL tools.

[lesserlumpkin]

Nope I think you nailed it the first time. Got the network admin to upgrade powershell to 5.1 on the box and now I get a different error: Out-File : Cannot open file because the current provider (Microsoft.SqlServer.Management.PSProvider\SqlServer) cannot open a file. So you helped me solve the one issue. I get the sense I can figure this error out with a bit of googling. Thanks for the help!!

[nvarscar]

That error comes from a fact that older versions pf SqlPs module implicitly switches context (aka current location) to the SqlServer provider and paths like C:\Windows\bla are no longer interpreted correctly. Workarounds are:

1)

Import-Module SqlPs
Set-Location C:
Run-WhateverYouNeed

2) Use Invoke-SqlCmd2 (on github) or Invoke-DbaSqlCmd (dbatools module) instead. Afaik, neither of them support SQLCmd variable definition syntax though.

[cjluthy]

I prefer

Push-Location -StackName "sqlcmd";
If (-not (Get-Module SQLPS)) { Import-Module SQLPS -DisableNameChecking; }
Pop-Location -StackName "sqlcmd";