3

I have a PowerShell script the with get data and then run a SQL file, parsing in the data it have got. However I keep getting the following error:

Invoke-Sqlcmd : The format used to define the new variable for Invoke-Sqlcmd cmdlet is invalid. Please use the 'var=value' format for defining a new variable.

PowerShell Script:

Write-host $ID, $XMLParam, $SourceID

$DBParam1 = "ID="+ $ID
$DBParam2 = "XMLPARAM="+ $XMLParam
$DBParam3 = "SOURCEID="+ $SourceID

$DBParams = @($DBParam1, $DBParam2, $DBParam3)

$Results = Invoke-Sqlcmd -InputFile "D:\SamHelp\marksheet.sql" -Variable $DBParams -Server $SQLServer -Database 'DB' -Username $Username -Password $Password

SQL File:

declare @id int, @xmlParam xml, @sourceID int

SET @id = '$(ID)'
SET @xmlParam =  '$(XMLPARAM)'
SET @sourceID = '$(SOURCEID)'
...

The Write-Host in the powershell outputs:

001 <data><destSu id="000001"/><destSu id="000002"/></data> 0000001

Improve this question
2
  • Can't test this (hence not posted as answer) but based on examples from other related questions (stackoverflow.com/questions/16654866/… and stackoverflow.com/questions/19283531/…) I think you might need to place single quotes around the parameter values (at least the XML one anyway, since it's a string), e.g. $DBParam2 = "XMLPARAM='"+ $XMLParam + "'" . Not sure if you'll need to do it for the other two or not, since they're numeric Commented Sep 5, 2019 at 9:22
  • 1
    If $XMLParam is a string containing XML with double quotes, another thing you can try if the suggestion @ADyson provided doesn't work is $DBParam2 = "XMLPARAM="+ $XMLParam.Replace('"', '""'). This will escape the double quotes in the SQLCMD scripting variable value. Commented Sep 5, 2019 at 10:42
Related questions
2
passing a parameter to an sql file in powershell (sqlcmd)
81
How to execute a .sql file using PowerShell
13
Invoke-Sqlcmd, InputFile, and Variable
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.