Thursday, February 15, 2018

Filtering Results of a Stored Procedure

Recently I needed to filter the results of a Stored Procedure, After some searching I found 2 options for this as below.


1. Using OpenRowSet Command

SELECT * FROM OPENROWSET ('SQLOLEDB', 'Server=ServerName;TRUSTED_CONNECTION=YES;', 'EXEC sp_Who2')

Remember in this method this feature should be enabled in the server.

2. Using Temporary Tables.

-- Creating a temporary table.
CREATE TABLE #tblSPWho2
(SPId INT, Status NVARCHAR(200), LoginName NVARCHAR(200), HostName NVARCHAR(200), BlockedBy NVARCHAR(200), DBName NVARCHAR(200),
Command NVARCHAR(200), CPUTime BIGINT, DiskIO BIGINT, LastBatch NVARCHAR(200), ProgramName NVARCHAR(200), SPId2 INT, RequestId NVARCHAR(200))

-- Inserting the results of the Stored Procedure into the temporary table.
INSERT INTO #tblSPWho2
EXEC sp_Who2

-- Doing the required filtering using the temporary table.
SELECT * FROM #tblSPWho2 WHERE LoginName = 'Domain\UserName'

-- Removing the temporary table.
DROP TABLE #tblSPWho2

SP_Who2 Stored Procedure will bring all the processes that is currently active in SQL server with the relevant information, the above query will filter the results and will only display the processes that are initiated by the given username (Domain\UserName).

Tuesday, December 05, 2017

It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error can be caused by a virtual directory not being configured as an application in IIS.

Today a friend of mine started getting this error while he tried to deploy his solution to a server. This only started when ‘Precompile during publishing’ option is selected.

image

“Error 20 It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error can be caused by a virtual directory not being configured as an application in IIS.”

After having a look, I identified this was caused by a temporary copy of a Web.Config file. Earlier developer created a copy of the Web.Config and placed it inside a folder named Backup within the solution. This backup config file had authentication tag (<authentication>), which is a tag that can be only used in global Web.Config file or the Web.Config file in applications root. Due to the presence of authentication tag, compiler started complaining that there is a virtual directory not configured as a application in IIS.

The solution for my friend’s issue was to simply exclude the copy of the Web.Config file from project by right clicking the file. Then the project started deploying happily.

Tuesday, June 27, 2017

Cheap Direct Flights From Australia to Sri Lanka

Have you heard the good news that SriLankan Airlines is going to restart their direct flights to Australia. Yes it is true, SriLankan is going to start direct services between Sri Lanka and Australia from November 2017. They have decided to do this after acquiring brand new aircrafts from Airbus Industries. So when compared to other airlines, you will fly in more recently built modern aircraft with SriLankan.

SriLankan A340

SriLankan A333

With this new competition I think we will see better fairs to more Asian destinations. Introductory prices for the month of November 2017 from SriLankan seems very good as you see from some of the screenshots I have taken from the web site. Reserve your seats quickly by visiting SriLankan web site.

image

imageimageimageimage

Tuesday, June 20, 2017

Enabling .NET 3.x on Windows 8 and Up

As you may be knowing, now you do not need to separately download and install .Net framework as we used to do with the older .Net frameworks. Since it is coming with Windows, you can just go to “Turn Windows Features On or Off” screen and enable framework you are after. It is simple right?

But in one of the machines it was not that simple for me. The installation tried to download files from Windows Update and was failing mentioning that it cannot get connected to Windows Update when the machine is connected to the internet without any issues.

The solution is to use DISM (Deployment Image Servicing and Management) tool to get it installed.

First you need to find a Windows setup media, a setup DVD or an ISO downloaded will work.

Then use the below command to enable the feature using a local source. Remember to open the Command Window as an Administrator of the machine.

DISM /Online /Enable-Feature /FeatureName:NetFx3 /All /LimitAccess /Source:E:\Sources\sxs

/Online – Targets the currently active and running OS.

/LimitAccess – If some installation files are missing, this will check Windows Update for the missing files. For not to check use /LimitAccess:True.

/Source – Is the location to find the source files, E: is my virtual drive which I mounted the previously downloaded Windows ISO.

If typed correctly, there will be a progress bar showing the installation progress.

image

Wednesday, May 03, 2017

Genoa Falls, Genoa, Victoria, Australia

