I cant seem to get a PS variable called $sqlParameters to be used in an SQL script, any clues to what I am doing wrong?
PowerShell:
$sqlParameters = "powerShellVar = 'CONTOSO\cmpsvc'"
invoke-sqlcmdInvoke SQL script using Powershell variables -inputfile "c:\temp\test.sql" -serverinstance SRVSCCM\MSSQLSERVER -variable $sqlParameters
SQL:
CREATE LOGIN [$sqlParameters] FROM WINDOWS;
USE test
EXEC sp_addrolemember N'db_owner', N'$sqlParameters'
GO
James
YOu can try something like this:
$MyArray = "MyVar1 = 'String1'", "MyVar2 = 'String2'"
Invoke-Sqlcmd -Query "SELECT `$(MyVar1) AS Var1, `$(MyVar2) AS Var2;" -Variable $MyArray
or something like this:
$query=@'
CREATE LOGIN [$sqlParameters] FROM WINDOWS;
USE test
EXEC sp_addrolemember N'db_owner', N'$sqlParameters'
GO
'@
Invoke-Sqlcmd -Query $query
But if you have a file, then you have to use T-sql variables only cause SQL will understand in that way only:
Invoke-Sqlcmd -InputFile "C:\Folder\SQLCmd.sql"
Hope it helps.
One way you could do it is to use Get-Contents and then get Powershell to do the variable expansion, something like this:
$sqlParameters = 'CONTOSO\cmpsvc'"
Invoke-SqlCmd -Query ($ExecutionContext.InvokeCommand.ExpandString(Get-Contents 'my_file.sql'))
Or an easier way would be to use the format string. Change your file contents to this:
CREATE LOGIN [{0}] FROM WINDOWS;
USE test
EXEC sp_addrolemember N'db_owner', N'$sqlParameters'
GO
Then your Powershell code would be come this:
$sqlParameters = 'CONTOSO\cmpsvc'"
Invoke-SqlCmd -Query ((Get-Contents 'my_file.sql') -f $sqlParameters)
