How to pass array as variable in SQLCMD

Question

I am trying to pass server names in sql script , but its not working. Please help

SQL Script patch_report.sql, I am running via powershell giving error

SELECT * from table where server in ('$(trimsqlstr)')

Error

Msg 102,level Level 15, State 1, Server DBserver, Line 1
Incorrect syntax near 'server1'.

$DB_server = 'DBserver'
$serverName = "server1
server2
server3
"
$serverName = $serverName -split "\n" | foreach {$_.ToString().TrimEnd()}
$trimsqlstr = foreach($server in $serverName){
if ($serverName.Indexof($server) -eq $($serverName.Length-1)){
"'$Server'"
} else {
"'$Server',"
}

SQLCMD.exe -v trimsqlstr = "$($trimsqlstr)" -E -S $DB_server -W -i patch_report.sql

I am expecting it to result like this

SELECT * from table where server in ('server1','server2','server3')

my code was not working because it had wrong syntax , I was wrapping ('$(trimsqlstr)') in "'" , I corrected it to ($trimsqlstr) and it worked — mayursharma
– mayursharma, Commented Apr 5, 2019 at 11:37

Palle Due · Accepted Answer · 2019-02-19 09:51:26Z

2

The accepted answer is an excellent solution, I just want to point you towards the -join operator.

The -join operator is very practical for joining members of an array to a string.

You can do something like this:

$serverNames = "server1
server2
server3
"

$serverNameArray = $serverNames -split "\n" | foreach {$_.ToString().TrimEnd()} | Where-Object {$_} | foreach {"'$_'"}
$whereClause = $serverNameArray -join ','
$selectQuery = "SELECT * from table where server in ($whereClause)"

Where-Object {$_} is removing the empty elements.

answered Feb 19, 2019 at 9:51

Palle Due

6,3224 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user6811411 Over a year ago

You could shorten further: (($serverNames -split "\n").Trim() -ne ''| ForEach-Object{"'$_'"}) -join ','

Mudit Bahedia · Accepted Answer · 2019-02-19 09:15:53Z

1

You may use below code:

$DB_server = 'DBserver'
$serverName = "server1
server2
server3
"

$serverName = $serverName -split "\n" | foreach {$_.ToString().TrimEnd()}
ForEach($server in $serverName) 
{
$serverstring = $serverstring+"'"+$server+"'"+","
}
$trimsqlstr = $serverstring.Substring(0, $serverstring.Length-4)

And then use $trimsqlstr in query like below

SELECT * from table where server in ($trimsqlstr)

answered Feb 19, 2019 at 9:15

Mudit Bahedia

2462 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to pass array as variable in SQLCMD

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related