I have a private project going on where I’m in the data cleaning and import phase. This weekend’s problem has been a lot of date columns stored as string on 2 different formats (yyyymmdd and ddmmyyyy) and different seperator characters (‘-‘ and ‘.’) all over the place. And these strings I want in date datatype. I’m a lazy programmer and had to make string2date function to use for my import stuff. Here is my first version of the function.
CREATE FUNCTION [dbo].[String2Date] ( @string NVARCHAR(100), @style smallint = 104 ) RETURNS date AS BEGIN IF (CHARINDEX('-', @string, 1) = 5 OR CHARINDEX('.', @string, 1) = 5) BEGIN SET @style = 102 ; END; RETURN CONVERT(date, @string, @style) ; END
This function is not perfect, but suits my purpose for the moment. It would probably give some exceptions now and then, but I don’t care :)
Today I found a pretty nice way to delete duplicate rows in a table on SQL Server. I had a table with 25,000 rows where 7,500 rows where rows containing one or more duplicates. I was not very eager to manually delete these duplicate, so I started to googling for answers. I found many different approaches, but suddenly I found my answer at stackoverflow.com. This thread help me rewriting a simple SQL statement after I added an [Id] column to uniquely identify a row. The clue is to use Common Table Expression (CTE) in SQL Server together with the OVER() function to create an unique row number for all duplicates within the key expression (in my case [Name] column), and ordering by the [Id] column. I only want to keep the first row for each duplicate item. Therefore, deleting all [rowno] greater than 1.
WITH cte_duplicates AS ( SELECT [Name], row_number() OVER ( PARTITION BY [Name] ORDER BY [Id] ) AS [rowno] FROM [dbo].[fm2011] ) DELETE FROM cte_duplicates WHERE [rowno] > 1
I have a goal for February to complete 3 challanges for “KiPi 2015″. It should be possible even if I’m going to sell the house in this period. The last challenge last for 32.5 hours and cover many topics within Hybrid Cloud design and implementation. Designing private cloud data centers is not my current work focus, but very useful knowledge in my work as Cloud Solution Architect.
This module consist of the following parts:
- Part 1: Modernizing Your Datacenter Jump Start (Level 200, 52 points)
- Part 2: Hybrid Cloud Workloads: Storage and Backup (Level 200, 52 points)
- Part 3: Hybrid Cloud Workloads: Disaster Recovery and High Availability (Level 200, 44 points)
- Part 4: Hybrid Cloud Workloads-Websites (Level 200, 50 points)
- Part 5: Hybrid Cloud Workloads SQL (Level 200, 52 points)
- Part 6: Deploying Linux VMs on Microsoft Azure (Level 300, 29 points)
- Part 7: Migrating VMs from Amazon AWS to Microsoft Azure (Level 200, 36 points)
- Part 8: Automating the Cloud with Azure Automation (Level 300, 42 points)
I’m having just some hours left of the first part of my KiPi 2015 Challenge for “Cloud Development“, and the next course path is “Mobile Development”. This path will contain video sessions on C#, XAML, Universal app development and how Xamarin and Visual Studio can be used for cross-plattform development. These courses are about 32 hours in total watching.
- Part 1: Programming in C# Jump Start (Level 200, 38 points)
- Part 2: Developing Universal Windows Apps with C# and XAML (Level 200, 118 points)
- Part 3: Cross-Platform Development with Xamarin & Visual Studio ( Level 200, 47 points)
As I wrote in the previous post, I’m participating in the KiPi 2015 Challange at Microsoft Virtual Academy (MVA). I have decided to focus on 100% on Microsoft Azure (cloud paths) during this challange – cloud development, mobile develogment, hybrid could.
The first path “Cloud Development” that consist of four different courses:
- Part 1: Microsoft Azure Fundamentals (Level 200, 50 points)
- Part 2: Microsoft Azure Fundamentals: Websites (Level 100, 78 points)
- Part 3: Designing Applications for Windows Azure Jump Start (Level 200, 51 points)
- Part 4: Developing Microsoft Azure Solutions (Level 300, 64 points)
Currently, I have just compled “Part 2″, and looking forward to the next 2 parts.
Long time and no blog posts from me. It has been a busy winter for me at home.
I have been updated my knowledge before Christmas and in January on Pluralsight with the following courses:
- ALM for Developers with Visual Studio 2012
- Real World Scrum With Team Foundation Server 2013
- Building End-to-End Multi-Client Service Oriented Applications (C# and SOA)
In addition, I’m atttending the current “Know it. Prove It” (KiPi 2015) challange at Microsoft Virtual Academy running in February. I recommend everybody to attend to get a kick-start on the new year. Im attending the following paths:
- Cloud Development
- Mobile Development
- Hybrid Cloud
If this is not enough, I will have a recap on my QlikView knowledge from 5-6 year back, and start looking at Office 365 Implemetations. It will be a busy winter and spring, but I love it…
At work we have started to deploy development, test and production environment to Windows Azure. We have an application that use a web and sql server, in addition to its own AD controller. One of the components in our application use SMTP email server, and after some investigation we decided to use our company’s SMTP server. As a result we need to assign a fixed IP address to the server(s) that shall use the company SMTP services. This IP address has to be granted access to the SMTP server.
The following powershell commands will reserve an public IP adress to the name ‘mytestappip’.
$ip_test_app = "mytestappip" $location = "North Europe" New-AzureReservedIP -ReservedIPName $iptestapp -Location $location Write-Host "test web: " $ip_test_app
Now, we have a reservered, static IP that will remain ours. You can retrieve informaton about an reservered IP with the following Powershell command:
Get-AzureReservedIP -ReservedIPName "mytestappip"
This IP address can now be used when a virtual machine is created. This virtual machine will have this IP address even if the virtual machine is turned off (deallocated). Normally, Windows Azure will release IP addresses for VMs that is turned off completly.
At this time, the first 5 IP addresses are free, but the pricing is found here. You can remove any until we decide to remove it by the command Remove-AzureReservedIP
Remove-AzureReservedIP -ReservedIPName "mytestappip" -Force