Cannot find type [Microsoft.Azure.Cosmos.Table.TableQuery] error display when running Get-AzTableRow command on VSTS azure powershell

Question

I am trying to run azure powershell on VSTS with Get-AzTableRow command.

Powershell :

$ConfigurationPortalName = "xxxx"
$ResourceGroupName = "xxxx"
$Location = "West Europe"
$SubscriptionName = "xxxx"
$SubscriptionId = "xxxx"
$AdHocReleaseDefinitionId = "xxxx"
$MultipleEnvReleaseDefId = "xxxx"
$StorageConnectionString = "xxxx"
$StorageContainerName = "xxxx"
$PortalStorageAccountName = "xxxx"
$SkuName = "Standard_LRS"
$tableName = "Subscription"
$partitionKey = "Subscription"
$storageAccountName = $PortalStorageAccountName.ToLower()

write-host "*Install modules*"
Install-Module -Name Az.Accounts -RequiredVersion 1.5.2 -Scope CurrentUser -AllowClobber -Force
Install-Module -Name Az.Resources -RequiredVersion 1.3.1 -Scope CurrentUser -AllowClobber -Force
Install-Module -Name Az.Storage -RequiredVersion 1.3.0 -Scope CurrentUser -AllowClobber -Force
Install-Module -Name AzTable -RequiredVersion 2.0.2 -Scope CurrentUser -AllowClobber -Force 

write-host "*Current version*"
Get-Module Az.Storage
Get-Module Az.Accounts
Get-Module AzTable
Get-Module Az.Resources

write-host "*remove module*"
try
{
    Remove-Module -Name AzureRM.Profile -Force
    Remove-Module -Name Az.Accounts -Force
    Remove-Module -Name Az.Storage -Force
    Remove-Module -Name AzTable -Force
}
catch
{
    $_.Exception.Message
}

write-host "*Import module version*"
Import-Module -Name Az.Accounts -RequiredVersion 1.5.2 -Force
Import-Module -Name Az.Resources -RequiredVersion 1.3.1 -Force
Import-Module -Name Az.Storage -RequiredVersion 1.3.0 -Force
Import-Module -Name AzTable -RequiredVersion 2.0.2 -Force

write-host "*Final version*"
Get-Module Az.Accounts
Get-Module Az.Storage
Get-Module AzTable
Get-Module Az.Resources

$storageAccount = Get-AzStorageAccount -ResourceGroupName $ResourceGroupName -Name $storageAccountName
Write-host "strgacc : " $storageAccount

if (!$storageAccount) 
{
    $storageAccount = New-AzStorageAccount -ResourceGroupName $ResourceGroupName `
        -Name $storageAccountName `
        -Location $Location `
        -SkuName $SkuName `
        -Kind Storage

    Write-host 'Storage Account Created !!!'
}
else 
{
    Write-host 'Storage Account Already Exist !!!'
}

$storageAccountKeys = Get-AzStorageAccountKey -ResourceGroupName $ResourceGroupName -AccountName $storageAccountName
$newStorageConnectionString = [string]::Format('DefaultEndpointsProtocol=https;AccountName={0};AccountKey={1};EndpointSuffix=core.windows.net', $storageAccountName, $storageAccountKeys[0].Value);

$webApp = Get-AzWebApp -ResourceGroupName $ResourceGroupName -Name $ConfigurationPortalName;
$appSettingList = $webApp.SiteConfig.AppSettings;

$hash = @{};
ForEach ($kvp in $appSettingList) 
{
    $hash[$kvp.Name] = $kvp.Value;
}

if ($hash['Config:Storage:ConnectionString'] -ne $newStorageConnectionString) 
{
    $hash['Config:Storage:ConnectionString'] = $newStorageConnectionString;

    Set-AzWebApp -ResourceGroupName $ResourceGroupName -Name $ConfigurationPortalName -AppSettings $hash;

    Write-host 'WebApp Configured With New Storage Account Key successfully !!!'
}
else 
{
    Write-host 'WebApp Already Configured With Storage Account Key !!!' 
}

$ctx = $storageAccount.Context

