Wednesday, February 24, 2010

Convert varchar in format of mm/dd/yyyy hh:mi:ss:mmm to DateTime using T-SQL

It seems like an easy thing to convert a sting to a datetime in SQL Server. I check the MSDN documentation of converting between strings (varchar / nvarchar) to a DateTime datatype. The problems that I ran into is that when I look at the predefined formats that it understands, none of them seem to be mm/dd/yyyy hh:mi:ss:mmm. The only one that is even close is 101 which is U.S. and makes sense. I was a bit surprised when I tried it that it actually automatically recognized the time portion as well. I was very pleased. So, in fact, I thought it should be easy, it looked like it would be difficult, and in the end it is easy if you know the trick.

Here is all I did.

select convert(datetime, '02/22/2010 16:01:51.802', 101)

That returns 2010-02-22 16:01:51.803 (a datetime datatype)

Keep in mind, I used a literal, but you could use any column that is a varchar or nvarchar and contains a string of that format.

NOTE: I have no idea why the last digit is 803 vs 802. It is not a typo on my part. It is actually accurate. So strange. Bug in SQL Server maybe?

Tip: If you happen to need to convert mm-dd-yyyy hh:mi:ss:mmm, you can just use the replace function to change it to the above format.

select convert(datetime, Replace('02-22-2010 16:01:51.802', '-', '/'), 101)

Monday, February 22, 2010

Monitoring a web server

I was recently wanted to find how much cpu, disk I/O, memory, and bandwidth was being used by my web server.

After a little research, I found that there are some basic performance counters in the Windows Performance Monitor (Start Menu | Administrative Tool | Performance). It is also known as System Monitor.


To minimize overhead you probably don’t want to run the performance monitor using the current activity graph or anything like that where the UI is needed. To reduce overhead, you really want to use the following instructions to monitor your system.

  1. Open the System Monitor by going to Start Menu | Administrative Tool | Performance.
  2. Expand the Performance Logs and Alerts node on the left navigation.
  3. Right-click the Counter Logs and select the New Log Settings… menu item.
  4. Name your Log as desired
  5. On the General Tab, click the Add Counters… button. Add the counters discussed below under each of the headings.
  6. On the General Tab, you may want to change the interval to 3 seconds, but nothing more frequent than that if you can help it. Otherwise, the overhead will be too much in many cases.
  7. On the Log Files tab, change the Log file type drop down list to be Text File (Tab delimited) or other format of choice if you will be working with the data later.
  8. On the Log Files tab, click the Configure… button to change the location for the log file to be something other than c:\ in case you run out of disk space, you don’t want to crash Windows. You also may want to set a file size limit.
  9. ON the Schedule tab, you can set it to whatever makes sense for you. I prefer manual settings to start and stop the logging.
  10. Your Log settings will now show up in the detail panel to the right.
  11. Right-click your log settings in the details panel, and choose Start to start the logging.
  12. When you are ready to stop the logging, right-click your log settings in the details panel, and choose Stop to stop the logging.


When monitoring CPU activity consider monitoring the following counters:

  • Use: Processor: % Processor Time (default is for one total processors)
    • This tells us the percentage of time the CPU spends executing a thread that is not idle.
    • If you have multiple processors, use a separate instance of this counter for it.
    • Consistent 80% – 90% is considered high and should be reduced.
  • Optional Use: Processor: % Privileged Time
    • This tells us the percentage of time the cpu spends executing MS Windows kernel commands. This could be time processing I/O requests.
  • Optional Use: Processor: % User Time
    • This tells us the percentage of time the CPU spends executing user processes such as SQL Server, IIS, apps, etc.

For more information, see this MSDN page.

Disk Usage

When monitoring Disk usage disk activity typically falls into two areas:

  • Monitoring Disk I/O
    • Use: PhysicalDisk: % Disk Time
      • This tells us the percentage of time that the disk is busy with read/write activity.
      • Above 90% is considered high and should be reduced.
  • Detecting Excessive Paging
    • Use: PhysicalDisk: Avg. Disk Queue Length (default)