If you are interested in waterfalls and not too fussed about the height, I think you will like Genoa falls in Genoa in the state of Victoria, Australia. Belonging to the Croajingolong National Park, Genoa falls is a set of small cascades of water each with a height of no more than 3 meters, located near the Victorian and New South Wales border.
Genoa falls is located in about 488 kilometers East from the city of Melbourne and can be reached in about 5 and half hours’ drive. Access to the falls is via an unsigned dirt road which can be easily missed. I am sure there are many who are passing in Princess highway daily without knowing there is a small waterfall in this location. After turning to the dirt road, you just need to drive about 200 meters to reach the place to park the car. There is space to park 3 or 4 cars comfortably. This will not be a problem since this is not much of a famous attraction, so you will find space to park your car at any time of the year.
At the car park, there will be a small sign mentioning that you are in the Genoa falls in Croajinolong national park. Camping, open fires and pets are not allowed to make you and others safe at the location. You have to walk about 500 meters to reach Genoa waterfall from the car park, I would say the walk is moderately difficult because the last part descending to the Genoa creek is having one or two steep steps and path is narrow. Better look for reptiles such as snakes and lizards on your way. Keep a good eye at your children if you got them on your trip since the area is hazardous specially with uncovered waterholes.
Once you go down the final set of steps, turn left to reach the main attraction. It is a Basalt floor all the way to the waterfall with random water holes. In dry weather, you will be able to reach the waterfall without getting your feet wet.
After the waterfall the stream will divide into two parts, one will be going behind the boulders and hidden, the other smaller stream will be visible, follow it downstream. In about 30 meters, to your left you will see the other stream joining back with two small waterfalls.
Another good thing is that you can walk the breadth and length of the stream in dry weather without getting wet. When wet, be mindful, the area will be slippery with the algae build-up. I feel the water is bit Ionic from the slight Brown colour seen.
Apart from space to park the car, there are no facilities available on site. So, if you plan to have a barbecue or a picnic, this is not the place. Also, be prepared to carry your garbage out, since there are no garbage bins available.

Score out of 5
1 Parking Availability Adequate

3

2 Wheelchair Access Main Viewpoint – No

Secondary Viewpoint – No

Picnic Areas – No

0

3 Accessibility Moderately difficult walk of about 15 minutes from car park.

2

4 Facilities Covered Sitting Areas – No

Toilets – No

Barbecue – No

Garbage Bins – No

1

5 Scenery Waterfall

Stream

Mountains

3


Hope you will enjoy the video.

Map

Tuesday, May 02, 2017

Agnes Falls - Hazel Park - Victoria - Australia

Today I thought to post an article about the highest waterfall in Victoria, which is Agnes Falls in Hazel Park, Hazel Park is a suburb in the state of Victoria in Australia. Agnes falls is located about 200 kilometres South East of Melbourne city, which can be reached in about 2 and half hours’ drive from Melbourne city.

At the end of the Agnes falls road you can park your car and walk for about 100 metres to reach the first viewpoint. This part is mostly downhill so is easily accessible. Walking further 200 metres will get you to the main 2 viewpoints to enjoy the beautiful Agnes falls. There will be bit of uphill walk involved. If needing a break there will be a seat on the way, but nice scenery will facilitate the walking and you will be there in no time

At the end of the short walk you will have to go down few steps, so wheel chair and pram access is not possible. After walking down turning left will take you to viewpoint 2, which overlooks the mountains and the distance Toora windfarm.

All viewpoints are well guarded and safe for little children, but if it is a rainy day, look for puddles of water in the main viewing area otherwise you will end up in wet shoes / feet.

Spanning 59 meters, Agnes falls becomes the highest waterfall in the state of Victoria and has 3 cascades of water. Due to the quality of the water in Agnes river, it is found ideal for domestic use and Agnes river is used as a source of water for the nearby Toora township. The 1924 proposed water supply scheme ensured year-round gravity fed water for the township, and the first part was completed on 1926.

There is wheelchair accessible toilets, few tables with benches and 2 undercover tables with seating to have a picnic. Remember to bring some bags to carry your garbage since there are no waste bins provided.

      Score out of 5
1 Parking Availability Adequate

4

2 Wheelchair Access

Main Viewpoint – No

Secondary Viewpoint – Yes

Picnic Areas – Yes

