Wednesday, December 16, 2015

SQL Server ALTER COLUMN calculates table width incorrectly

SQL Server 2012

I recently discovered a bug/feature of SQL Server that drove me crazy until a colleague found an article that explained everything. Before I reference the article, read through the following...

Consider this table create statement...
CREATE TABLE WideTable (Col1 int, Col2 char(3000), Col3 char(3000));

Now change the width of Col2 to char(4000) to make the total width = 7004 bytes ...
ALTER TABLE WideTable ALTER COLUMN Col2 char(4000)

You get the error
Msg 1701, Level 16, State 1, Line 2
Creating or altering table 'WideTable' failed because the minimum row size would be 10011, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Where does SQL Server get 10011 bytes from? This is a perfectly legal change isn't it? Let's prove it...
CREATE TABLE WideTable2 (Col1 int, Col2 char(4000), Col3 char(3000));

Apparently SQL Server can't do math. That's a little scary. Here's the story...

Let's start off by running a handy query - you might want to save this one.

SELECT  c.name AS column_name, column_id, max_inrow_length,
      pc.system_type_id, leaf_offset
 FROM sys.system_internals_partition_columns pc
    JOIN sys.partitions p
        ON p.partition_id = pc.partition_id
    JOIN sys.columns c
         ON column_id = partition_column_id
            AND c.object_id = p.object_id
WHERE p.object_id=object_id('WideTable');
GO

This query shows the results below.
column_name column_id   max_inrow_length system_type_id leaf_offset
----------- ----------- ---------------- -------------- -----------
Col1        1           4                56             4
Col2        2           3000             175            8
Col3        3           3000             175            3008

Now you can see that there is a four byte overhead before Col1, Col2 starts at offset 8, and Col3 starts at offset 3008. So if we added another column it would have an offset of 6008. A new column with a width of more than about 2050 bytes would exceed the 8060 maximum allowable table row size. Let's try reducing the width of Col2 to 2500 bytes and see what happens.

ALTER TABLE WideTable ALTER COLUMN Col2 char(2500) 

This works. Let's run that query again...
column_name column_id   max_inrow_length system_type_id leaf_offset
----------- ----------- ---------------- -------------- -----------
Col1        1           4                56             4
Col3        3           3000             175            3008
Col2        2           2500             175            8

Look at the column sequence. It changed. It looks as though SQL Server is adding the altered column to the end of the column list and then removing it from the middle. In between those events it looks like there is code that says something like "If the column width is increasing then check that the sum of the columns does not exceed 8060." But it's including the old AND the new widths of the altered column. This check is not performed if the column width is not increasing.

Looks like a bug, smells like a bug, walks like a bug.

The article that helped me on this issue is http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/alter-table-will-not-reclaim-space.aspx. It talks about a slightly different issue but is very relevant to this problem.

The solution is obviously to make a structure change that forces the table to be rebuilt. There's a discussion of this at the end of the referenced blog entry.

Monday, October 19, 2015

Binding to a user control property

This is for Framework 4.0.

I have a page with a user control on it. The user control contains a list and I want to display the count of items in the list in a text block on the main page. It was obvious I would need to create a dependency property in the user control, so...

Public Shared ReadOnly CountProperty As Windows.DependencyProperty = Windows.DependencyProperty.Register("AttachmentCount", GetType(Integer), GetType(Attachments), Nothing)

Public Property AttachmentCount As Integer
Get
    Return CInt(GetValue(CountProperty))
End Get
Set(value As Integer)
    SetValue(CountProperty, value) 
End Set
End Property

Now it's a simple matter of binding the textblock to the user control's property, right?

<TextBlock Name="AddAttachmentsCount" Text="{Binding ElementName=AddAttachmentsUserControl, Path=AttachmentCount"/>

Oops, now I'm seeing an error in the output window at run-time...


System.Windows.Data Error: 40 : BindingExpression path error: '(AttachmentCount)' property not found on 'object' ''UserControl' (Name='AddAttachmentsUserControl')'. BindingExpression:Path=AttachmentCount; DataItem='UserControl' (Name='AddAttachmentsUserControl'); target element is 'TextBlock' (Name='AddAttachmentsCount'); target property is 'Text' (type 'String')

