Problem passsing variables to invoke-sqlcmd

Question

Hi,

I'm fairly new to powershell and trying to pass variables to invoke-sqlcmd as follows:

$Server = "Server"
$DBName = "DatabaseName"
$FileName = "full path to .sql script"
sqlps -NoLogo -Command Invoke-sqlcmd -ServerInstance $Server -Database $DBName -InputFile $FileName

When I try to execute this I get the following error:

SQLPS.exe : Cannot validate argument on parameter 'InputFile'. The argument is null or empty. Supply an argument that is not null or empty and then try the command again.
At line:1 char:6
+ sqlps <<<<  -NoLogo -Command { Invoke-sqlcmd -InputFile $FileName -ServerInstance $Server -Database $DBName }
    + CategoryInfo          : InvalidData: (:) [Invoke-Sqlcmd], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

I've tried moving the parameters (InputFile, ServerInstance and Database) passed to Invoke-sqlcmd but this only result in the same error but on a different parameter.

If I remove the variables and just enter the exact information, it runs just fine. For example

sqlps -NoLogo -Command { invoke-sqlcmd -InputFile "c:\temp\script.sql" -ServerInstance "Server" -Database "Database" }

Any help would be appreciated.

Thanks

Rich

Answer 1

Hi Rich McCloud,

Regarding to your description and powershell code you provided, seems you were using SqlPs  with InPutFormat that works for you.
Based on my research InPutFormat { Text | XML } specifies that input to the sqlps utility is formatted as either text strings (Text) or in a serialized CLIXML format (XML). So you need to add InputFormat.
For more information, please refer to sqlps Utility

Meanwhile you can use the resolve-path cmdlet for this. Here is more info from a PowerShell forum discussion: Invoke-Sqlcmd -InputFile (resolve-path .\1.sql).Path and mn resolve-path.

For further troubleshooting about Powershell please link to Powershell forums 

 

---

Regards, Amber zhang

Answer 2

Hi Amber,

Thanks for the reply but the problem is not with the Input Format, I've tried setting that with no change in the error and if I change the order of the commands passed to Invoke-SQLCMD I get the same error just referring to a different parameter, e.g. $Database.

If I run the exact command replacing the variables $Server, $DBName and $InputFile with the actual values (not variables) the command runs correctly.  Below is another example of the same issue:

$DBVer = sqlps -NoLogo -Command { invoke-sqlcmd -ServerInstance $Instance -Database $DBName -Query "select version from cfgSystem" | select-object -expandproperty Version }

Error:

SQLPS.exe : Cannot validate argument on parameter 'Database'. The argument is null or empty. Supply an argument that is not null or empty and then try the command again.
At line:1 char:15
+ $DBVer = sqlps <<<<  -InputFormat Text -NoLogo -Command { invoke-sqlcmd -ServerInstance $Instance -Database $DBName -Query "select version from cfgSystem" | select-object -expandproperty
Version }
    + CategoryInfo          : InvalidData: (:) [Invoke-Sqlcmd], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

However, if I run the exact same command replacing the variables with the actual values it runs correctly:

$DBVer = sqlps -InputFormat Text -NoLogo -Command { invoke-sqlcmd -ServerInstance localhost -Database VisionDemo61 -Query "select version from cfgSystem" | select-object -expandproperty Version }

Correctly returned result:

$DBVer = sqlps -NoLogo -Command { invoke-sqlcmd -ServerInstance localhost -Database VisionDemo61 -Query "select version from cfgSystem" | select-object -expandproperty Version }

__________________________________________________________________________________________________________________________________________________________________________________________
PS C:\Users\richardmccloud> $DBVer
6.2.300

I can certainly link to the powershell forums but since the problem appears to be with the SQL Server powershell cmdlets I thought this was the appropriate location. 

Thanks

Rich

Answer 3

Not my domain of knowledge, but this was interesting:

Passing Variables to SQL Stored Procedure from Powershell
http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/5b7f9652-aa52-41b9-8a0a-597c5ab72305/

FWIW,
RLF

PS - If you are using SQL Server Agent for this work.

SQL Server Agent calling Powershell with parameters that Powershell can use as variables?
http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/add01ee8-c77d-4554-996f-1722cb977cff/

Answer 4

Hi Russell,

I had seen that particular post and testing a bit more, if I access the SQLPS mini-shell, set the variables and then run the invoke-sqlcmd passing the variables it also runs fine so it's either a limitation/bug of sqlps when called outside of the mini-shell or some issue with syntax that I can't seem to figure out.

Thanks for the continued help.

Rich

Answer 5

Rich, As I mentioned, this is not my area of expertise, but I did a little research that indicates that the 'mini-shell' is needed through SQL Server 2008 R2:

http://sev17.com/2010/05/the-truth-about-sqlps-and-powershell-v2/

Although the mini-shell is still supported in SQL Server 2012, there is now a SQLPS module that can be imported into a Windows PowerShell 2.0 environment. 

FWIW,
RLF