For more info (including how to troubleshoot bottlenecks, isolating SQL Server disk I/O, etc), see this MSDN page.

Memory (RAM)

  • Rate at which pages are read from or written to disk to resolve hard page faults.
    • Use: Pages/sec (default)
    • Should be under 1000 at all times.
  • There are so many other stats that you can look at 32 in all. They are all under memory.
    • Click here to get a better understanding of memory and what you need to monitor to answer the questions you may be trying to answer.

IIS Bandwidth

NOTE: These number may be a little lighter than that seen from a Firewall since Multicast traffic doesn’t show up and packets filtered by the firewall also don’t show up*.

  • Rate at which the WWW service is sending  and receiving data in bytes per second (both send and receive total)
    • Use: Web Service | Bytes Total/Sec
    • For more info, see here.
  • The total number of bytes of data that have been sent and received by the WWW service since the service started.
    • Use: Web Service | Total Bytes Transferred
    • To get an idea of how much data for the time you are logging you are actually transferring you will need to adjust all your values to baseline at the first value in your log. In other words, you will need to subtract your first amount from your last amount (assuming the service has not been reset).
    • For more info, see here.

Friday, February 12, 2010

Where is Silverlight Toolkit installed?

The short answer is check your start menu for an item that is named similarly to Microsoft Silverlight 3 Toolkit November 2009. The typical answer is C:\Program Files\Microsoft SDKs\Silverlight\v3.0\Toolkit. This has all shortcuts to items of importance. When you download the Silverlight Toolkit you will notice it is an MSI. This means it has an installer. However, this installer does not allow or tell you where it will install its goods.

After reviewing the Microsoft Silverlight 3 Toolkit November 2009 entry in my Start Menu, I found the following installation points.

Binaries – where the .dlls are that you can reference in your project,
C:\Program Files\Microsoft SDKs\Silverlight\v3.0\Toolkit\Nov09\Bin

Documentation – the CHM file version of the documentation
C:\Program Files\Microsoft SDKs\Silverlight\v3.0\Toolkit\Nov09\Documentation.chm

Sample Source Code – the source code to the controls in the toolkit and sample code for using the toolkit
C:\Program Files\Microsoft SDKs\Silverlight\v3.0\Toolkit\Nov09\Source

Silverlight Toolkit on CodePlex – link to the Silverlight toolkit on CodePlex where you likely downloaded the toolkit from, and can get the latest version from.

Silverlight .net Discussions – link to the Silverlight forums where you can post your own questions or just search for answers or even answer some questions :)

Source Code – shortcut to the exact same place that Sample Source Code pointed to… Weird
C:\Program Files\Microsoft SDKs\Silverlight\v3.0\Toolkit\Nov09\Source

Toolkit Samples – link to a local (runnable) copy of the Silverlight Toolkit. Opens in your default web browser.
C:\Program Files\Microsoft SDKs\Silverlight\v3.0\Toolkit\Nov09\Samples\default.htm

Welcome – link to local web page that is a Good starting point, release notes, explanation similar to what I gave here, etc
C:\Program Files\Microsoft SDKs\Silverlight\v3.0\Toolkit\Nov09\Welcome.htm


Other Points of Interest

Themes –Use these themes to change the look of your Silverlight applications without much work at all.
C:\Program Files\Microsoft SDKs\Silverlight\v3.0\Toolkit\Nov09\Themes

Wednesday, February 10, 2010

Making the Silverlight ComboBox bind to multiple columns.

It is actually very simple to make the Silverlight ComboBox show multiple columns, but took me a while to figure out how. Below is what I learned.

The Setup

Let’s assume you have a ComboBox defined in XAML as follows

<ComboBox x:Name="cboTest" Width="100" Height="20" />

Now you want to populate it with some data that you get from the database (through WCF, Web Service, etc). All you have to do is set the ItemsSource property as shown below. In this case, GetMyData() returns a List<Person> type. You can do this in the code behind.

