Deleting Default Constraints

Feb 28
2014

Useful to run something like this before you drop columns or update with better named default constraints.


declare @table nvarchar(255)
set @table = 'MyTableName'

DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from sys.default_constraints where parent_object_id = OBJECT_ID(@table))
BEGIN
select @sql = 'ALTER TABLE ' @table + ' DROP CONSTRAINT ' + name
from sys.default_constraints
where parent_object_id = OBJECT_ID(@table)

exec sp_executesql @sql
END

As an aside, I’m in the middle of overhauling everything about my web presence. I may move snippets like this to Github and I’m thinking of ditching wordpress in favour of Jekyll…we’ll see.

Sending SSRS generated MHTML with System.Net.Mail.

Nov 26
2012

I recently had a need to email MHTML files generated by SSRS. It took me a little time to figure out how to use the SSRS web service, but finding posts like this on msdn I eventually came up with a short console app which takes some parameters and writes out the appropriate report generated by my SSRS server. The problem I had was that, although SSRS will generate MHTML reports and these look fine in most browsers that can render them, it is impossible to send that file in the body of a message to Outlook and have it render as html. I thought a good way around this would be to get SSRS to generate an HTML4.0 version of the report and simply send that instead. Unfortunately I ended up with another problem.

Mostly for my own reference, but also in case StackOverflow is ever down, the issue manifests as the report appearing to be horizontally squashed when viewed in Outlook.

e.g. this is what it should look like :

But this is what it looks like in Outlook :

The reason for this becomes clear if you compare the differing HTML generated by SSRS for the HTML4.0 and MHTML format reports. This isn’t exactly trivial to achieve though since in order to view the HTML from the MHTML format report you first have to base64 decode the appropriate section of the document. However, having done that, and running it through WinMerge, the difference is clear :

This shows a comparison between table cell classes in HTML4.0 (left) and MHTML (right). It’s immediately obvious that the HTML4.0 format CSS is missing two important properties : min-width and width. Why this should be I can’t begin to guess, but the result of this is that Outlook squashes the table cells. I suspect Outlook does this because it uses a very different definition of body width than a browser, so without actual width cues, it tries to fit everything into a narrower viewport. Anyway, whatever the reason, the solution seems to be to try reading the base64 encoded html from the MHTML format report and sending that instead. Thankfully, this is fairly easily achievable with a short bit of C# code something like this :

var decoded_text = new StringBuilder();
using (var reader = new StreamReader(mhtFile))
{
    while (!reader.EndOfStream)
    {
        var line = reader.ReadLine();
        if (line != "Content-Transfer-Encoding: base64") continue;
 
        reader.ReadLine(); //chew up the blank line
        while ((line = reader.ReadLine()) != String.Empty)
            if (line != null)
                decoded_text.Append(
                    Encoding.UTF8.GetString(
                        Convert.FromBase64String(line)));
        break;
    }
}

Visit Our Friends!

A few highly recommended friends...

Pages List

General info about this blog...