SQL Results to variables failing

PowerShell Help
1

I’m querying a database in the cloud, and using the data from the results to set variables in my caller script.

The variables drive a build process where I dynamically build out Azure resources.

The variable is not setting in the caller script. I’m a total PowerShell newbie so I’m not quite sure what I’m doing wrong. The function does work, and does return data, it just doesn’t seem to make it back to the caller or I’m setting the variable incorrectly in the caller.

Here is the function I’m using to query for the data, and to send the results back to the calling script:

Function Get-SubscriptionValues {

[CmdletBinding()]

Param(

    [Parameter(Mandatory=$true)]

    [string] $tenantid,

    [Parameter(Mandatory = $true)]

    [string] $subscriptionid)

Process

    {

         $thequery = "SELECT prefix, suffix, location FROM dbo.tablenamehere WHERE sub_id = '$subscriptionid'"



         $SQLConnection = New-Object System.Data.SqlClient.SqlConnection    

         $SqlConnection.ConnectionString = "Server=tcp:;Initial Catalog=;Persist Security Info=False;User ID=;Password=;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

         $SqlCmd = New-Object System.Data.SqlClient.SqlCommand    

         $SqlCmd.CommandText = $thequery

         $SqlCmd.Connection = $SqlConnection  

         $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter    

         $SqlAdapter.SelectCommand = $SqlCmd    

         $DataSet = New-Object System.Data.DataSet    

         $SqlAdapter.Fill($DataSet)    

         $SqlConnection.Close()              

         $thedata = $DataSet.Tables[0]

         return $thedata

        }

    }

In the caller script I set the variables like so:

$prefixname = ($thedata).prefix

I tried putting a pause in between when I called the function and when I set the variable to see if maybe the cloud was lagging on returning data to the caller, but the pause didn’t seem to have any affect.

2

The variable in the calling function should be set to the value returned by the called function:

# Using dot notation
$prefixName = (Get-SubscriptionValues).prefix

or using Select-Object

$prefixName = Get-SubscriptionValues | Select-Object -ExpandProperty prefix

The reason you can’t access $thedata directly is due to the variable scope. If they were in the same script, it would work. You can read more about Scopes in the help:

3

Ah, OK, that makes sense.

So, does that mean I need to change what I’m returning to the caller as well? Because right now I’m returning this: return $thedata. The sql query brings two other values back, in addition to prefix field.

4

There’s no need to modify it to get it working. The code I’ve given you, when run from the calling function will give you the result you need as it is.

You may consider querying for, and only returning, the prefix if you never use the other data. That might sense from an efficiency point of view, but it’s your call.