Well maybe I can bind the AttachmentCount property to the text property of my textblock as I define the instance of my user control...


<local:Attachments x:Key="AddAttachments" AttachmentCount="{Binding ElementName=AddAttachmentsCount, Path=Text, Mode=TwoWay}"/>

Yay!


Tuesday, October 13, 2015

Installing Reporting Services

The second time I've had to install Reporting Services I have decided to create a blog. If I were smarter I would have done it the first time.

We have a curious situation where we have Reporting Services partially installed. Perhaps it was installed for an earlier version of SQL Server but not upgraded so now when I run SQL Server xxxx Reporting Services Configuration Manager it cannot find an instance and the screen looks like this...


Follow these steps.

  1. Insert your SQL Server installation DVD and when SQL Server Installation Center pops up click on "Installation" on the left and then "New SQL Server stand-alone installation or add features to an existing installation" on the right.
  2. Click [Next >] in the wizard until you get to Installation Type.
  3. Select the "Add features to an existing instance" radio button and select the instance. Click [Next >]
  4. Check the "Reporting Services - Native" checkbox (unless you wanted sharepoint). Click [Next >] until you get to the [Install] option
  5. Click [Install]. Have a cup of tea and read the newspaper. Click [Close]
Configure your new Reporting Service

  1. Run Reporting Services Configuration Manager. The Report Server Instance should now be populated
  2. Click [Connect]
  3. Click Service Account on the left. The default service account is ReportServer. This is good.
  4. Click Web Service URL on the left. The default Virtual Directory is ReportServer. This is good. Click [Apply]
  5. Click Database on the left. There is no default database so click [Change Database]
     - You will probably want to create a new report server database. Click [Next]
     - Enter the server name and authentication used to create the new databases. I default to Current User - integrated security. Click [Next] and [Finish]
  6. Click Report Manager URL on the left. The default virtual directory is "Reports". This is good. Click [Apply]
  7. That's all you have to do. You can exit the application now.
Now go to my previous blog on enabling non-administrator access to a new instance of reporting services at http://wpfthoughts.blogspot.com/2015/05/running-report-manager-on-newly.html

Friday, October 9, 2015

Flicker and fail with Clickonce and Citrix

We deployed our first widespread WPF application recently and our Citrix users were experiencing a weird problem with the ClickOnce process. When they attempted to perform the install their mouse cursor started flickering and they got a black screen. The Citrix server was still running Server 2003 which is obsolete so tech support blamed the version of IE (8). I installed the Citrix receiver app on my computer and I was able to install the WPF application without a problem. What gives?

Some sleuthing revealed a hot fix http://support.microsoft.com/en-us/kb/955692 which describes our environment and the symptoms reported. It also mentions that the problem only affects non-administrators on the Citrix server (I am an administrator) which explains why I had no problems.

Maybe someone will find this useful.

Tuesday, October 6, 2015

ICO files in WPF applications

Well the last blog entry was a bit lengthy so here's a nice short one. Is has to do with assigning a ico file to a WPF project. The ico file contains a set of graphics at different resolutions in a single file and is used by WPF for the taskbar, the alt-tab, and window graphics like this...




Setting the ico file is simple. You add it to your project and reference it in the project properties under the application tab like this.

Setting the ico file for a WPF project
The problem is that when you are debugging in visual studio the ico file is not used because you are actually running against a proxy exe called vshost.exe. If this is a problem for you there are two solutions.


  1. Disable the visual studio hosting process in the debug tab of the project properties. This causes you to debug against the actual executable that has the ico file. I don't know why the proxy doesn't have it.
  2. Explicitly specify the ico file in the main page XAML like this.
    <Window x:Class="MainWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Icon="Images\pur.ico" .../>
  Take your pick.

Monday, September 14, 2015

Implementing a validator with a bindable property

Framework 4.0