3

3 Accessibility Easily accessible within about 10 minutes walk from carpark.

4

4 Facilities

Covered Sitting Areas – Yes

Toilets – Yes

Barbecue – No

Garbage Bins – No

3

5 Scenery

Waterfall

Mountains

4

Watch the video and get a feel of the location before you plan your trip to Agnes Falls.

Map

Loy Yang Power Station – Traralgon Victoria – Australia

In my recent travels to Victoria I visited the Loy Yang power station. This power station consists of two sections named as “Loy Yang A” and “Loy Yang B” which was built in 1980s, both these plans are coal fired thermal power stations. Taken together these generate 4300 MW of power which is about 30% of Victoria’s current power needs and is the largest power station in Australia (when taken together). AGL owns and operates Loy Yang A and the adjacent coal mine.

There are no tours to look inside the power station but there is a picnic area where you can see the plants from a distance.

      Score out of 5
1 Parking Availability  

4

2 Wheelchair Access Easy

5

3 Accessibility Easily accessible within about 3 minutes walk from carpark.

4

4 Facilities

No covered sitting areas.

Toilets – No

Barbecue – Yes

2

5 Scenery  

2

Map

Sunday, March 26, 2017

Removing all Partitions from a Disk

Today I thought of writing an article on how to remove all the partitions from a disk so you can re-use the disk.

If you tried removing existing partitions using the Computer Management (type computer management on the start menu to open it) tool in Windows you will find that, you can delete partitions by right clicking on the partition / volume and clicking on Delete Volume option.

image

However you will notice Computer Management is useless on some of the protected volumes since the options to work on them will not be available.In this case you will need to use the DiskPart utility in the Command Prompt.

image

In the Start type Cmd or Command Prompt to open a command prompt. Then type DiskPart, it is a powerful utility which can even work on protected volumes.

In my case, I would like to work on Disk 3 to remove all the existing volumes on it. Below are the steps required to clean the drive.

1. Select the disk.

List all the disks by typing List Disk, this will list all the disks connected and active at the time in your computer including the disks connected via USB ports.

image

Once you identify the disk you need, selection can be made by using the command Select Disk x. In my case Select Disk 3.

To confirm, list all the partitions on the selected list by using the command List Partition.

image

2. Delete all partitions by using the Clean command.

Ensure the proper disk is selected by Listing all disks by command List Disk, the currently focused disk will have a star at the beginning since recovering disks is not easy.

Type Clean and press enter. This will remove all the partitions from the disk. You can check the results by re issuing the List Partition command.

image

Close the DiskPart tool by entering the command Exit.

Computer Management will now show that the disk is uninitialized as below, Initialize the disk and create partitions / volumes as necessary to use it again.

image

Removing a Partition from a Disk

You can remove simple partitions by using the Computer Management tool found in Windows. Search for it on the Start menu to launch it.

But if it is a protected partition you will need to use the tool DiskPart. In the Start menu type Cmd or Command Prompt to open a command prompt. Then type DiskPart, it is a powerful utility which can even work on protected volumes.

To delete one partition, follow the steps.

1. List all disks by using the command List Disk.

2. Select the disk by using Select Disk x command.

3. You can confirm the disk by listing all the partitions on it by using the command List Partition.

4. Select the partition by using the command, Select Partition x command.

5. Delete the partition by command Delete Partition Override.

Override parameter needs to be passed if the partition is not a simple data partition.

You can confirm the deletion by re-listing the partitions on the disk by List Partition command.

image_thumb[15]

Sunday, March 19, 2017

SQL – The target principal name is incorrect. Cannot generate SSPI context.

Recently in one of my Virtual Machine (VM)s I received the above error message when trying to connect to a SQL Server which I used to connect on other times.

image

After having a look I found that the trust between my virtual machine and the domain was broken. You can find the status of the secure channel by using the PowerShell command Test-ComputerSecureChannel.

image

This will also be evident when you try to login to the computer using a domain account. It will generate the following message.

image[6]

To fix this you can try using the below methods.

1. PowerShell

Use the command Test-ComputerSecureChannel.

If your current login has the required access in the domain you can use the below command.

Test-ComputerSecureChannel –Repair

If you need to use another account than the current logged user then you need to use the –Credential parameter when calling the command.

Test-ComputerSecureChannel –Repair –Credential MyDomain\MyUser

