Bonski's Box

Trimming that "untrimmable space"

bonskijr — Wed, 20 May 2009 17:36:00 GMT

UPDATE: It seems Community Server stripped the actual CHAR(160) in @someStringWithPad, just so that it will be contrived append CHAR(160) at the @someStringWithPad variable. ;)

Ever experienced trying the following code to trim a seemingly "space" padded string?

DECLARE @someStringWithPad VARCHAR(25)   

SET @someStringWithPad = 'space the final frontier '
SET @someStringWithPad = RTRIM(@someStringWithPad)   

IF  LEN(@someStringWithPad) > 24     
     PRINT 'ASSERT: expected 24 characters but was ' + CAST(LEN(@someStringWithPad) AS VARCHAR(3))

Guess what will be printed? It will be the ASSERT message. Now what gives here? We've already RTRIMed the string, trying LTRIM(RTRIM()) won't help either. Doing a REPLACE(@someStringWithPad, SPACE(1),'') , doesn't help either.

Try doing the following:

SELECT ASCII(RIGHT(@someStringWithPad,1))

What do you get? It won't be CHAR(32) for space, it will be CHAR(160) for a Non-breaking Space.

So the fix is to issue a REPLACE(@someStringWithPad, CHAR(160), '') a little tricky if you ask me, and hopefully will save you some headscratching as to why your INNER JOIN or WHERE clauses doesn't match those that is padded with CHAR(160).

In SSMS 2005, set the "Include IF NOT EXISTS clause" to false ALWAYS

bonskijr — Wed, 13 May 2009 14:49:00 GMT

This is a frustrating UI experience, setting the "Include IF NOT EXISTS clause" to TRUE in the scripting options of SSMS 2005 results in the following when using a Modify or any scripting options:

While it does wrap an IF NOT EXISTS statement(a safety check and a must) it deserializes the stored procedure to be executed inside an sp_executesql command. This sucks, since you have to manually remove all of the text before the ALTER and text after the last delimiter everytime you're going to modify something, worse if the sql contains literal strings you have to manually search/replace all the single quote delimeter. I had to uninstall SSMSToolpack (great add-in) which I thought was the culprit, but the thing still remains.

Good thing I was helped by Tibor Karzi and pointed me to disable that setting. This UX change was already logged as an issue way back in 2007, it was reported in SP2 mine is already in SP3. I'm not sure if this still happens in SSMS 2008 though.

Hope this helps

Fresh Start

bonskijr — Wed, 21 Nov 2007 03:06:00 GMT

Time for a fresh start and hopefully with my structured programming mindset, which almost didn't land me the job, will categorically be flushed out from my system and really start thinking OOP. I thought I was doing OOP at that time, but my boss upon interview, told me that what I was doing was structured programming.

Anyhow I'm excited to work here, now to survive the 3 mos. probation and stay here for good.

I wonder if I'll be able to speak T-SQL again :P

Fix for :"undefined method `exitstatus' for nil:NilClass" when building IronRuby

bonskijr — Thu, 13 Sep 2007 12:07:00 GMT