I recently got a requirement to apply validation to a textbox based on the content of the textbox and another textbox. This was way more difficult to implement than I thought it would be when I foolishly said it wouldn't take long.

I reduced the requirement to its simplest form for this blog. I have two text boxes. If the text of the second textbox is not included in the text of the first textbox the second textbox will fail validation.


Let's start with a simple validator that works with an unbound value for "String Value"...

Start a new WPF Application (VB today), call the project "ContainsDemo" and add a new class called Validators. Here is the code for the first cut at our validator.

Public Class ContainsValidator
    Inherits ValidationRule

     Public Property StringValue As String

     Public Overloads Overrides Function Validate(value As Object, cultureInfo As Globalization.CultureInfo) As ValidationResult
         Try
             If StringValue.Contains(value.ToString) Then
                 Return New ValidationResult(True, "")
             Else
                 Return New ValidationResult(False, "Substring not in string value")
             End If
         Catch e As Exception
             Return New ValidationResult(False, e.Message)
         End Try
    End Function
End Class

Build the project before you continue.

As you can see, the validator has a property called StringValue which can be set in MainWindow.xaml as shown below.

<Window x:Class="MainWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentationxmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:local="clr-namespace:ContainsDemo" Title="MainWindow" Height="350" Width="525"
DataContext="{Binding RelativeSource={RelativeSource Self}}">
<Window.Resources>
    <local:ContainsValidator x:Key="ContainsValidator" ></local:ContainsValidator>
</Window.Resources>
<Grid>
    <Grid.RowDefinitions>
        <RowDefinition Height="auto"></RowDefinition>
        <RowDefinition Height="auto"></RowDefinition>
    </Grid.RowDefinitions>
    <Grid.ColumnDefinitions>
        <ColumnDefinition Width="auto"></ColumnDefinition>
        <ColumnDefinition Width="auto"></ColumnDefinition>
    </Grid.ColumnDefinitions>
    <Label Grid.Row="0" Grid.Column="0" Content="String Value"></Label>
    <TextBox Grid.Row="0" Grid.Column="1" Name="txtStringValue" Text="{Binding Path=StringValue, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}" Width="200"></TextBox>
    <Label Grid.Row="1" Grid.Column="0" Content="Substring"></Label>
    <TextBox Grid.Row="1" Grid.Column="1" Name="txtSubstring" Width="200">
        <TextBox.Text>
            <Binding Path="Substring" UpdateSourceTrigger="PropertyChanged" Mode="TwoWay">
                <Binding.ValidationRules>
                    <local:ContainsValidator StringValue="ABCDEFG"/>
                </Binding.ValidationRules>
            </Binding>
        </TextBox.Text>
    </TextBox>
</Grid>
</Window>

Here is the XAML.VB file

Class MainWindow
    Public Property StringValue As String = "ABCDEFG"
    Public Property Substring As String = "CDE"
End Class

If you run the project you can see that the validation works but we haven't met the requirement of validating against a bound property.


At this point, if we want to bind the ContainsValidator StringValue we might be tempted to try something like...

<local:ContainsValidator StringValue="{Binding Path=StringValue}"/>

but if you do that you will see an error message saying that you can only bind to a DependencyProperty of a DependencyObject. Our validator already inherits from ValidationRule so it can't also inherit from DependencyObject - VB and C# do not allow multiple inheritance.

So we have to alter the validator to implement a public property that is a class that inherits DependencyObject. We can bind to that property but it gets complicated.

Let's start by adding a new class to the validator that implements DependencyObject and registering a DependencyProperty. They we can add a public property that instantiates the new class. The Validator code ends up looking like this...

Imports System.Globalization
Public Class ContainsValidator
    Inherits ValidationRule

    Public Property oStringValue As cStringValue
    Public Sub New()
        _oStringValue = New cStringValue()
    End Sub

    Public Overrides Function Validate(value As Object, cultureInfo As CultureInfo) As ValidationResult
    Try
        If _oStringValue.mStringValue.contains(value.ToString) Then
            Return New ValidationResult(True, "")
        Else
            Return New ValidationResult(False, "Substring not in string value")
        End If
    Catch e As Exception
        Return New ValidationResult(False, e.Message)
    End Try
    End Function