2. Joining the domain again.

This will also get fixed by removing the machine from the domain and adding it back. Before removing the computer from the domain make sure you have access to a local administrator account on the computer. Otherwise you will not have a way to login to the computer.

This can be achieved by going to computer system properties,

  1. removing the computer from the current domain,
  2. restarting the computer.
  3. adding the computer to the domain again
  4. restarting the computer

To avoid the two restarts you can try using the following PowerShell commands.

$myPC = Get-WmiObject Win32_ComputerSystem
$myPC.UnjoinDomainOrWorkGroup("Account Password", "Account Username”, 0)
$myPC.JoinDomainOrWorkGroup("Domain", "Account Password", "Account Username", $null, 3)
Restart-Computer -Force

Monday, December 26, 2016

Open Live Writer – Plugin Not Working

While trying to setup and use the Code Plugin by Rich Hewlett I had trouble getting the plugin loaded into Live Writer. I did add the registry entry required as mentioned in the site, but still the plugin did not load.

Registry

Location - HKEY_CURRENT_USER\SOFTWARE\OpenLiveWriter\PluginAssemblies

Key - SyntaxHighlight_WordPressCom_OLWPlugIn

Value - C:\Users\Arjuna\AppData\Local\OpenLiveWriter\Plugins\SyntaxHighlight_WordPressCom_OLWPlugIn.dll

After a while I figured out that, when loading the plugin Live Writer encounters the following error.

"System.IO.FileLoadException: Could not load file or assembly 'file:///C:\Users\Arjuna\AppData\Local\OpenLiveWriter\Plugins\SyntaxHighlight_WordPressCom_OLWPlugIn.dll' or one of its dependencies. Operation is not supported. (Exception from HRESULT: 0x80131515)
File name: 'file:///C:\Users\Arjuna\AppData\Local\OpenLiveWriter\Plugins\SyntaxHighlight_WordPressCom_OLWPlugIn.dll' ---> System.NotSupportedException: An attempt was made to load an assembly from a network location which would have caused the assembly to be sandboxed in previous versions of the .NET Framework. This release of the .NET Framework does not enable CAS policy by default, so this load may be dangerous. If this load is not intended to sandbox the assembly, please enable the loadFromRemoteSources switch. See http://go.microsoft.com/fwlink/?LinkId=155569 for more information.

This is due to a security feature of .Net Framework 4 or later. Before framework 4, if a DLL is downloaded (which is created in another computer), they used to run in full trust in the zone the assembly is running, but with frameworks 4 and later, downloaded DLLs will not run by default. To make them run, simply grant the DLL full access by going to file properties and selecting the Unblock checkbox in the security section as seen in the below image. This will apply to any plugin you download from internet. Also keep in mind to do this to DLLs you trust safe. Otherwise your computer will be unsafe.

image

To troubleshoot plugin and other errors you can refer to Open Live Writer log file located in C:\Users\Arjuna\AppData\Local\OpenLiveWriter\Open Live Writer.log.

Saturday, September 17, 2016

Monitoring Windows Services using PowerShell

Recently I was interested in finding a way to monitor a windows service. What I needed was to check whether the service is running and if not running get a notification and try to restart the service. Following PowerShell script does exactly that, it checks for Microsoft CRM Asynchronous Service and the Microsoft CRM Asynchronous Maintenance Service activity and send 2 emails to Admin and Dev. This needs to be then scheduled using Windows Task Scheduler or SQL Server Job.

### Checking for CRM Async and Maintenance service failure and try restarting, if failing send an email notification.

 

## Function to send mail notification.

function Send_Email ([string]$strEmailSubject, [string]$strEmailBody)

{

       $EmailFrom = "Arjuna@Email.com"

       $EmailTo = "Admin@Email.com, Dev@Email.com"

       $EmailSubject = $strEmailSubject

       $EmailBody = $strEmailBody

       $EmailSMTPServer = "SMTP.server.com"

       ## Creating Mail Message object.

       $SMTPMessage = New-Object System.Net.Mail.MailMessage $EmailFrom, $EmailTo, $EmailSubject, $EmailBody

       ## Enabling HTML mail body.

       $SMTPMessage.IsBodyHtml = $true

       ## Creating SMTP client object.

       $SMTPClient = New-Object System.Net.Mail.SMTPClient $EmailSMTPServer

       ## Sending mail.

       $SMTPClient.Send($SMTPMessage)

       ## Sending mail method 2.

       ##send-mailmessage -from "Arjuna@Email.com" -to "Admin@Email.com, Dev@Email.com" -subject "CRM Async Service Failed" -body "Please check." -smtpserver "SMTP.server.com"

       ## Sending mail method 2 using parameters.

       ##send-mailmessage -from $EmailFrom -to $EmailTo -subject $EmailSubject -body $EmailBody -smtpserver $EmailSMTPServer

}

 

## Function to check the service activity.

function Check_Service

{

       ## Get all services which has a Name like MSCRMAsyncService, Start Mode is Auto and service State is Running.

       $FailedAsyncService = Get-WmiObject Win32_Service | Where-Object {$_.Name -like 'MSCRMAsyncService' -and $_.StartMode -eq 'Auto' -and $_.State -ne 'Running'} | Select-Object DisplayName

       ## For Testing.

       ##Write-Host "A: " $FailedAsyncService

 

       ## Get all services which has a Name like MSCRMAsyncService$maintenance, Start Mode is Auto and service State is Running.

       $FailedAsyncMainteService = Get-WmiObject Win32_Service | Where-Object {$_.Name -like 'MSCRMAsyncService$maintenance' -and $_.StartMode -eq 'Auto' -and $_.State -ne 'Running'} | Select-Object DisplayName

       ## For Testing.

       ##Write-Host "B: " $FailedAsyncMainteService

 

       ## Checking whether the Async Service has failed.

       if ($FailedAsyncService -ne $NULL)

       {

              ## Trying to start the failed Async Service.

              Start-Service -displayname "Microsoft Dynamics CRM Asynchronous Processing Service"

              ## Service Name can also be used to start the servie.

              ##Start-Service MSCRMAsyncService

              ## Get all services which has a Name like MSCRMAsyncService, Start Mode is Auto and service State is Running.

              $AsyncServiceStarted = Get-WmiObject Win32_Service | Where-Object {$_.Name -like 'MSCRMAsyncService' -and $_.StartMode -eq 'Auto' -and $_.State -ne 'Running'} | Select-Object Name

              ## Checking the service to see whether it started.

              if ($AsyncServiceStarted -ne $NULL)

              {

                     ## Calling Send_Mail function to notify.

                     Send_Email ("CRM Async Service Failed.") ("System has detected that the following CRM Async Service has failed. System automatically tried restarting the service but it was unsuccessful. Try manual start. <BR/><BR/>" + $FailedAsyncService)

              }

              else

              {

                     ## Calling Send_Mail function to notify.

                     Send_Email ("CRM Async Service Restarted.") ("System has detected that the following CRM Async Service has failed. System automatically tried restarting the service and it was successful. <BR/><BR/>" + $FailedAsyncService)

              }

       }

 

       ## Checking whether the Async Maintenance Service has failed.

       if ($FailedAsyncMainteService -ne $NULL)

       {

              ## Trying to start the failed Async Maintenance Service.

              Start-Service -displayname "Microsoft Dynamics CRM Asynchronous Processing Service (maintenance)"

              ## Get all services which has a Name like MSCRMAsyncService$maintenance, Start Mode is Auto and service State is Running.

              $AsyncMainteServiceStarted = Get-WmiObject Win32_Service | Where-Object {$_.Name -like 'MSCRMAsyncService$maintenance' -and $_.StartMode -eq 'Auto' -and $_.State -ne 'Running'} | Select-Object Name

              ## Checking the service to see whether it started.

              if ($AsyncMainteServiceStarted -ne $NULL)

              {

                     ## Calling Send_Mail function to notify.

                     Send_Email ("CRM Async Service Failed.") ("System has detected that the following CRM Async Service has failed. System automatically tried restarting the service but it was unsuccessful. Try manual start. <BR/><BR/>" + $FailedAsyncMainteService)

              }

              else

              {

                     ## Calling Send_Mail function to notify.

                     Send_Email ("CRM Async Service Restarted.") ("System has detected that the following CRM Async Service has failed. System automatically tried restarting the service and it was successful. <BR/><BR/>" + $FailedAsyncMainteService)

              }

       }

}

 

## Calling the Check_Service function.

Check_Service