CODEWITHSUNDEEP
sql-serverpowershellsqlcmd
James Atance
James Atance

Reputation: 21

Invoke a SQL script containing Powershell variables

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

Upvotes: 2

Views: 1036

Answers (2)

Dave Sexton
Dave Sexton

Reputation: 11188

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)

Upvotes: 0

Ranadip Dutta
Ranadip Dutta

Reputation: 9133

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.

Upvotes: 1

Related Questions