Archive

Archive for June, 2017

Powershell error – The term ‘Login-AzureRmAccount’ is not recognized as the name of a cmdlet, function, script file, or operable program

June 8, 2017 4 comments

 
If you are new to PowerShell like me then you would have encountered this ans similar errors.

Recently while executing one PowerShell script to connect to Azure Resource Manager I faced following error:

XyzPSScript.ps1 : The term ‘Login-AzureRmAccount’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
+ CategoryInfo : ObjectNotFound: (Login-AzureRmAccount:String) [XyzPSScript.ps1], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException,XyzPSScript.ps1

This error means that you do not have Azure PowerShell module installed on your system or server.
 

–> So, let’s see how to install the dependent modules.
 

1. Connect to PowerShell from command prompt (CMD) in Admin mode:

c:\> powershell.exe

Windows PowerShell
Copyright (C) 2016 Microsoft Corporation. All rights reserved.

 

2. Check the version of PowerShell:

PS c:\> $PSVersionTable.PSVersion

Major Minor Build Revision
—– —– —– ——–
5 1 14409 1005

 

3. Run the below command to check if you have PowerShellGet installed on your system:

PS c:\> Get-Module PowerShellGet -list | Select-Object Name,Version,Path

Name Version Path
—- ——- —-
PowerShellGet 1.0.0.1 C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PowerShellGet.psd1

 

4. Now we can easily install the Azure PowerShell from the PowerShell Gallery by runnign following command:

PS C:\> Install-Module AzureRM

NuGet provider is required to continue
PowerShellGet requires NuGet provider version ‘2.8.5.201’ or newer to interact
with NuGet-based repositories. The NuGet provider must be available in
‘C:\Program Files\PackageManagement\ProviderAssemblies’ or
‘C:\Users\essensed\AppData\Local\PackageManagement\ProviderAssemblies’. You can
also install the NuGet provider by running ‘Install-PackageProvider -Name
NuGet -MinimumVersion 2.8.5.201 -Force’. Do you want PowerShellGet to install
and import the NuGet provider now?
[Y] Yes [N] No [S] Suspend [?] Help (default is “Y”): Y

Untrusted repository
You are installing the modules from an untrusted repository. If you trust this
repository, change its InstallationPolicy value by running the Set-PSRepository
cmdlet. Are you sure you want to install the modules from ‘PSGallery’?
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help
(default is “N”):Y

On every prompt type ‘Y’ to proceed ahead.

While Installing the module you will see this screen:

5. Finally you just need to import the AzureRM module:

PS C:\> Import-Module AzureRM

This import command will not give any output, and you may proceed with the PS prompt.


Error – The following feature couldn’t be installed – .NET Framework 3.5

June 4, 2017 Leave a comment

 
I got error this message on a pop window while running a new application after installing it on my Windows Server 2012 Azure VM.


 

This feature can be installed from the “Windows Server Manager”, you will see its icon on the task bar besides the Start button, as shown below. Or you can directly type “servermanager” on CMD or Run prompt.

Click on the “Add Roles and Features”


 

A new window will open up “Add Roles and Features Wizard”, click Next.
On “Installation Type”, click Next again.
On “Server Selection”, select the current/default server and click Next.
On “Server Roles” click Next.
Now on Features select the “.NET Framework 3.5 Features” option, an click Next.
On Confirmation page you will see “Specify an alternate source path” link, click on that link, and a new window opens up, as shown below:


 

Here on the highlighted text box you need to specify the “side-by-side (SxS) folder” path of the OS installation media. So, if you have an ISO file you can mount it or if you have a dump of the setup, provide the path form the respective location. Finally click on Install button.
 

If you are on an Azure Windows VM, you will not have access to the ISO or installation media. In this case you can simply run a Windows Update from Control Panel–> System and Security –> Windows Update.


Categories: MS.net, Windows

Parsing nested JSON in customized SQL Tabular format – MSDN TSQL forum

June 2, 2017 Leave a comment

 
–> Question:

Basically I have a JSON file output that I want to put into a SQL table, but no matter what syntax I try it doesn’t seem to be working.

This is my sample JSON file contents:

{
   "destination_addresses":[
      "Orlando, FL, USA"
   ],
   "origin_addresses":[
      "New York, NY, USA"
   ],
   "rows":[
      {
         "elements":[
            {
               "distance":{
                  "text":"1,072 mi",
                  "value":1725756
               },
               "duration":{
                  "text":"15 hours 40 mins",
                  "value":56405
               },
               "status":"OK"
            }
         ]
      }
   ],
   "status":"OK"
}

What I want is a SQL table that has 2 columns, column 1 will be item names (Destination Address,Origin Address, Distance Text, Distance Value, Duration Text & Duration Value) and column 1 will be the actual data.

I can get OPENJSON to do what I want for destination address & origin address but when I try and using OPENJSON to read the nested items that exist in ‘rows – > elements’ I can’t seem to pick them up. Can anyone help with the right syntax?
 

–> Answer:

declare @str varchar(4000) = 
N'{
   "destination_addresses":[
      "Orlando, FL, USA"
   ],
   "origin_addresses":[
      "New York, NY, USA"
   ],
   "rows":[
      {
         "elements":[
            {
               "distance":{
                  "text":"1,072 mi",
                  "value":1725756
               },
               "duration":{
                  "text":"15 hours 40 mins",
                  "value":56405
               },
               "status":"OK"
            }
         ]
      }
   ],
   "status":"OK"
}'

SELECT 
	CONCAT_WS(' ',Destination_Addresses, Origin_Addresses,Distance_Text,
		Distance_Value,Duration_Text,Duration_Value) as Col1
	,@str as Col2
FROM OPENJSON (@str)
WITH (
    Destination_Addresses    VARCHAR(1000) '$.destination_addresses[0]',
    Origin_Addresses   VARCHAR(1000) '$.origin_addresses[0]',
    Distance_Text    VARCHAR(1000) '$.rows[0].elements[0].distance.text',
    Distance_Value   VARCHAR(1000) '$.rows[0].elements[0].distance.value',
    Duration_Text    VARCHAR(1000) '$.rows[0].elements[0].duration.text',
    Duration_Value   VARCHAR(1000) '$.rows[0].elements[0].duration.value'
) AS Orders