cboTest.ItemsSource = GetMyPersonData();

Now you want to select the appropriate item (the item with PeopleID equal to 123 in our sample) in the ComboBox.

Person foundItem = (cboTest.ItemsSource as ObservableCollection<Person>).FirstOrDefault(p => p.PeopleID == 123);
cboAccountManager.SelectedItem = foundItem;

Showing One Property for each Item in the ComboBox

This is easy, just set the DisplayMemberPath in your XAML or Code behind. In this case, we will display a ComboBox that shows a list of first names.


<ComboBox x:Name="cboTest" Width="100" Height="20" DisplayMemberPath="FirstName" />



Showing Multiple Properties for each Item in the ComboBox

This is actually not bad at all either (once you see how to do it). You will want to remove any code or XAML that sets the DisplayMemberPath. Now all you have to do is change your XAML to look the way you want. Here is an example, that shows the FirstName and LastName properties of our Person object concatenated with a space.

<ComboBox x:Name="cboTest">
<StackPanel Orientation="Horizontal">
<TextBlock Text="{Binding FirstName, Mode=OneWay}" />
<TextBlock Text=" " />
<TextBlock Text="{Binding LastName, Mode=OneWay}" />
You are free to use whatever combination of controls you need to make your multi column layout or maybe just custom format you want.

Items collection must be empty before using ItemsSource.

In general, you are probably getting this message because you are trying to bind to the ItemsSource property and you have hard coded some items in your XAML. You can do one or the other, but not both.

I ran into this when I was trying to define a template for my ComboBoxItems. The thing that I forgot was to put the <ComboBox.ItemTemplate>. If you don’t put that tag first then everything is interpreted as hard coding values, even if you have databinding tags.

This stackoverflow page explains some issues and problems pretty well.

Tuesday, February 9, 2010

Add a gradient background to your DataForm

While the default DataForm in Silverlight 3 looks pretty good. You can add some flare to it by changing the background from solid color to a gradient of blue. You do so by adding the Background tag to your XAML for your DataForm.

<dataFormToolkit:DataForm >
<LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0">
<GradientStop Color="#FFE8F3FC" Offset="0"/>
<GradientStop Color="#FF9BCCEE" Offset="0.108"/>
Here is an example of what it would look like.

I snagged it from this very nice tutorial on DataForms.

Using a ComboBox on a DataForm for Silverlight 3

Disclaimer: I think my next solution is better. Give it a try. Click here to check it out.

I love the DataForm for Silverlight 3. It is truly awesome. My biggest complaint is that customizing the fields has changed significantly over time. For instance it now longer supports Fields, which I thought worked well.

With that said, the DataForm for Silverlight 3 has the ability to automatically generate the UI based on the objects which you pass it. The collection you pass it can even have different types of objects. This functionality is very useful for doing things that are textfields, calendars, numbers, etc.

The other option is to use templates if you can’t leave it up to the DataForm to decide what to display based on what you pass it. The problem is your UI is now tied on a field by field basis to what object you pass it. While that is not usually an issue, it is a bummer to lose that cool functionality.

The problem comes when you want to add a combo box to the DataForm. All of a sudden it seems that support is thrown out the window. I thought ASP.NET Dynamic Data did a pretty good job of handling DropDownLists, but I am disappointed with how the DataForm for Silverlight handles it. It seems that I have to work too hard just to implement a ComboBox.

The good news is that I did manage to figure out how to use a ComboBox with not much code at all; at least once you have created an edit template for your DataForm. Another bit of good news is that the DataForm makes it very easy to create an edit template. The sample below shows what an edit template looks like. One interesting thing is that since I did not specify any other templates, they are automatically generated it appears. Very nice of the DataForm. :)

Here is the XAML that defines a DataForm that has one hidden ID field, 3 textfields, and one drop down to select the Owner from a list of People.

<dataFormToolkit:DataForm x:Name="dfCar" AutoGenerateFields="True" >
<dataFormToolkit:DataField Visibility="Collapsed">
<TextBox Text="{Binding CarID, Mode=TwoWay}" />

