Monday, February 01, 2016

passing variables from power shell to sql server by using Invoke

power Shell script:
==============

param(
    [Parameter(Mandatory=$true)]
    [string]$PWD,
    [string]$saUser="sa",
    [string]$ServerInstance="localhost\SQLEXPRESS"
)
$winUser=([Environment]::UserDomainName)+"\"+([Environment]::UserName)
Write-Host "Windos User=" $winUser
$sqlVars = "V1 = 'testV1'", "V2 ='$winUser'"
Invoke-Sqlcmd -Query "SELECT `$(V1) AS V1, `$(V2) AS V2;" -Variable $sqlVars -serverinstance $ServerInstance  -Username $saUser -Password $PWD
invoke-sqlcmd   -InputFile "test.sql " -Variable $sqlVars -serverinstance $ServerInstance  -Username $saUser -Password $PWD


SQL Script (test.sql):
================
select $(V1) as v1, $(V2) AS V2;

No comments:

Post a Comment