Execute Large SQL Script from command line

For my own future reference, when I get a large SQL file, large enough that SSMS freaks out about lack of memory, I need to use Sqlcmd tool. And here how it is done:

sqlcmd -S localhost\sqlexpress -d MyDatabaseName -i .\InputFileName.sql

Note that localhost is important to put there, if you try to connect to .\sqlexpress, you will only get a connection error


Tonight I’ve done a talk on my local Aberdeen Developers .Net User Group about CQRS architecture and I think this was a success. After 70 minutes talk (I took my time in some code samples) we had another 30 minutes discussions and Q&A time. Some good quality questions were raised and some things been asked I have not thought about before.

I really enjoyed doing the talk and getting the message across. I think I’ll try doing this more and will look for a possibility to go to other cities to present these ideas. And if you think you and your team might benefit from CQRS architecture, let me know, we can organise something.

For those who have been on the talk, here are the slides and code samples

Swagger Awesomeness

This post will be a dumping ground of links, problems and solutions related to Swagger. I’ll be putting updates here as I go along

I’m starting a new project and we would love trying new things. This time we would like to jump on the whole Azure Api Applications with Swagger as a descriptor of API.

Great article “What is Swagger”

Here is the whole Swagger Spec – have a look what is possible.

Here is the pretty cool page with Swagger file editor

Tutorials for ASP.Net

Continue reading

Prevent Multiple Logins in Asp.Net Identity

I have seen a fair amount of questions on Stackoverflow asking how to prevent users sharing their password. Previously with MembershipProvider framework it was not a simple task. People went into all sorts of crazy procedures. One of the most common was to have a static global list of logged-in users. And if a user already in that list, the system denied their second login. This worked to an extent, until you clean cookies in the browser and try to re-login.

Luckily now Asp.Net Identity framework provides a simple and clean way of preventing users sharing their details or logging-in twice from different computers.

Continue reading

Shutdown Azure VM when it is not required

My current setup for one of the projects requires a VM running in Azure – a tiny build server I set up just for myself. And I only need it when I actually working on the project. And the idea is to shut the VM down when I’m not working on the project. The fear was that one day I’ll forget to shut it down and I’ll be billed for time that I have not used.

So I’ve created a tiny script in PowerShell:

# Get your publishsettings file here: https://manage.windowsazure.com/publishsettings/
Import-AzurePublishSettingsFile "c:\Path\to\subscription\file.publishsettings"

Select-AzureSubscription -SubscriptionName "MySubscriptionName"

Stop-AzureVM -Name myVmName -ServiceName myVmName -Force

NB: you need -Force parameter otherwise the script will prompt for Y/N and will require user intervention.

And then I hooked the script as part of shutdown on my work PC:

  1. Run gpedit.msc
  2. Go to Computer Configuration -> Windows Settings -> Scripts -> Shutdown
    2015-07-04 15_03_14-Local Group Policy Editor

    1. Open Shutdown option and switch to PowerShell tab; Add your script there.
    2. PROFIT!

Now your VM will be shut-down when you shut-down your PC.

Another alternative was to have free web-site running on Azure, deploy there a Web-Job that constantly polls on your PC if it is working or not. And if your PC if offline, shut-down the VM. But this sounds like a lot of work and you’ll have to expose some sort of publicly available endpoint on your PC. So nothing fancy!

Asp.Net Identity Invalid Token for password reset or email confirmation

I’m an avid user on StackOverflow in questions about Asp.Net Identity and I attempt to answer most of the interesting questions. And the same question comes up quite often: users try to confirm their email via a confiramtion link or reset their password via reset link and in both cases get “Invalid Token” error.

There are a few possible solutions to this problem.

Continue reading

How to export data from Excel into SQL Server

TL;DR: Go here for a code sample.

This is long overdue post – I’m too busy these days with contract work and other commitments.

By the nature of my work, I have to do data migrations on a regular basis. Some of the migrations from one database into another, but most of the migrations are from Excel spreadsheets into a database.
A database to database migration is somewhat nerve racking – usually the structure is broken, data is dirty, fields re-purposed and all other sort of terrible things you read about on The Daily Wtf. The best so far I’ve seen are along the lines of person middle names stored in field called CompanyName2; employee information stored in table called tbl_Jobs and info about actual jobs in table tbl_Job – see the difference here? That is all to avoid the confusion!

Database-to-database migration are unique in their own kind. But I found a lot of similar things in a process of export from Excel into SQL Server. So I’ll write down these things here – for my own future reference and you, my dear reader, might find this useful as well.

Continue reading

Not able to download files over HTTPS

Today I came across somewhat annoying bug. On one of the sites I maintain, after enabling HTTPS users could not download files anymore. The annoying part was that I could. With all the browsers. They could not with IE9. Turned out that the issue was only on the internal network only over HTTPS: the same file was downloaded from the same address fine over HTTP, but when you go HTTPS on the same url, this happened:

Unable to download from HTTPS

Turned out that some domain policy was prohibiting to store encrypted files during browsing. IE has this setting Do not save encrypted pages to disk (Inside Tools / Internet Options > Advanced) that caused other people to complain. However in my situation this was not turned on which was strange. I decided to give a recommended trick with headers a go and it worked:

I have modified Cache-Control header to be no-store, no-cache and the problem went away. Strange!

Enabling HTTPS on sites

Sometimes I have to configure sites that are no actually in my control, I only host and administer them. But site development/update is done by somebody else. Or it could be a PHP (yuck!) hosted on IIS.

Today I had to enable a bunch of sites to be served over HTTPS, all these sites sit under one IP, but under different subdomains. Unfortunately IIS does not have ability to configure from UI host header for HTTPS connections. So you have to drop down to command line:

appcmd set site /site.name:"<SiteName>" /+bindings.[protocol='https',bindingInformation='*:443:subdomain.domain.com']

And then in web.config add redirection to HTTPS, but make sure URL Rewrite is installed on server (check if %SystemRoot%\system32\inetsrv\rewrite.dll file is present):

                <clear />
                <rule name="Redirect to https" stopProcessing="true">
                    <match url="(.*)" />
                        <add input="{HTTPS}" pattern="off" ignoreCase="true" />
                    <action type="Redirect" url="https://{HTTP_HOST}{REQUEST_URI}" redirectType="Permanent" appendQueryString="false" />

And add STS header to all the requests:

            <remove name="X-Powered-By"/>
            <add name="Strict-Transport-Security" value="max-age=31536000"/>

Just writing down the steps for myself, so I don’t have to search this stuff over again in 6 months time.