Friday, 13 November 2009

SQL Server - How do I select just the date portion of a datetime field

There are a few ways to do this, but I find the easiest is to use the float version of a datetime and crop off the decimal portion (right of the decimal place is the time portion)

DECLARE @currentDate DateTime
SET @currentDate = Cast(Floor(Cast(GetDate() as Float )) As DateTime)

Friday, 9 October 2009

"Failed to pause full-text catalog for backup" while backing up a database

So you're trying to back up your database and you get the following error:

Failed to pause full-text catalog for backup. Backup was aborted. Check to make sure Microsoft Full-text Service is running properly and SQL server service have access to full-text service.

There are a couple of things that could be up here. Try all of the following and see if that get's you out of this:

1. Check if the full text service is running. This can be found by opening up the windows services and locating either "SQL Server Full Text Search" or "Microsoft Search Service". If it's not running or disabled then this may be your problem- Retry the backup.

2. Ensure full text catalog is enabled on the databases by running the following command:

exec sp_fulltext_database 'enable'

Retry the backup.

3. If all of the above fails take a note of how the full text catalog is set up on the database in question. It should be accessible via Storage -> Full Text Catalogs. When you see what tables and fields it references try dropping and recreating the full text catalog. Retry the backup.

Hope this helps!

Karma Gardas Snippets Diary - Blogged

"The virtual directory specified already exists"... but it doesn't!

I've come across this issue intermittently on some servers, but I haven't figured out yet why it happens. The times I've seen it happen is when the virtual folder wasn't created with all the permissions required, and then deleting it seems to leave a remnants of it behind. An example of this is when you cannot locate the virtual directory but can see it referenced by an app pool.

So, how do you get rid of it?

9 times out of 10 the following works for me. Let's say the new virtual directory is called "MyDir" and was located in the web site "My Web"

- Open up a command prompt
- Type in the following command:

IIsVDir /delete "My Web"/MyDir

Sometimes if that doesn't work creating the virtual directory (using the same name - MyDir in this example) and running the above command again usually works.

If anyone can shed any light on why this remnants happens please do.

Friday, 2 October 2009

How to programatically move the mouse in C#

.Net gives you access to the mouse via the "Cursor" class. You can interact with the mouse position like this:

Cursor.Position = new Point(Cursor.Position.X + 10, Cursor.Position.Y + 10);

Easy peesy

Tuesday, 29 September 2009

DateTime.Min != SqlDateTime.Min

Yes, as the title says, .Net DateTime Minimum does not equal Sql Servers equivalent. For whatever reason you come across this gem of an issue you have a couple of options to get around it.

1. Use a .net Nullable datetime instead. i.e. Declare your "DateTime" as "DateTime?". Putting question marks after a lot of .Net variable types in C# turns them into nullable types.

2. If you really do need the min date, use SqlDateTime.Min.Value. "Value" gives you access to a DateTime type containing SQLs min date.

Wednesday, 26 August 2009

SQL Server - Select rows of a "max date" from a table (using ROW_NUMBER() OVER)

I often come across different requirements where I need to get the "top row" where certain criteria exists. It often applies to VAT Rates, Exchange Rates, even historical data where a different record may apply depending on the scenario. For this example, let's use VAT rates.

Let's create sample data using the following:

create table #myvattable (vatname nvarchar(50), effectivedate datetime)
insert #myvattable values('Rate A', '01/01/2009')
insert #myvattable values('Rate B', '01/01/2009')
insert #myvattable values('Rate C', '01/01/2009')
insert #myvattable values('Rate A', '01/02/2009')
insert #myvattable values('Rate B', '01/02/2009')
insert #myvattable values('Rate B', '01/02/2008')

So, we have several instances of the same "Rate" in the table that have different effective dates. Now, let's take this requirement:

I want to retrieve all VAT rates applicable for a certain date. How do we do this?

Step 1 - use a row_number() clause on your dataset:

select * from
select row_number() over (partition by a.vatname order by a.vatname, a.effectivedate desc) as rownum,
a.vatname, a.effectivedate
from #myvattable a

This simply puts a row_number clause on each row partitioning by the vatname. This returns a result set similar to this:

