CleanBin – Cleaning up your bin and obj solution folders in Visual Studio

I’m pretty sure every programmer is lazy. What do you do when sending sample code to colleagues or customers by email? Just zipping the Visual Studio solution and send it? Have you ever been stopped by size limitations for attachments on Exchange set by your IT-department? Been there, done that…In addition, until recently I had bad upload capacity on my home broadband, and my synchronization between local disk and Skydrive/Dropbox was awful when there were many new and large files.

This is not a big issue when you have utilities like “Dropbox – public links”, but until recently, I was fed up and created my own utility to clean up unnecessary files from my sample projects. BUT…As mentioned, programmers are lazy and we probably don’t clean the solutions when we close the solution. Therefore I had to create an utility that went through all my source code locally and removed the files under BIN and OBJ folders.

I called this utility CLEANBIN and the main objective was to loop through all my sample code stored on my local drive and remove all files from the BIN and OBJ folder in my Visual Studio solutions.

Downloads:

Happy coding…

Check SQL Server configuration with powershell

When you are working with many SQL server at once, you will minimize the validation of configuration and settings. Normally, you open “Management Studio” and run some commands in a query window or navigate through the GUI. But in newer version (even powershell 2.0) you can access most of the SQL Server configuration properties and settings by powershell commands.

The following script will connect to the currently loggin server and write typical configured properties on a server you are looking for:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, `
	Version=10.0.0.0, `
	Culture=neutral, `
	PublicKeyToken=89845dcd8080cc91"

$server = New-Object `
	Microsoft.SqlServer.Management.Smo.Server($env:ComputerName)

Write-Host "---------------------------------------------"
Write-Host "##### check_sql_config, by sveroa       #####"
Write-Host "---------------------------------------------"

Write-Host "Server         : " $env:ComputerName
Write-Host "Network name   : " $server.Properties["NetName"].Value
Write-Host "Product        : " $server.Properties["Product"].Value
Write-Host "VersionString  : " $server.Properties["VersionString"].Value
Write-Host "Edition        : " $server.Properties["Edition"].Value;
Write-Host "ProductLevel   : " $server.Properties["ProductLevel"].Value
Write-Host "Processors     : " $server.Properties["Processors"].Value;
Write-Host "PhysicalMemory : " $server.Properties["PhysicalMemory"].Value;
Write-Host "Max Memory     : " $server.Configuration.MaxServerMemory.ConfigValue;
Write-Host "Data folder    : " $server.Properties["DefaultFile"].Value;
Write-Host "Log folder     : " $server.Properties["DefaultLog"].Value;
Write-Host "Backup folder  : " $server.Properties["BackupDirectory"].Value;
Write-Host "Collation      : " $server.Properties["Collation"].Value;

You can also run this script on a remote SQL server by some changes described in this blog.

Happy coding…

Currency Collector

Edit (04-FEB-2014): new version of source code

I couldn’t keep away from programming during my long Chrismast Holiday. Decided to do some “just-code-something-useful” instead of doing something new. I remebered back to my previous employer and a quite simple and fun task of implementing a currency collector, and decided to rebuild this from scratch.

The national bank (Norges Bank) updates the curency rates every day at 15:15 (local time). This file is stored as a CSV file

http://www.norges-bank.no/WebDAV/stat/no/valutakurser/v2/valuta_dag.sdv

This file is on the following format seen in the screenshot below. The first column contains the date on the format DD-MMM-YY. The following columns contains data for all the available currencies – one column for each currency. The column header contains both unit and currency code according to Norwegian Kroner (NOK). Each row contains data for all currencies for the specified date in column 1. The file contains all historical data back to 2001.

csv_file

The main page for the currency rates are located here;

These data are the official data for currency rates according to NOK, but it might be more correct to collect data from different banks as well to add fees you have to pay when buying and selling currency.

The concept has a simple data model.  One table for storing information on the available currencies, one table for the collected rates. I also have a table for sources for currency rates. For the time beeing, I have only implemented one – NB, Norges Bank.

dbmodel

I have developed the solution i Visual Studio 2012 as a console application. The database is created/published with the project type “SQL Server Database Project”. This project type has evolved since I used this a few years back and become more mature, in my opinion.

For reading CSV files I’m always using the super-fast CSV reader by Sebastien Lorion. This lovely library is now also available via Nuget. I’m using Log4Net as logging framework. And the database access is done by “LINQ to Classes” – simple and easy.

The program uses an input- and output directory. It will store all files retrieved from Norges Bank with a date in the output directory, and the main program will check if the file for current day is already stored locally and take no action if this file exists.

If the currency file for today doesn’t exist it will http download the latest currency rates from the URL described above. The program will read currency reates that is enabled in the database (Active = true), and read all new currency rates for all currencies from the downloaded file and write them to the database.

This code is probably not a school example, but it works. I will refactor the code later **IF** I decide to implement a new currency rate source, such as “DNB”.

Full source code is located at my dropbox.

Change history:

  • 04-FEB-2014), bugfix to fix hardcoding folder error

Happy coding…

Unattended install for SQL Server 2014 CTP2

The process of creating an unattended installation procedure for the newest “beta” release of SQL Server is the same as described in my previous post for SQL Server 2008 R2. But one of the major difference is that you should install SQL Server 2014 on Windows Server 2012, and we can use the newest version of Powershell and take advantage of new, powershell functionality like disk management and other advanced OS configurations.

SQL2014-CTP2_install

I created the configuration file as shown above, and added the following statements:

IACCEPTSQLSERVERLICENSETERMS="True"
INDICATEPROGRESS="True"

I also had to remove the UIMODE and QUIET statements, since they can’t be used with the statement to optimize the unattended install options:

QUIETSIMPLE="True"

This is my first install of SQL Server 2014 CTP2, and I will optimize the powershell installation script with a set of the most important pre- and post installation tasks.

Happy installing…

 

Unattended install for SQL Server 2008 R2

My current project involves lot of migration of servers from one data room to a new datacenter or local data room at new location for the customer. This involves installation of many SQL Servers. There exist many SQL Server 2000 database servers and we decided to install SQL Server 2008 R2 and run the SQL Server 2000 databases with the Compability Level option. Microsoft only support the latest 3 version with this option. This means that SQL Server 2008 R2 is the latest version we can use for support of SQL Server 2000. Windows Server 2008 R2 is used and Powershell 2.0.

You start by generating your installation options by running a standard install with all the options you want for your SQL Server. When you reach the “Ready To Install” a file called ConfigurationFile.ini with a path is generated with all your options set. Normally, you click install at this point, but just copy the filepath and press “Cancel”. This is shown in the screenshot below.

SQL2008_install20

The next step is to modify the ConfigurationFile.ini to be able to run the installation without any interaction. Put the following statements below the “ACTION=Install” statement:

IACCEPTSQLSERVERLICENSETERMS="True"
INDICATEPROGRESS="True"
QUIETSIMPLE="True"

You have to remove the UIMODE statement since this is not combinable with the QUIETSIMPLE statement. This should be enough to perform a unattended installation from the command-line:

D:\setup.exe /configurationfile="C:\Install\ConfigurationFile.ini"

In my case, the DVD has the drive letter D: and my installation scripts are located in C:\Install folder.

The next step is to create a powershell script with pre- and post-installation tasks (OS and DB) that you need to perform during the before and after installation. Jonathan Kehayias at sqlskills.com has a very good checklist post that can be used to complete your powershell script. In my current script I’m checking the ‘Allocation Unit Size‘ for the data, log and tempdb disks, in addition to configuring the ‘Maximum Server Memory‘ and initial user rights.