Query SQL and store result as a variable

I’m trying to query MS SQL and take the results and store it as a string variable, that can then be passed to a function.

The results I get are:

ConnectionString

SERVER01,SERVER02

I only require the:

SERVER01,SERVER02

how do I just get that into a variable to use in a function?

If the results are stored in a variable called $Results. You can use:

$NewResults = $Results | Select-object -ExpandProperty ConnectionString

OR

$Results.Connectionstring | foreach { MyFunction $_}

Hey Kawiki,

The text that you show there. Is it already assigned to a variable, or is that the console output you get on your screen when you run your query?

It would help to know if it’s console output, a variable that is only string output, or if it is actually an an object with a property (e.g. $Results.ConnectionString). If it’s the last, Kiran’s along the right tracks, but we’d need to do more if it’s one of the first two.

Here is the way I’m doing it, I’m storing the results to a variable…

$QueryResult = Invoke-Sqlcmd -ServerInstance 'SERVERNAME' -Database 'AdminDB' -Query "SELECT * FROM   [dbo].[function](1, 'v5.0');"

return $QueryResult

With the info provided above, I am able to receive what looks to be the results that I want

$QueryResult = Invoke-Sqlcmd -ServerInstance 'SERVERNAME' -Database 'AdminDB' -Query "SELECT * FROM   [dbo].[function](1, 'v5.0');"
$NewResults = $QueryResult  | Select-object  -ExpandProperty  ConnectionString
Write-Verbose $NewResults

Get-NuixLicenseServer -ServerName $NewResults -Verbose

however, the function doesn’t run correctly. If i run the same function, but put type the value in like:

Get-NuixLicenseServer -ServerName SERVER01,SERVER02 -Verbose

The function runs perfectly, so it looks liek the results being stored from the query some how isn’t being interpretted correctly, I tried using .ToString() as well, that made no difference…

Thanks

Ah, thanks for the other info.

I think this will be because the function sees it as a string and not as any array. You’ll just need to cast the string as an array. Something like this should do it for you:

$NewResults = $NewResults.Split(',')
Get-NuixLicenseServer -ServerName $NewResults -Verbose

so your idea seemed to have worked, kind of…

Let me try to explain…

in this example, below, the ending result was what I expected, but see the comments in the script…

PS SQLSERVER:\> $QueryResult = Invoke-Sqlcmd -ServerInstance 'DBSERVER' -Database 'AdminDB' -Query "SELECT * FROM   [dbo].[NuixLicenseConnectionStringGet][1, 'v5.0'];"
$NewResults = $QueryResult  | Select-object  -ExpandProperty  ConnectionString

$NewResults = $NewResults.Split[',']
Write-Host $NewResults
Get-NuixLicenseServer -ServerName $NewResults -Verbose
SERVER01 SERVER02  ##here is what is written after the split
VERBOSE: SERVER01 - Nuix Server 5 service is Stopped...  ##shows SERVER01 is stopped, which is correct
VERBOSE: server02 - Nuix Server 5 service is Running... ##checks the service on SERVER02, however, it has it in lowercase now, when it originally was in CAPS.
VERBOSE: Loaded the Get-NuixLicenseStatus script
VERBOSE: License Status: OK
VERBOSE: The Nuix Server License Status is: OK  ##Everything seem sto check out just fine and I get a return of "server02"
server02

and even though the ending results in this case was what I expect, I also expect the CAPS to stay to upper case, because for some reason, if I have SERVER03 in there, in the place of SERVER02, it doesn’ recognize the server if it’s in lowercase, only in uppercase, which is weird…Why is that? and is there a way to force these to come back in uppercase, as that is how it will come from the DB?

Thanks

This will probably mean your server collection is configured as case sensitive. (CS instead of CI)

If you need to ensure it’s upper case, then you can use the ToUpper method of a string. So your code would now have the first line below happening before it splits the string into an array.

$NewResults = $NewResults.ToUpper()
$NewResults = $NewResults.Split(’,’)

Of if you wanted this on a single line…

$NewResults = $NewResults.ToUpper().Split(’,’)