rownum vatname effectivedate
------ ------------- -----------------------
1 Rate A 2009-01-02 00:00:00.000
2 Rate A 2009-01-01 00:00:00.000
1 Rate B 2009-01-02 00:00:00.000
2 Rate B 2009-01-01 00:00:00.000
3 Rate B 2008-01-02 00:00:00.000
1 Rate C 2009-01-01 00:00:00.000

Ok, so what does this do for us? It effectively ranks the rows by each vatname so that we can see which ones are most applicable. Basically we want all the 1's.

Step 2 - Filter the rownums.
So our query becomes this:

select * from
select row_number() over (partition by a.vatname order by a.vatname, a.effectivedate desc) as rownum,
a.vatname, a.effectivedate
from #myvattable a
where rownum = 1

Ok, pretty much there now. The only step really that is left is... what if someone wants historical data? This query is fixed to the latest most applicable vatrate.

Step 3 - Make the date flexible

select * from
select row_number() over (partition by a.vatname order by a.vatname, a.effectivedate desc) as rownum,
a.vatname, a.effectivedate
from #myvattable a
where effectivedate <= @DATEVARIABLE )
where rownum = 1

So, @DATEVARIABLE could be a parameter/variable... or, even better, a field from another joined table.

Tuesday, 25 August 2009

How to calculate the width/height of a string in a particular font

- Follow up from the listbox width issue -

This applies to mainly, but the technique for winforms isn't drastically different. What we need to use is a Graphics object to measure a string width. Here's how:

// Bitmap is solely required for creating the graphics object
Bitmap TextBitmap = new Bitmap(1, 1);
Graphics g = Graphics.FromImage(TextBitmap);

// Create the font relative to the text (let's say Arial 8)
Font myFont = new Font("Arial", 8);
SizeF fontSize = g.MeasureString("This is a test string", myFont);

Done. The values you need are fontSize.Height and fontSize.Width.

Friday, 21 August 2009

How to close a window using ASP.Net server side

After doing what needs to be done you can register some startup script to close your browser window like this:

string closeScript = "<script language="javascript">;</script>";
if (!ClientScript.IsStartupScriptRegistered("clientScript"))
  ClientScript.RegisterStartupScript(Page.GetType(), "clientScript", closeScript);

Thursday, 20 August 2009

Getting the dialog arguments passed into a web page modal dialog

This is especially useful if you use Microsoft CRM. Because it ties in very closely to how the entity forms and grid views communicate to custom web pages etc.

For example, in Microsoft CRM you can edit the ISVConfig to add a button to the toolbar for the entity grid. Set the WinMode=2 (2 basically means a modal dialog) to allow CRM to provide you with the selected entities. So how do we know what items were selected in the entity list? It passes us in a list of IDs as part of the window.dialogArguments.

So, how do we get access to this? Through javascript. Here's the steps:

1. Create the following javascript function (available from MSCRM help pages):

function loadSelectedListItems() {
  var sGUIDValues = "";
  var selectedValues;
  //Make sure window.dialogArguments is available.
  if (window.dialogArguments) {
    selectedValues = new Array(window.dialogArguments.length - 1);
  else {
  selectedValues = window.dialogArguments;
  if (selectedValues != null) {
    for (i = 0; i < selectedValues.length; i++) {
      sGUIDValues += selectedValues[i] + ",";
    document.getElementById('<%= winDialogArguments.ClientID %>').value = sGUIDValues;

2. Use this function as part of the onload of the body:
<body onload="loadSelectedListItems()">

3. Create a new holder object (in my example an asp textbox) in a hidden division on your form:
<div style="visibility: hidden">
   <asp:textbox id="winDialogArguments" runat="server" text="">

Job done. Just access the Text of the asp textbox server side on the next post back (e.g. click of an OK button) to get the list of IDs.

Web Page Modal Dialog opens new window on postback

If you have a web page that's opening as a Modal Dialog (using window.showModalDialog or similar) and you are finding your button post backs are opening up a new browser window... here's the code that generally stops it happening. At the top of your head tag insert the following:

<base target="_self" />

In some instances this won't work. If not then pop this onload script in the body:

<body onLoad="'myDialogWindow' ......>

and set the base tag to this instead:

<base target="myDialogWindow" />

How to close a window with an asp button (ASP.Net) using Javascript

A really handy way to do this:

<a href="#" onClick="javascript:window.close();">
   <asp:Button ID="btnCancel" Text="Cancel" runat="server" />