End Class

Public Class cStringValue
    Inherits DependencyObject

Public Shared ReadOnly mStringValueProperty As DependencyProperty = DependencyProperty.Register("mStringValue", GetType(String), GetType(cStringValue))
    Public Property mStringValue As String
    Get
        Return DirectCast(GetValue(mStringValueProperty), String)
    End Get
    Set(value As String)
        SetValue(mStringValueProperty, value)
    End Set
    End Property
End Class

Now we can modify our XAML to bind to the new property. Change the txtSubstring textbox definition to look like this...

<TextBox Grid.Row="1" Grid.Column="1" Name="txtSubstring" Width="200">
    <TextBox.Text>
        <Binding Path="Substring" UpdateSourceTrigger="PropertyChanged" Mode="TwoWay">
            <Binding.ValidationRules>
                <local:ContainsValidator>
                    <local:ContainsValidator.oStringValue>
                        <local:cStringValue mStringValue="{Binding Path=StringValue}"></local:cStringValue>
                    </local:ContainsValidator.oStringValue>
                </local:ContainsValidator>
            </Binding.ValidationRules>
        </Binding>
    </TextBox.Text>
</TextBox>

But if you try this you will see it doesn't really work. If you put a breakpoint in the Validate function of the validator you can see the value of mStringValue never gets set. Also, if you look in the output window you will see the familiar error message "Cannot find governing FrameworkElement or FrameworkContentElement for target element." It's complaining about the StringValue binding. So we fix it with a proxy element.

Add another window resource.
<FrameworkElement x:Key="ProxyElement" DataContext="{Binding}"></FrameworkElement>

Add a ContentControl at the bottom of the Grid.
<ContentControl Visibility="Collapsed" Content="{StaticResource ProxyElement}"></ContentControl>

Now we can bind StringValue via the datacontext of the proxy element by changing the binding to this.
<local:cStringValue mStringValue="{Binding Path=DataContext.StringValue, Source={StaticResource ProxyElement}}"></local:cStringValue>

Run it again and you will see it works pretty well and the error message is gone. However there is still one scenario that is a problem. If you invalidate the substring by appending an "X" and then make it valid by inserting an "X" into the string value after CDE so that it looks like "ABCDEXFG" the substring is still flagged as invalid. The problem is that the substring is not being revalidated when the string value changes. To do that, we need to implement INotifyPropertyChanged in the code behind so that it looks like this.

Imports System.ComponentModel
Class MainWindow
    Implements INotifyPropertyChanged
   
    Private _StringValue As String = "ABCDEFG"
    Private _SubString As String = "CDE"
    Public Property StringValue As String
    Get
        Return _StringValue
    End Get
    Set(value As String)
        If _StringValue <> value Then  
            _StringValue = value
            NotifyPropertyChanged("StringValue")
            If txtSubstring.GetBindingExpression(TextBox.TextProperty).ValidateWithoutUpdate() Then
                txtSubstring.GetBindingExpression(TextBox.TextProperty).UpdateSource()
            End If
        End If
    End Set
    End Property

    Public Property Substring As String
    Get
        Return _SubString
    End Get
    Set(value As String)
        If _SubString <> value Then
            _SubString = value
            NotifyPropertyChanged("Substring")
        End If
    End Set
    End Property

    Public Event PropertyChanged(sender As Object, e As PropertyChangedEventArgs)      Implements INotifyPropertyChanged.PropertyChanged
    Public Sub NotifyPropertyChanged(PropertyName As String)
        RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs(PropertyName))
    End Sub
End Class

Take a look at the setter for the StringValue property. After it calls NotifyPropertyChanged it re-validates the Substring property and updates the source if it is valid. It has to do this because the source is not updated if the target is invalid and if some other change causes the target to become value, the source will never get updated.


Now the substring is revalidated when the string value changes and, if it is valid, the txtSubstring text is saved to the Substring property.