<TextBox Text="{Binding Make, Mode=TwoWay}" />

<TextBox Text="{Binding Model, Mode=TwoWay}" />

<TextBox Text="{Binding Color, Mode=TwoWay}" />

<ComboBox x:Name="cboOwner" DisplayMemberPath="Name" DataContext="{Binding OwnerID}"/>

In the constructor of user control that contains this DataForm we need to register for the ContentLoaded event of the DataForm. This will be called after the Template is loaded and thus the ComboBox (cboOwner) has been created. Be sure NOT to register for the Loaded event by mistake. This is too early in the lifecycle of the DataForm and the ComboBox will not have been created by then. You must wait for the ContentLoaded event to fire before looking for the ComboBox.

void dfCar_ContentLoaded(object sender, DataFormContentLoadEventArgs e)
// populate the ComboBox
ComboBox cbo = dfCar.FindNameInContent("cboOwner") as ComboBox;
cbo.ItemsSource = GetOwnersList();

// Select the appropriate item in the ComboBox based on the ID
int? personID = (dfCar.CurrentItem as Car).OwnerID;
if (personID.HasValue)
Person foundPerson = (cbo.ItemsSource as ObservableCollection<Person>).First(p => p.ID == personID.Value);
cbo.SelectedItem = foundPerson;

// some supporting stuff just so you can see what they look like.
// Your objects and methods will likely be more complex, and pull data from a web services,
// RIA Services, WCF, etc.
public class Person
public string Name { get; set; }
public int ID { get; set; }

public List<Person> GetOwnersList()
List<Person> people = new List<Person>();
people.Add(new Person { Name = "Brent", ID = 0 });
people.Add(new Person { Name = "Amanda", ID = 1 });
people.Add(new Person { Name = "Lance", ID = 2 });
return people;}

As you can see, most of this isn’t extra code so much as it is just stuff that would need regardless. The ContentLoaded event handler is really all that we had to add that was extra and that code is pretty straight forward.

I really hope I am missing something. It seems to me that I should not have to do this, and I also don’t think I should have to search the ComboBox by ID. It seems that the ASP.NET model (DataMember property to specify what property in the object should be used as a “value” matching property) would be nice here.

If anyone knows of a better way I am very interested to hear about it.

I got the idea from here.

Tuesday, February 2, 2010

Get Login failed for user after a database restore

If you are using SQL Server logins (not Windows Authentication) to connect to your database and you just restored a fresh copy of your database server (say from prod to dev server) you will likely get an error saying something like the following:

Login failed for user 'SomeUser'.

The reason is that your login and database user have become disconnected.

If are restoring your database to a database server that you have NOT had your database before, you will need to create the Login (not the database user because it should exist and by moved when you did your backup and restore) before you try this solution.

Once you create the login, you will need to link the database user and your new login. If you don’t, you may get an error like this:

Cannot open database "SomeUser" requested by the login. The login failed. Login failed for user 'SomeUser'.

You can link them with a simple T-SQL statement

sp_change_users_login 'Update_One', 'SomeUser', 'SomeUser'

The ‘Update_One’ string is important and is the same no matter what user you are doing. You can do all logins at one by changing the parameters to this stored procedure, but I am always too scared to do it. I like doing it one login at a time, since SQL Server will match automatically. I never really looked enough into what criteria it uses, so I don’t use that.

 MSDN has more details on this method if you need it.

How to change the schema for a table in SQL Server

Here’s the situation, you have a table that is in some schema and you want to move it to another schema in SQL Server 2005 (probably for 2008 also). In my example, I created the table using MS SQL Sever Management Studio to create a table. It puts it in the dbo schema. I want it to be in MySchema schema. I also have some code generation tools that require that it be in dbo schema. So, I have the need to change a table from one schema to another.

Thankfully there is an easy to use T-SQL statement for addressing this issue.

Move table from dbo schema to MySchema


Move table from MySchema to dbo schema