#Get Storage Tabel Reference
$cloudTable  = (Get-AzStorageTable -Name $tableName -Context $ctx -ErrorVariable ev -ErrorAction SilentlyContinue).CloudTable

#If no reference then create new table else check for default entry
if ($ev) 
{
    New-AzStorageTable -Name $tableName –Context $ctx;
    $cloudTable  = (Get-AzStorageTable -Name $tableName -Context $ctx -ErrorVariable ev -ErrorAction SilentlyContinue).CloudTable
    Write-host 'Table Created :' $cloudTable
}

Write-Host "Table :" $cloudTable

if ($cloudTable) 
{
    #$rows = Get-AzTableRow -table $cloudTable -partitionKey $partitionKey | ft
    $rows = "aaa"
    write-host "Rows :" $rows
    #if (!$rows)
    if ($rows) 
    {
        Add-AzTableRow `
            -table $cloudTable `
            -partitionKey $partitionKey `
            -rowKey $SubscriptionName `
            -property @{`
                            "SubscriptionId"          = $SubscriptionId; `
                            "AdHocReleaseDefinitionId" = $AdHocReleaseDefinitionId; `
                            "MultipleEnvReleaseDefId"  = $MultipleEnvReleaseDefId; `
                            "StorageConnectionString" = $StorageConnectionString; `
                            "StorageContainerName"    = $StorageContainerName
                        }

        Write-host 'Row successfully inserted !!!'
    }
    else 
    {
        Write-host 'Default rows already configured !!!'
    }
}
else 
{
    Write-host 'Table Storage Reference Not Found !!!'
}
Write-Host 'done'

Error :

2019-05-17T08:13:38.6380364Z ##[error]Cannot find type [Microsoft.Azure.Cosmos.Table.TableQuery]: verify that the assembly containing this type is loaded.

Note : Running fine in local powershell ISE. Using agent 'Hosted VS2017'in VSTS

Version of installed modules in local and VSTS are same when i run the below commands to check:

Get-Module Az.Storage
Get-Module Az.Accounts
Get-Module AzTable
Get-Module Az.Resources

then version i am getting :

Az.Accounts 1.5.2
Az.Storage 1.3.0
AzTable 2.0.2
Az.Resources 1.3.1

Now the new error displayed on line :

$storageAccount = Get-AzStorageAccount -ResourceGroupName $ResourceGroupName -Name $storageAccountName

Error :

2019-05-22T10:38:30.2164835Z ##[error]Method 'get_SerializationSettings' in type 'Microsoft.Azure.Management.Internal.Resources.ResourceManagementClient' from assembly 'Microsoft.Azure.PowerShell.Clients.ResourceManager, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' does not have an implementation.

Answer 1

Finally able to run the script on VSTS :

Note : Azure Powershell version must be 4.* (preview)

$ConfigurationPortalName = "xxxx"
$ResourceGroupName = "xxxx"
$Location = "West Europe"
$SubscriptionName = "xxxx"
$SubscriptionId = "xxxx"
$AdHocReleaseDefinitionId = "xxxx"
$MultipleEnvReleaseDefId = "xxxx"
$StorageConnectionString = "xxxx"
$StorageContainerName = "xxxx"
$SkuName = "Standard_LRS"
$tableName = "Subscription"
$partitionKey = "Subscription"
$storageAccountName = "xxxx"

write-host "*Install Modules*"
Install-Module -Name Az.Accounts -RequiredVersion 1.5.2 -Scope CurrentUser -AllowClobber -Force
Install-Module -Name Az.Storage -RequiredVersion 1.3.0 -Scope CurrentUser -AllowClobber -Force
Install-Module -Name AzTable -RequiredVersion 2.0.2 -Scope CurrentUser -AllowClobber -Force 

write-host "*remove module*"
Remove-Module -Name Az.Accounts -ErrorAction SilentlyContinue -Force
Remove-Module -Name Az.Storage -ErrorAction SilentlyContinue -Force
Remove-Module -Name AzTable -ErrorAction SilentlyContinue -Force

write-host "*Import Module version*"
Import-Module -Name Az.Accounts -RequiredVersion 1.5.2 -Force
Import-Module -Name Az.Storage -RequiredVersion 1.3.0 -Force
Import-Module -Name AzTable -RequiredVersion 2.0.2 -Force

write-host "*Get Module version currently in use*"
Get-Module Az.Accounts
Get-Module Az.Storage
Get-Module AzTable

$storageAccount = Get-AzStorageAccount -ResourceGroupName $ResourceGroupName -Name $storageAccountName

if (!$storageAccount) 
{
    $storageAccount = New-AzStorageAccount -ResourceGroupName $ResourceGroupName `
        -Name $storageAccountName `
        -Location $Location `
        -SkuName $SkuName `
        -Kind Storage

    Write-host 'Storage Account Created !!!'
}
else 
{
    Write-host 'Storage Account Already Exist !!!'
}

$storageAccountKeys = Get-AzStorageAccountKey -ResourceGroupName $ResourceGroupName -AccountName $storageAccountName
$newStorageConnectionString = [string]::Format('DefaultEndpointsProtocol=https;AccountName={0};AccountKey={1};EndpointSuffix=core.windows.net', $storageAccountName, $storageAccountKeys[0].Value);
$webApp = Get-AzWebApp -ResourceGroupName $ResourceGroupName -Name $ConfigurationPortalName;
$appSettingList = $webApp.SiteConfig.AppSettings;
$hash = @{};

ForEach ($kvp in $appSettingList) 
{
    $hash[$kvp.Name] = $kvp.Value;
}

if ($hash['Config:Storage:ConnectionString'] -ne $newStorageConnectionString) 
{
    $hash['Config:Storage:ConnectionString'] = $newStorageConnectionString;
    Set-AzWebApp -ResourceGroupName $ResourceGroupName -Name $ConfigurationPortalName -AppSettings $hash;
    Write-host 'WebApp Configured With New Storage Account Key successfully !!!'
}
else 
{
    Write-host 'WebApp Already Configured With Storage Account Key !!!' 
}

$ctx = $storageAccount.Context

#Get Storage Tabel Reference
$cloudTable  = (Get-AzStorageTable -Name $tableName -Context $ctx -ErrorVariable ev -ErrorAction SilentlyContinue).CloudTable

#If no reference then create new table else check for default entry
if($ev)
{
    New-AzStorageTable -Name $tableName –Context $ctx;
    $cloudTable  = (Get-AzStorageTable -Name $tableName -Context $ctx).CloudTable
    Write-host 'Table created successfully !!!'
}

Write-Host "Table name :" $cloudTable

if ($cloudTable) 
{
    $rows = Get-AzTableRow -table $cloudTable -partitionKey $partitionKey | ft
    if (!$rows)
    {
        Add-AzTableRow `
            -table $cloudTable `
            -partitionKey $partitionKey `
            -rowKey $SubscriptionName `
            -property @{`
                            "SubscriptionId"          = $SubscriptionId; `
                            "AdHocReleaseDefinitionId" = $AdHocReleaseDefinitionId; `
                            "MultipleEnvReleaseDefId"  = $MultipleEnvReleaseDefId; `
                            "StorageConnectionString" = $StorageConnectionString; `
                            "StorageContainerName"    = $StorageContainerName
                        }

        Write-host 'Row successfully inserted !!!'
    }
    else 
    {
        Write-host 'Default Row already configured !!!'
    }
}
else 
{
    Write-host 'Table Storage Reference Not Found !!!'
}

Write-Host 'Completed !!!'

Answer 2

based on Manage organization billing in Azure DevOps - Sprint 150 Update #Support for Azure PowerShell Az module

You need to install the Az module on your agent machine if you are using private agents.

you should use private agent as you can't update the version of powershell module on hosed agent(eg: Hosted VS2017).

Answer 3

For the issue that you provide:

2019-05-17T08:13:38.6380364Z ##[error]Cannot find type [Microsoft.Azure.Cosmos.Table.TableQuery]: verify that the assembly containing this type is loaded

The reason is that you need to use the module Az.Storage version 1.1.0 or greater than. Take a look at the troubling in this document. And you can use the PowerShell command:

Update-Module -Name Az

Then open a new PowerShell session and it will work.