Sql Server Management Studio Results to Excel

I need to do some quick analysis on a error log table, so was running a grouping query on the log table.

To get the results into excel I wanted to cut and paste the results from SSMS query window into excel.

Annoyingly the .Net Exceptions where going into Excel on new rows which made it unreadable. 


With SQL 2012 the line feeds and carriage returns are preseved  and needed to be filtered out in the query

SELECT COUNT(*) as count, replace(replace(Message, char(10), ''''), char(13), '''') as ''Message'' FROM Log

Sql Server Management Studio Freezing On Start Up

For a few weeks I had a really annoying issue with Sql Server Management Studio freezing on start up, this happened a few times at crucial moments and caused me delays.

I tried several re-installs and repairs and even the 2016 RC version, but it would just keep happening randomly.

Until I found his blog and following the second option to clear the profile information.


An bingo it never happened again, turns out the profile data is not removed on a uninstall.



Migrating Blog to SQL Azure Database

This is post is about my experiences migrating my blog to a SQL Azure Database.

When I started with BlogEngine.Net I used the put of the box persistence which is XML file based in the App_Data folder.

I wanted to use a SQL Azure Database and migrate the data across, I found the blog post below with instructions.


I took the database script from the setup folder in the BlogEngine.NET file system and ran it against the New SQL Azure DB using SQL Server Managment Studio.

This gave me most of what was required but I had to change the BlogMigration.aspx page to remove the ''1'' from the master page name;

<%@ Page Language="C#" MasterPageFile="~/admin/admin1.master" AutoEventWireup="true" CodeFile="BlogMigration.aspx.cs" Inherits="admin_Pages_BlogMigration" Title="Blog Migration" %>

I still had some compile errors , the following blog post describes the changes required to the BlogMigration.aspx.cs code file, and how to set the blog guid id.


I had to check that the firewall setting were correct for my new SQL Azure DB and that it allowed connections from Azure Web Servers. This post describes how to do that


The users were missing from the database , so I took the encrypted passwords out of the \App_Data\Users.xml and put into the database. This link shows where to find the user information  in the XML and the database.


I also found I had to take the blog guid and change the user and role table to use the imported blog guid, they were all set up with the guid from the set up script.

A few niggle bit was very easy to do in the end thanks to the migration tool and above blog posts.