Just downloaded/checked out the latest IronRuby(now on rubyforge) source and followed the instruction on the intro webcast by John Lam. It was a simple issuance of rake test command to see if it will build correctly, much to my dismay it blurted out : ""undefined method `exitstatus' for nil:NilClass" error. Huh?! I tried it several times even with the --trace switch so that I could see which portion it's giving an error. I thought also that resgen.exe wasn't in the path so I tried to build it using VS2005 command line, no luck, one clue was that it did point out to the rake source file..

Just to make sure that the source is working correctly, I opened it in VS and compiled it, no problem there. So, I suspect that it was the rake command that's giving the problem. I issued a gem list and noted that I haven't updated it, it was 0.6, it was some time ago since I last issued a gem install rails --include-dependencies --http-proxy[myproxy]. However instead of updating rails, I simple issued a gem install rake, to update to the latest, v0.7.3. I then issued a rake test to confirm, and voila it was able to build correctly.

Hope this helps.

Computing Time Difference

bonskijr — Sat, 23 Jun 2007 14:29:00 GMT

While Sql Server provides functions to manipulate datetime data types(DateDiff, DateAdd etc.) they don't have(as far as I know) functions to manipulate time. Very useful when computing time differences. In the past I had to manually datediff parts of the time(hh:mm:ss) which wasn't scalable especially if you're going to deal with more than a day of difference. However there's an easy way to deal with time differences, regardless of the amount of difference(day, month, year, hours etc.)

Sql Server stores it's datetime datatype as 2 4byte integer, the first portion holds the date and the fraction portion holds the time(ie.. 12345.67890), so to get the time difference:

DECLARE @timeIn AS DATETIME
DECLARE @timeOut AS DATETIME

SELECT @timeIn ='2007/07/25 20:20:30', @timeOut = '2007/07/25 22:30:30'

SELECT CAST(CAST(@timeOut AS float) - CAST(@timeIn AS float) AS DATETIME)

results to:

1900-01-01 02:10:00.000

2 hrs and 10 mins.. which you can easily extract using CONVERT(varchar(10),@timeDiff,114)

hth

Sql Server Data Type Precedence : Redux

bonskijr — Tue, 17 Apr 2007 12:09:00 GMT

Previously jokiz bloged about this one, I'd like to add further.

The same issue cropped up again in the forums although not the exactly the same as jokiz's experience, this one could really give you hours of head scratching. Previously I was amazed that dynamic ORDER was possible with T-SQL without resorting to dynamic T-Sql using CASE statements:

SELECT p.ProductId -- int
       ,p.ProductName -- varchar
       ,p.EncodedDate -- datetime
       ,p.IsActive    -- bit
FROM dbo.Product p
ORDER BY CASE @sortBy
         WHEN 'Id' THEN p.ProductId
         WHEN 'Date' THEN p.EncodedDate
         ELSE p.ProductName END

The following will work as long as the @sortBy variable is either "Id" or "Date", once it is neither the engine will raise an "Error converting varchar to datetime." The reason is that the CASE statement has to return an undetermined datatype, it does an implicit conversion according to data type precedence; since DateTime is of higher precedence than varchar, the engine will try to cast the varchar to DateTime hence the error.

A hack would be to cast all the columns to be returned as varchar so it won't give that error. However, you'd have to pad integers in order for it be sorted correctly (ie: converting integers(1,2,10) to varchar will give you (1,10,2), so it should be padded first 0001,0002,0010). This can get really ugly and slow. With this kind of scenario and many criterias, the best way would be to resort to dynamic T-Sql.

On a side note, Sql Server also will implicitly cast statement(s) with operators:

SELECT columns
FROM dbo.table001 t
WHERE t.FileNo = 12345 -- Where FileNo is of Varchar with 0 pads(ie. 0012345)

Assuming there is no FileNo like 'X012345', the query will be parsed and executed without errors. Of course it woud be prudent to explicitly CAST it, just in case we'll have heterogenous data in the future.

Hope this helps!

Using ConnectionString section in NHibernate

bonskijr — Sun, 08 Apr 2007 15:12:00 GMT

Finally NHibernate (1.2+) is able to take advantage of .NET2.0's connectionStrings section, which previously I'd have to either declare it twice (in either config file or the dedicated hibernate.cfg.xml) All you need to do is, instead of using hibernate.connection.connection_string, use the "newer" hibernate.connection.connection_string_name property.

<connectionStrings>
  <add name="myConnectionSTring" connectionString="Data Source=myServer;Initial Catalog=myDB;Integrated Security=True"
   providerName="System.Data.SqlClient" />
 </connectionStrings>

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
        <session-factory>
            
            <property name="dialect">NHibernate.Dialect.MsSql2000Dialect</property>
            <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
            <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
            <property name="connection.connection_string_name">myConnectionString</property>
            
        </session-factory>
    </hibernate-configuration>

UPDATE(Thanks to Ayende):Castle's ActiveRecord(RC1/trunk) which uses NHibernate, gives you two option of taking advantage of the connectionStrings sections either by(older one)

<add key="hibernate.connection.connection_string" value="ConnectionString = ${myConnectionString}" />

or the "newer one" which takes advantage of the NH1.2+ support for the connectionStrings sections:

<add key="hibernate.connection.connection_string_name" value="myConnectionString" />

It now makes it more manageable, no need to have separate connectionString sections, more importantly though, is that you can take advantage of connectionString/protectedSections of .NET 2.0.

Expression Studio is now part of MSDN Subscriptions

bonskijr — Wed, 04 Apr 2007 05:28:00 GMT

Via Scott Guthrie

Microsoft finally had a changed of heart and included the Expression Studio as part of MSDN subsription. I don't have to worry about that trial expiring anymore, coz I think Expression Web/Blend is a blast for creating WPF/E applications. Thank you Microsoft!

Official statement from Somasegar

Sql Server: How To Restore A Non-Production DB Faster

bonskijr — Thu, 01 Mar 2007 11:11:00 GMT

Today I was preparing the DB for an implementation of one our module app, I brought along a soon to be production DB . The DB itself was not that big (5GB), however restoring it was taking alot of time. Normally I wouldn't worry about it, but I was on time constraints and the Establishment is working morning only, and I still haven't setup the application.

The restore itself finished more than half an hour, and since we always have two environment a production and a demo environment, I dread waiting for it again as I was about to restore the demo environment. Then I thought of something, made a copy of the DB and log file and renamed it appropriately. Then I simply attached the database with a different name. Voila! instant "restore." Saved me alot of time waiting(20+ mins), the only time I waited when the DB/Log files were recopied which took about 10+mins. Got the application and reports setup long before the bells whistled.

And don't forget to take your production DB offline first or else you won't be able to copy as Sql Server locks the file.

Getting rid of .svn folders

bonskijr — Mon, 26 Feb 2007 07:50:00 GMT

I often copy several OSS projects that I have checked out from their public Subversion repository, back at home so that I have the "bleeding edge" source when working. I find it a hassle to have to include all the .svn files. John Galloway presented a hack which even includes the command in the Context menu, however MattyT suggested a better way and that is to use svn export or even better TortoiseSvn->Export context menu, which copies all the files except the .svn folders. I didn't know this, I always thought when I use the export command(either with TortoiseSvn/Svn) that it has to connect to repository then exports everything from there.
Another case of having the answer in front of you, if you just look a little more closer

The Case of Swallowed Key Combinations

bonskijr — Thu, 22 Feb 2007 14:13:00 GMT

My officemate asked my why his key combination was not working, when I looked at the application I told him that it might be because the active control is currently in read-only mode and that it might be rejecting those key combo. However, he refuted that if that were the case, his other key combinations should also not work; and also pointed out that I might be right with my initial assessment of read only fields rejecting key strokes when he made his key combinations on a textbox which isn't read only. He has, by the way, enabled the KeyPreview property of the form to capture those keystrokes first before the any controls. It was kind of odd, that some key combinations were available regardless of the readonly property status of an active control.

I did a quick test to observe the KeyEventArgs during KeyDown event with the following code:

       public Form1()
        {
            InitializeComponent();
 
            this.KeyDown+=new KeyEventHandler(this.OnFormKeyDown);
 
        }

        private void OnFormKeyDown(object sender, KeyEventArgs e)
        {
 
            this.txtKeyCode.Text = e.KeyCode.ToString();
            this.txtKeyData.Text = e.KeyData.ToString();
            this.txtKeyModifiers.Text = e.Modifiers.ToString();
            this.txtKeyValue.Text = e.KeyValue.ToString();
            this.txtHandled.Text = e.Handled.ToString();
         }


After running the program I've observed the following:
1.) For the key combinations : CTRL+L(my officemates key combo), CTRL+E, CTRL+J, and CTRL+L, were not displaying the correct KeyData
2.) Oddly it(KeyData) was the same as pressing only the CTRL key

It's as if when those key combinations are made the KeyEventArgs are not filled correctly. Hence I couldn't do the following check:

 if (e.Control && (e.KeyCode == Keys.L)){ //do something }

UPDATED: Comgen, correctly noted that it wasn't a KeyValue returning a constant 17, but actually returns nothing ie:the KeyEventArgs.KeyValue isn't getting a new value but the same value from before. The reason I concluded before that it was a constant 17 because I did a CTRL, then +L, since the previous KeyValue property was 17(for CTRL), the next time I pressed L the KeyValue property remained the same. However, if you pressed any CTRL+combination(except those questionable 4), and without depressing CTRL press either of those 4 letters (ie. CTRL + L) then the previous KeyValue will still remain. I am really stressing this one a bit. :P

Launchy is da Bomb!

bonskijr — Tue, 30 Jan 2007 07:52:00 GMT

Just finished reformatting my harddrive, and installed Windows 2003 Server+SP1 as my OS. Primarily to take advantage of Win2K3's superior memory management and security, not to mention stability compared to WinXp+SP2. I have been salivating with the newer tech(NET 3.0, Virtual Server 2005 R2, etc.) and although I have been toying with these in my virtual machine, I felt constrained by the VM itself, so I've decided to use OS w/c supports it natively hence the reasons above.

I have made an application checklist that were present in my previous setup, and decided to install one a time. I have heard of Launchy before, but I've been using SlickRun for some time now, you've seen one program launcher you've seen em all, I said. Out of curiosity though, I decided to install Launchy which already reached 1.0 milestone, and boy was I glad I did. Instead of configuring applications to have a magic word like SlickRun, it uses all the items in your Start menu as a lookup. It's like a searchable Startmenu ala Vista, but more prettier. It's default look-up directory is the Start Menu directory, and indexes all shortcut files(.lnk) for faster searching, you can configure also to add several lookup directories and include exe files or whatever files for lookup.

The only minor(minute) gripe is that I couldn't create a mnemonic for the application ala magic word in SlickRun. So instead of "vs.net" I'd have to type "Visual Studio 2005" to get to the application, maybe it can be done in the future. The default launch key of Launchy is ALT+SPACE, which is basically the same as the system menu, so I changed it to WIN + Q in memory of SlickRun :)

In Memory of Douglas Reilly

bonskijr — Sun, 24 Dec 2006 05:12:00 GMT

One of the first book that I bought about .NET is an entry level book entitled:"Designing Microsoft ASP.NET Applications" by Douglas Reilly. Made when .NET was still in its infancy(v1.0), it still nevertheless gave me the foundation for web application using .NET as the platform. Looking at his credentials, he looked like a well rounded guy(used Delphi and .NET platform for his company, MVP ), and his writing style is far from being too serious.

However Douglas Reilly was already suffering from a condition called:mucinous adenocarcinoma, which led to cancer. His wife kept a blog about Douglas' condition. He passed away 23rd Dec 2006, peacefully.

Thank you Douglas Reilly, for being such a wonderful author, you have given me the foundation to build upon as ASP.NET progresses.

Programming Microsoft Web Forms, was his last book.

CodeMash: Have .NET in your Java

bonskijr — Tue, 19 Dec 2006 09:12:00 GMT

Disregarding the cool although a rather brutal name, CodeMash aims to demystify the different development platforms' practices and methodologies and maybe put an end(or at least minimize) the ".NET is better than Java or Ruby is better than .NET" crap by very passionate platform developers and instead learn from each other's platform.

From the site itself:

What is CodeMash?

CodeMash is a unique event that will educate developers on current practices, methodologies and technology trends in variety of platforms and development languages such as Java, .NET, Ruby and PHP. Held January 18-19, 2007 at the lush Kalahari resort in Sandusky, Ohio, attendees will be able to attend a world-class technical conference amid Ohio's largest indoor waterpark. So nobody will frown if you show up in shorts, sandals, and your loudest t-shirt. You might even win a prize for doing so.

CodeMash

Sql Server 2005 Tip:Implicit Conversion of bit data types

bonskijr — Sun, 10 Dec 2006 05:47:00 GMT

Didn't you know that the following statement is now valid for Sql Server 2005 as criteria for a column with bit data type?

SELECT * FROM Orders WHERE Processed = 'true'

The engine is now intelligent enough to implicitly convert it to

SELECT * FROM Orders WHERE Processed = 1

More readable, imo. Too bad it's Sql Server 2005 only.