I am learning Powershell by my own and I am trying to keep the best practices in my learning process. I am using a database.env file with some key value pair configuration lines, if I use it I present the error, if I put it on plain text to the code, it does work and I do not know why.

The error:
PS C:\Path\To\Folder> .\main.ps1 Connected to the database: MY_INSTANCE on server: MY_DATABASE "SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE';" Error: Exception calling "Fill" with "1" argument(s): "Could not find stored procedure 'SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE';'."

It tells me "Could not find stored procedure ...", but I am not using any, since my command type is ""Text" as it is mentioned below ( $command.CommandType = [System.Data.CommandType]::Text ).

1- This is my database.env file content:

# database.env

# Configuration
DATABASE_DEFAULT_SERVER_INSTANCE="MY_INSTANCE"
DATABASE_DEFAULT_NAME="MY_DATABASE"
DATABASE_DEFAULT_USERNAME="MY_USER"
DATABASE_DEFAULT_PASSWORD="MY_PASSWORD"

# QUERIES
SELECT_INFORMATION_SCHEMA="SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE';"

2- This is my main.ps1 file code:

# main.ps1

. .\src\Functions\StartEnvVariables.ps1
Start-EnvVariables

Import-Module .\src\Modules\Database\DBFunctions.psd1
Import-Module .\src\Modules\StoredProcedures\CreateProcedures.psd1

$connection = Get-Connection
    
try {
    $connection.Open()
    
    Write-Host "Connected to the database: $($connection.Database) on server: $($connection.DataSource)"

    # ✔️ Works fine
    # With burnt content
    #$query = "SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE';"

    # ✖️ Does not work
    # With environment variable
    $query = [System.Environment]::GetEnvironmentVariable("SELECT_INFORMATION_SCHEMA")

    Write-Host $query
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    $command.CommandType = [System.Data.CommandType]::Text

    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
    $dataTable = New-Object System.Data.DataTable
    $adapter.Fill($dataTable)

    foreach ($row in $dataTable.Rows) {
        $schema = $row["table_schema"]
        $tableName = $row["table_name"]

        Start-CreateProcedures -SchemaName $schema -TableName $tableName
    }
}
catch {
    Write-Host "Error: $($_.Exception.Message)"
}
finally {
    $connection.Close()
}

3- And this is how I load the environment variables:

function Start-EnvVariables {
    Get-Content .\config\database.env | ForEach-Object {
        $indexOfEquals = $_.IndexOf('=')
        if ($indexOfEquals -ge 0) {
            $name = $_.Substring(0, $indexOfEquals).Trim()
            $value = $_.Substring($indexOfEquals + 1).Trim()
            
            [System.Environment]::SetEnvironmentVariable($name, $value, [System.EnvironmentVariableTarget]::Process)
        }
    }
}

1- I tried converting my environment variable content to string:

$query = ([System.Environment]::GetEnvironmentVariable("SELECT_INFORMATION_SCHEMA")).toString()

2- To verify the single quotes are not affecting at the SQL execution:

"SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE';" "SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = '''BASE TABLE''';"