Query SQL and store result as a variable

This topic contains 8 replies, has 4 voices, and was last updated by Profile photo of Andrew Pearce Andrew Pearce 1 year, 8 months ago.

  • Author
    Posts
  • #23357
    Profile photo of Kawika Moss
    Kawika Moss
    Participant

    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?

  • #23358
    Profile photo of Kiran Reddy
    Kiran Reddy
    Participant

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


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

    OR

    $Results.Connectionstring | foreach { MyFunction $_}

  • #23360
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    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.

  • #23366
    Profile photo of Kawika Moss
    Kawika Moss
    Participant

    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
    
  • #23369
    Profile photo of Kawika Moss
    Kawika Moss
    Participant

    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

  • #23372
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    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
    
  • #23408
    Profile photo of Kawika Moss
    Kawika Moss
    Participant

    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

  • #23409
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    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(',')

  • #23435
    Profile photo of Andrew Pearce
    Andrew Pearce
    Participant

    Of if you wanted this on a single line...

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

You must be logged in to reply to this topic.