EntropiaOnline development

If Business Objects...

2009-11-17T09:29:00.002+01:00

...was an actor, it would be Sylverster Stallone. It somehow looks slightly impressive with its sleeves rolled up. And then it opens its mouth...

MediaWiki - Add a tab to everyone's Personal Tools

2009-11-11T17:34:00.002+01:00

For a while now, I had adding an extra option to the Personal Tools on my list of things to do. My wiki's main entry point for my users is through my forum (which has a user-bridge to the wiki, meaning it is not neccessary for my users to create seperate logins for the wiki if they already have an account on the forum), but I wanted to add a link back to the main site somewhere.

The logo would have been my choice, but we use that for users to be able to return back to the main wiki-site. So I figured it would be nice to add this to the "Personal Tools" menu, which is the menu in the upper right corner that shows your username, "My talk", "My preferences", etc.

Here's how to do it. In your includes folder, there's a file called SkinTemplate.php. Open that in an editor, and look for the following:

[code]
$personal_urls['userpage'] = array(
'text' => $this->username,
'href' => &$this->userpageUrlDetails['href'],
'class' => $this->userpageUrlDetails['exists']?false:'new',
'active' => ( $this->userpageUrlDetails['href'] == $pageurl )
);[/code]

Directly above that, add the following:

[code]
$personal_urls['myId'] = array(
'text' => 'myText',
'href' => 'myHref'
);[/code]

myId is a unique identifier name for the personal url.
myText is the name you want the link to have
myHref is where the new link should point to.

That's it. You're done.

Note that the method above means the link will be the first link to appear. If you prefer setitng it somewhere inbetween instead, move the snippet of code to the proper location.

MediaWiki - Help file import, no SVG support, and additional edits

2009-10-20T21:05:00.016+02:00

Another MediaWiki post. This time, I ran into some trouble after exporting all help pages from the official wiki, and trying to import them to my own, as described here.

I'd also recommend you to import Manual:LocalSettings.php, as that is referenced in one of the help pages in an admin-only box.

At the bottom of the list of files referenced, there's a whole bunch of images, which you have to save manually. Some include files in .svg format (vector based), but before you can actually use those, you need to install additional software.

My problem is that I have no idea how to install stuff my VPN, as I am not sure my host enabled a shell account for me. So rather than potentially messing up my box, or relying on my host for something I'm not even sure will add that much value, I figured I'd learn something about wiki, and just change the references from .svg to the png format in which I saved all the files.

As you see, the official wiki page is little help. "You might have to edit some pages to fix the link".

Anyway, here's the pages/items that require editing for the images:

Template:Admin_tip
Template:PD_Help_Page (I also removed some conditional IF statement here)

Further edits I did:
All pages: Removed {{Languages}} tags.

Help:Navigation - There's an admin-block halfway down the page that references a bunch of settings in the LocalSettings.php file. However, the link seemed broken. I replaced this:

{{mediawiki|Manual:LocalSettings.php#Upload location|upload location}}

with this:

[[Manual:LocalSettings.php#Upload location]]

, and the same for the other links in the block.

Help:Starting_a_new_page - Similar edit to the one at Help:Navigation. In the admin-tip, replaced:

{{mediawiki|Extension:Inputbox|inputbox extension}}

with

[[Extension:Inputbox|inputbox extension]]

Help:Navigation references LocalSettings.php, which in turn references Template:Caution. Let's tackle that one next. First of all, we need the proper image referenced. The SVG is here, from where you can also download the other formats. I picked sng.

There's also some weird code in the template that I removed. I basically changed the whole top section, which checks the format of the template invocation, and stripped out the ability to use tables (for some reason, the conditional if made my template look all whack):

{{#if: {{{1|}}}|
}}[[Image:Nuvola apps important.svg|18px|Caution!]] '''Caution''': {{#if: {{{1|}}}| {{{1|}}}
}}

with:

[[Image:Nuvola apps important.png|18px|Caution!]] '''Caution''':

More will follow soon :)

MediaWiki - Toolbar

2009-10-20T18:52:00.003+02:00

Recently, we downloaded and installed MediaWiki. To our surprise, our of the box, the toolbar available when installing is lacking quite a few buttons that are standard available on wikipedia itself.

The MediaWiki helpfiles are less than helpful, though they do contain a description on how to add a single (table) button. For those wanting to replicate the look of the actual wiki, here's the code for the remaining buttons. You'll need to make modifications to two files (make backups first):

/w/EditPage.php

Find the following snippet:

),
array(
'image' => $wgLang->getImageFile('button-hr'),
'id' => 'mw-editbutton-hr',
'open' => "\n----\n",
'close' => '',
'sample' => '',
'tip' => wfMsg('hr_tip'),
'key' => 'R'
)

Below/after that, add the following:

,
array(
'image' => $wgLang->getImageFile('button-redirect'),
'id' => 'mw-editbutton-redirect',
'open' => '#REDIRECT [[',
'close' => ']]',
'sample' => wfMsg('redirect_sample'),
'tip' => wfMsg('redirect_tip'),
'key' => 'V'
),
array(
'image' => $wgLang->getImageFile('button-strike'),
'id' => 'mw-editbutton-strike',
'open' => "",
'close' => "",
'sample' => wfMsg('strike_sample'),
'tip' => wfMsg('strike_tip'),
'key' => 'S'
),
array(
'image' => $wgLang->getImageFile('button-linebreak'),
'id' => 'mw-editbutton-linebreak',
'open' => "
",
'close' => "",
'sample' => '',
'tip' => wfMsg('linebreak_tip'),
'key' => 'BR'
),
array(
'image' => $wgLang->getImageFile('button-superscript'),
'id' => 'mw-editbutton-superscript',
'open' => "^{",
'close' => "}",
'sample' => wfMsg('superscript_sample'),
'tip' => wfMsg('superscript_tip'),
'key' => 'I1'
),
array(
'image' => $wgLang->getImageFile('button-subscript'),
'id' => 'mw-editbutton-subscript',
'open' => "_{",
'close' => "}",
'sample' => wfMsg('subscript_sample'),
'tip' => wfMsg('subscript_tip'),
'key' => 'I2'
),
array(
'image' => $wgLang->getImageFile('button-smalltext'),
'id' => 'mw-editbutton-smalltext',
'open' => "",
'close' => "",
'sample' => wfMsg('smalltext_sample'),
'tip' => wfMsg('smalltext_tip'),
'key' => 'J1'
),
array(
'image' => $wgLang->getImageFile('button-hidden'),
'id' => 'mw-editbutton-hidden',
'open' => "",
'sample' => wfMsg('hidden_sample'),
'tip' => wfMsg('hidden_tip'),
'key' => 'HC'
),
array(
'image' => $wgLang->getImageFile('button-gallery'),
'id' => 'mw-editbutton-gallery',
'open' => "",
'close' => "",
'sample' => wfMsg('gallery_sample'),
'tip' => wfMsg('gallery_tip'),
'key' => 'HC'
),
array(
'image' => $wgLang->getImageFile('button-blockquote'),
'id' => 'mw-editbutton-blockquote',
'open' => "
",
'close' => "
",
'sample' => wfMsg('blockquote_sample'),
'tip' => wfMsg('blockquote_tip'),
'key' => 'B1'
),
array(
'image' => $wgLang->getImageFile('button-table'),
'id' => 'mw-editbutton-table',
'open' => "{|",
'close' => "|}",
'sample' => 'class="wikitable sortable border=1 cellpadding=5"
|+ table name
|-
! style="color:#AAB5BE;background-color:#24313F;" | header 1
! style="color:#AAB5BE;background-color:#24313F;" | header 2
! style="color:#AAB5BE;background-color:#24313F;" | header 3
|-
| style="color:#24313F;" | row 1, cell 1
| style="color:#24313F;" | row 1, cell 2
| style="color:#24313F;" | row 1, cell 3
|-
| style="color:#24313F;" | row 2, cell 1
| style="color:#24313F;" | row 2, cell 2
| style="color:#24313F;" | row 2, cell 3
',
'tip' => wfMsg('table_tip'),
'key' => 'A'
),
array(
'image' => $wgLang->getImageFile('button-reference'),
'id' => 'mw-editbutton-reference',
'open' => "",
'close' => "",
'sample' => wfMsg('reference_sample'),
'tip' => wfMsg('reference_tip'),
'key' => 'R1'
)

For icons, refer to the link above, and find the link at the first option.

Secondly, you need to edit /w/languages/messages/MessagesEn.php. This file will allow us to add both the path to the actual icons (which ought to be placed in /skins/common/images, by the way), and assign a tooltip text (which shows when you hover your mouse over a button) and a sample (which will be put as an example when you click the button. For instance, when you will click the strike-out button, this will show up: ~~Struck out text~~, and Struck out text is the sample).

Find:

'button-hr' => 'button_hr.png',

Below it, add:

'button-table' => 'button_table.png',
'button-redirect' => 'button_redirect.png',
'button-strike' => 'button_strike.png',
'button-superscript' => 'button_sup_letter.png',
'button-subscript' => 'button_sub_letter.png',
'button-linebreak' => 'button_linebreak.png',
'button-smalltext' => 'button_small_text.png',
'button-hidden' => 'button_hide_comment.png',
'button-gallery' => 'button_gallery.png',
'button-blockquote' => 'button_blockquote.png',
'button-reference' => 'button_reflink.png',

Then find:

'hr_tip' => 'Horizontal line (use sparingly)',

And replace it with:

'table_tip' => 'Insert table (3 by 3)',
'redirect_tip' => 'Redirect',
'redirect_sample' => 'Target page name',
'strike_tip' => 'Strike',
'strike_sample' => 'Strike-through text',
'subscript_tip' => 'Subscript text',
'subscript_sample' => 'Subscript text',
'superscript_tip' => 'Superscript text',
'superscript_sample' => 'Superscript text',
'smalltext_tip' => 'Small text',
'smalltext_sample' => 'Small text',
'hidden_tip' => 'Insert hidden comment',
'hidden_sample' => 'Comment',
'gallery_sample' => 'Image:Example.jpg|Caption1
Image:Example.jpg|Caption2',
'gallery_tip' => 'Insert a picture gallery',
'blockquote_tip' => 'Insert block of quoted text',
'blockquote_sample' => 'Block quote',
'reference_tip' => 'Insert a reference',
'reference_sample' => 'Insert footnote text here',

That's all there really is to it. Your toolbar now reflects the standard one at wiki.

The ten IKEA commandments

2009-08-19T20:11:00.002+02:00

Today, news reported that the bible is no longer the most published book on earth, but that it in fact has been outnumbered in number of copies by the IKEA catalogue.

This calls for ten brand new commandments. The new ten IKEA commandments:

1) Thou shalt not buy assembled furniture, nor shalt thy buy furniture elsewhere.
2) Thou shalt always count thy screws before commencing assembly.
3) Thou shalt eat Swedish meatballs while thy shoppeth.
4) Thou shalt not cut ahead in line.
5) Thou shalt praise the brilliance of the cam screw.
6) Thou shalt be allowed to return thy damaged on delivery products.
7) Thou shalt not expect to be given a time of delivery.
8) Thou shalt make use of the children's playroom while thy shoppeth.
9) Thou shalt secure thy furniture to the wall in order to avoid toppling, and thou cannot sue IKEA in case of non-compliance, neener, neener.
10) The Allen Wrench shalt be the object of nightly worship.

To Business Objects or not to Business Objects?

2009-05-18T11:12:00.004+02:00

Just out of curiosity. How do others consider Business Objects? My former company, and my current employer use it as a reporting tool on top of their data warehouse. Basically, between the database layer and the end-user, there resides a Business Objects universe that acts as a semantical layer translating database-objects into business entities, which allows endusers without any knowledge of SQL whatsoever to quickly generate their own reports.

That's the theory behind it, anyway. In reality, expecially at my current employer, I have to develop the reports, even after the universe phase, because our client has all kinds of specifications for report layouts (that the Business Objects reporting suite was never intended to be used for, but the client already sold it to their client, and seeing THEY don't have to build it anyway, that's all fine and dandy). Add to that that the existing universe is plain unfit to be consumed by end-users, and I'm seriously wondering why on earth the company is paying top dollars every year for a product that greatly complicates even the most trivial tasks.

I present to you the following theory I came to develop over my time of working with Business Objects: "A properly desgined data warehouse (meaning star schemas using dimensions that make sense to business users) has no need for a translational layer like Business Objects provides". In other words, a proper star-schema makes the universe part of the BI implementation redundant, which will lead to the Business Objects reporting suite becoming at best a limiting (not to mention expensive) engine for making the data look pretty.

My main complaints with Business Objects:

* Pricing
* An engine that greatly complicates even the most trivial tasks
* Poor documentation and error messages
* Reliability is horrible. More than once I experienced things "magically" dissappearing or changing. At first I dismissed these as incidents or failure on my part, but I managed to be able to "reproduce" these anomalies.
* When you're working with a proper data warehouse, the universe layers adds extremely little value.

I'd be interested in hearing other people's opinions.

Removing XP from Windows Boot Manager

2008-12-19T09:56:00.002+01:00

The other day I figured I'd try installing XP on my Vista lappie. Unfortunately, I did not bother to read properly, and just went ahead and tried. Now one problem I have is that my DVD drive in the laptop has been wonky from the getgo. I also did not bother creating a second partition (d'oh).

Anyway, after starting the installation and going through the initial setup steps, my laptop started showing a new screen called Windows Boot Manager which showed the new XP setup, and was for some reason using that as a default boot. The end result was constant blue screens.

I eventually solved the problem by first having Vista display hidden folders and files, as well as OS files. Having done that, removing the XP entry is as simple as deleting the boot.ini file from the root folder of the drive. You can also remove the XP install folders called $WIN_NT$.~BT and $WIN_NT$.~LS.

Business Objects - Custom entry to list of values

2008-12-08T08:13:00.011+01:00

After recently having joined a new employer, I'll be blogging a bit more about Business Objects and some tricks and tips to get certain stuff done.

Let's start with adding a custom entry to a list of values (lov). In this case, we want to give a user the ability to select in order to select all the values in the lov.

First we of course create the lov itself. In this case, Iøll show a mockup of a lov of clients that pulls data from a table created specifically for the purpose. Imagine that during the ETL process, a SELECT DISTINCT (ClientName) fills the table every night, in order to avoid having to do the SELECT every time an end-user retrieves an updated copy of the universe. It would look something like the following:

With the list of values itself in place, we now create a new object that will contain a dummy string called . In Oracle, we can do this by referencing the SYS.DUAL table. Let's start by creating the value itself:

Add the SYS.DUAL tab le to the universe, and then proceed by clicking on the "Table" button. There, select sys.dual from the list of tables. This will ensure the object will parse properly.

Now open the original list of values again, and select the "properties" tab. Supply a meaningful name for the list of values, and then click the "Edit" button. Here, we will add the newly created object to the existing list of values. Click on the "Combine Queries" button (circled) in order to add the extra object. You will notice a second tab called "Query 2" appearing. Delete the existing variable there, and select the object:

Click "OK" twice to return. At this stage, I usually choose to hide the object, as its only purpose is to enhance the list of values already there. It also is recommended to ensure that on the properties page of the object, you turn off "Associate a list of values".

To finish up, create a filter prompting the user to select a value from the list of values. To this, we will add the option to also select . It would look something like the following (this does assume there is an object "Blog\Client" available, from which the value will be compared with the filter):

This should do the trick. Please be aware that the screenshots contain mockups that have not been validated against a real database, and therefore they might be not neccessarily work 100%. If you find this is the case, please let me know, and I will try to do it over again, and ensure it works against the eFashion universe.

Failed to execute the request because the ASP.NET process identity does not have read permissions to the global assembly cache. Error: 0x80070005

2008-06-09T19:31:00.005+02:00

I started working on a new website yesterday, and this time, rather than starting it as a webpage, I figured I'd use the proper TheBeerHouse (TBH) approach, and actually create a blank project, and then add the website as an existing project to it.

I referenced the other projects required for everything to run (a web captcha control and a CustomEvents project), renamed all the namespaces, and figured I'd be on my way.

But my trustworthy Internet Explorer decided to give me hell, and just showed a page with an error message in bold red:

Server Application Unavailable

The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.

Administrator Note: An error message detailing the cause of this specific request failure can be found in the system event log of the web server. Please review this log entry to discover what caused this error to occur.

The application log showed the following messages (abbreviated)

Failed to initialize the AppDomain:/LM/W3SVC/1/Root/

Exception: System.IO.FileLoadException

Message: Could not load file or assembly 'System.Web, Version=2.0.0.0, Culture=neutral,PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Access is denied.

Followed by:

Failed to execute the request because the ASP.NET process identity does not have read permissions to the global assembly cache. Error: 0x80070005 Access
is denied.

And finally:

aspnet_wp.exe (PID: 1924) stopped unexpectedly.

As it turns out, due to setting up the site as a project, ASP.NET doesn't automatically get its permissions assigned to the project folder. Doing that manually solved the problem.

Security tab missing in XP

2008-06-09T19:23:00.003+02:00

I was troubleshooting an annoying problem on my PC when I ran into another annoying problem. Whenever I wanted to check access rights to a specific folder, there was no "Security" tab available. Kind of makes it hard to determine whether the account has the proper permissions.

Here's how to solve it:

1) Open up Windows Explorer.
2) Tools -> Folder Options -> View
3) All the way at the bottom of the listbox under "Advanced settings", ensure “Use simple file sharing (Recommended)” is not checked.
4) Click OK, and check permissions at will.

(I can see why simple file sharing is recommended by default, but some form of indication would have been nice).

Collation and CHECKSUM do not play together nicely.

2008-05-12T15:43:00.004+02:00

If you work with data warehouses, like yours truly, most likely you’ve had to write code that checks your dimension tables for changes. Our usual approach is to pump the most recent copy of the table into a staging table, and then compare it to the latest version of the data.

Roughly, the comparison would be something like:

SELECT
P.Column1
, P.Coulmn2
FROM
ProductionTable P
INNER JOIN StagingTable S ON P.PKID = S.PKID
WHERE

Followed by a piece of code where the individual columns are compared. There’s two methods that we use: A column by column comparison (WHERE P.Column1 != S.Column1 OR P.Column2 != S.Column2), or a CHECKSUM method, where a CHECKSUM is done over all the columns of the staging table, and compared against the CHECKSUM over all the columns of the production table (WHERE CHECKSUM(S.Column1, S.coulmn2) != CHECKSUM(P.Column1, P.Column2).

Today, when testing some new code, I found that even though the values in the two tables were identical (except for one column I excluded), and yet, SQL Server reported the checksums to be different. A bit more investigation revealed that the reason behind this was because of collation differences. For those of you who never heard of it (I didn’t until about a year ago, despite having worked with databases for 5+ years in numerous international organizations), here’s an excerpt from BOL (“Selecting collations”):

Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales. For example, in an ORDER BY clause, an English speaker would expect the character string 'Chiapas' to come before 'Colima' in ascending order. But a Spanish speaker in Mexico might expect words beginning with 'Ch' to appear at the end of a list of words starting with 'C'. Collations dictate these kinds of sorting and comparison rules. The Latin_1 General collation will sort 'Chiapas' before 'Colima' in an ORDER BY ASC clause, while the Traditional_Spanish collation will sort 'Chiapas' after 'Colima'.

Now the source system we pull our data from has a default collation of US-English, and our warehouse system uses Norwegian/Danish. Essentially, this means whenever we try and run comparison checks or updates between staging and production, we have to add a COLLATE DATABASE_DEFAULT clause in order to “convert” one set of values to another. That this also was an issue with CHECKSUM never occurred to me.

Here’s some sample code to prove my point. We create a test table with two different collations, and insert identical values into them. First, we’ll run a SELECT to see the CHECKSUMs are different, a second select which does a collation conversion on the US-column, and finally a statement that boxes the “other” collation column to the proper one for the database, which will result in an identical CHECKSUM. The code was run on a database with a US collation, by the way:

CREATE TABLE dbo.CHECKSUM_TEST
(
US nchar(10) NULL,
DK nchar(10) COLLATE Danish_Norwegian_BIN NULL
) ON [PRIMARY]

INSERT INTO CHECKSUM_TEST
(US, DK)
VALUES ('foo', 'foo')

INSERT INTO CHECKSUM_TEST
(US, DK)
VALUES
('bar', 'bar')

SELECT US, DK, CHECKSUM(US), CHECKSUM(DK)
FROM CHECKSUM_TEST

SELECT US, DK, CHECKSUM(US), CHECKSUM(DK COLLATE DATABASE_DEFAULT)
FROM CHECKSUM_TEST

DROP TABLE CHECKSUM_TEST

I’ve deliberately used Unicode rather than non-Unicode for the columns, to prove that doesn’t really help to deal with this particular issue. Below are the results from the three statements:

US DK
---------- ---------- ----------- -----------
foo foo 59294611 24735
bar bar -2088615046 25698

(2 row(s) affected)

US DK
---------- ---------- ----------- -----------
foo foo 59294611 24735
bar bar -2088615046 25698

(2 row(s) affected)

US DK
---------- ---------- ----------- -----------
foo foo 59294611 59294611
bar bar -2088615046 -2088615046

The lesson learnt here is to be careful when using CHECKSUM in combination with mixed collations, and when you do, it’s important to use the COLLATE statement on the proper column ;)

How to use a Mac

2008-02-01T12:50:00.000+01:00

Instructions on how to use a Mac:

1) Unplug Mac from any electrical input.
2) Get 1 gallon of water. Personally, I find water from the toilet works fine. If you're environmentally friendly, or your name is Al Gore, rainwater is the way to go.
3) Pour the water into any crevice in the Mac you can find.
4) Take a 5 minute break while surfing www.microsoft.com and chanting "WINDOWS, WINDOWS, WINDOWS".
5) Find a heavy object. Anvils, bowling balls, Oprah, whatever. Tie heavy object to the Mac.
6) Open the window. If the number of your floor is less than 5, take the stairs up, or find another building.
7) Drop Mac out of the window onto concrete underground (best results achieved when no potential passerby's are between Mac and concrete when gravity does its wonderful work).
8) Grab sledgehammer and go outside.
9) Until no pieces of the Mac are bigger than 1 square centimeter, bring down sledgehammer onto Mac.
10) You're done! Now go back inside, buy a REAL computer, and read Bill Gates' memoires.

.NET Framework source code available!

2008-01-17T08:49:00.001+01:00

Granted, this has little to do with developing the website (something that I've been slacking in for a bit now), but it's just damn cool.

Scott Guthrie posted a note yesterday that the source code of the .NET Framework Libraries is available now. This means you can actually debug the Microsoft .NET classes!

In the comments he also mentions that a downloadable version will be made available as well, which ensures you don't have to be online to see the source.

Very cool. Read more on Scott's blog here.

[w] - Waiting for

2008-01-14T09:16:00.001+01:00

How often doesn't it happen that you find yourself digging through your mail in order to see if there's anything you initiated, but for which you are dependant on others?

If you're chaotic like me, that happens quite a lot. Often I'll have tried setting things in motion, only to find out later that nothing happened so far. Usually this means I'll have to waste quite a bit of time digging through my mail in order to see when I sent the mail, to who, and whether anything happened.

This morning I came across an easy tip that will possibly save me all that time. In short, you create a new Outlook folder, and a rule that checks your outgoing mails. Should your mail contain a [w] tag, it will automatically be moved to your newly created folder, and you'll have a very easy way to have all items that require interaction from others listed together in a single spot.

Find out more on http://waiting-for.com/.

SQL Server 2005 varchar(max) in C#

2008-01-04T11:07:00.000+01:00

An interesting question at work today: How to properly define a parameter that's varchar(max) in Sql Server 2005 from within C#?

Sure, it should be possible to use the signature without adding the integer value. But what if a custom DataAdapter is being used that does require a value if you supply a string? We could use 4000 as a value, but that kind of defeats the purpose of using the new datatype. 8000 then? Same difference; you still possibly end up with truncated strings. Should we make it 2bn? That's 2GB of memory. Not really an otpion in my book.

After some googling, we found the answer: the size of the parameter should be set to -1. There's an example here, where the -1 value is not explained in detail, but just shown in the code. We tested, and it works like a charm.

Humanized dates in C#

2007-10-24T16:01:00.000+02:00

I've been looking for a way to make dates in the Forum sections of the site more user-friendly. Did some googling on how to accomplish this in either SQL or C#, but somehow that came up completely blank.

I did manage to find some scripts for some stuff I never heard about, and 1 javascript file. Just my luck. I seriously dislike javascript. Maxxim from the wrox forum will most likely be chuckling now, since he's been advising me to brush up my skills in Javascript ;)

So here's the translation in C#. Since I'm not sure about the copyright notice, I'll just slap a copy of the original in:

/**
* C# translation: Copyright (c) 2007 Peter Schmitz
* http://entropia-online.blogspot.com
*
* Original Javascript code: Copyright (c) 2007 Blake Householder
* http://www.blake8086.com/
*
* Permission is hereby granted, free of charge, to any person
* obtaining a copy of this software and associated documentation
* files (the "Software"), to deal in the Software without
* restriction, including without limitation the rights to use, copy,
* modify, merge, publish, distribute, sublicense, and/or sell copies
* of the Software, and to permit persons to whom the Software is
* furnished to do so, subject to the following conditions:
*
* The above copyright notice and this permission notice shall be
* included in all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
* EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
* NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS
* BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN
* ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
* CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
* SOFTWARE.
*
*
*/

public string HumanizeDate(DateTime date)
{
DateTime dateNow = DateTime.Now;

// Determine the start of the date's week
DateTime startOfWeek = (date.Date - TimeSpan.FromDays((double)date.DayOfWeek));

// We'll use this variable for monthly comparison
int intMonthCompare = ((date.Year * 12 + date.Month) - (dateNow.Year * 12 + dateNow.Month));

// Do the monthly comparison first, as that's the biggest possible way to group
if (intMonthCompare > 1)
{
return "Beyond next month";
}

if (intMonthCompare < -1)
{
return "Older";
}

if (intMonthCompare == 1)
{
return "Next month";
}

if (intMonthCompare == -1)
{
return "Last month";
}

// Now do the same, but in weeks
TimeSpan ts = (startOfWeek - dateNow.Date);
if (ts.Days > 28)
{
return "Five weeks from now";
}

if (ts.Days > 21)
{
return "Four weeks from now";
}

if (ts.Days > 14)
{
return "Three weeks from now";
}

if (ts.Days > 7)
{
return "Two weeks from now";
}

if (ts.Days > 0)
{
return "Next week";
}

if (ts.Days < -35)
{
return "Five weeks ago";
}

if (ts.Days < -28)
{
return "Four weeks ago";
}

if (ts.Days < -21)
{
return "Three weeks ago";
}

if (ts.Days < -14)
{
return "Two weeks ago";
}

if (ts.Days < -7)
{
return "Last week";
}

// Nothing found so far. Let's see if it's tomorrow, today, or yesterday
ts = date.Date - dateNow.Date;

if (ts.Days == 1)
{
return "Tomorrow";
}

if (ts.Days == 0)
{
return "Today";
}

if (ts.Days == -1)
{
return "Yesterday";
}

// Still nothing? Must be a different day in this week then
return date.DayOfWeek.ToString();
}

A strange error...

2007-10-07T12:56:00.000+02:00

It's been a while since my last post. That is mostly due to trying to actually get the website as a whole working.

We planned on going beta last weekend, but illness and distractions (in the form of LOTRO) put a stop to that.

To top things off, I've been worrying about the forums. Our potential members are used to the vBulletin forums, and the current TBH forums are nothing even remotely like it. Heck, there's no subforums, and not even the ability to send one another private messages. In itself, that shouldn't be too hard to implement, but one also should take into account that just building it is not going to cut it.

The end-user will also demand to actually use the functionality, and vBulletin has all that conveniently located.

So for now, I'm undecided where to go. I downloaded a free forum coded in C# yesterday, courtesy of Frans Bouma, that I will give our beta users to test. I also can try just buying vBulleting, and then writing code to integrate the users from my site into the forum databases (which is mySql). As a third alternative, I can try and code things myself, and see if other TBH users are interested as well in adding functionality.

Time will tell.

In the meantime, I came across a particular error today:

"Error 110 It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error can be caused by a virtual directory not being configured as an application in IIS. C:\...\EntropiaOnline\forumsbck\web.config 56 "

Never seen it before, but what happened is that yesterday I changed some code. In order to ensure I'd not lose anything, before I overwrote the code on the actual site, I grabbed the code I was going to replace, and put it in a local subfolder of my website.

Due to there being a web.config file in it as well, I started getting the error. The solution was to remove the newly created folder from the project-folder. The error is caused by having duplicate web.configs in subfolders, that are not "registered" in Visual Studio (by registered, I mean you did not add the folder itself to the project).

Reusing command parameters

2007-09-10T20:55:00.000+02:00

A while ago, at that hobby I practise during the day (some call it work. I resent that. Work is what I do when I come home), I was writing a piece of VB.NET code (I know... I know...) that would fire off a bunch of stored procedures, all with the exact same parameters.

So I wondered if I really had to define the parameters once for every time I called the procedures, or whether I could actually just change the CommandText.

I created a little test app, and went away. Today, I rewrote the code to C#, and decided to put it up here, in order to show that reusing command parameters is perfectly legal in .NET (and to me, it actually sounds logical too, as it prevents the coder from having to rewrite the same code over and over again).

I created a page with one single button, and two labels (I left their names unchanged, as I was doing this during worktime, and I wanted to have the test take as little time as possible.

I added the code, with a few comments thrown in for clarity, and used the labels to post the ID's of the first records I got back from my stored procedures.

Next I fired off the code, while keeping an eye on my profiler, and sure enough, both the stored procedures were executed. I then changed the code a little and increased the value for the second stored procedure, in order to see if that would still work. It did:

Click here to download the sample project and test it for yourself. The project assumes you have the Adventureworks database installed. you will have to create the two stored procedures usp_Test1, and usp_Test2 as well (they're also included in the ZIP file).

I won something...

2007-09-05T20:51:00.000+02:00

After I finished up my CRUD script, I submitted it to a competition I stumbled upon while reading up at www.sql-server-performance.com. Mostly in the hope of trying to gain some attention for the script. Besides, when do I ever win anything?

Well, this time I did! I just received an E-mail stating I won a full-conference pass to the PASS summit in Denver, Colorado.

I won't be able to attend, but I definitely am proud at the moment :)

CreateUserWizard - SendingMail event

2007-09-05T20:13:00.000+02:00

After following the examples in the book, I figured I wanted to add some extra functionality to the Membership and UserProfiling section.

While it allows your users to signup smoothly and simply, using a CreateUserWizard, I wanted to add an option where my site sends the user a link with a GUID embedded in it, in order to verify that the user didn't only fill in a properly formatted E-mail address, but actually does own the mail address that was entered.

I went off on my merry way, using a code snippet from my local installation of MSDN as a basis:

e.Message.Body.Replace("<%PasswordQuestion%>", Createuserwizard1.Question);
e.Message.Body.Replace("<%PasswordAnswer%>", Createuserwizard1.Answer);

So my code became:

protected void SendMail(object sender, MailMessageEventArgs e)
{
// Generate a GUID
string guidResult = System.Guid.NewGuid().ToString();

// Code to paste into a URL, which I cannot paste cause Blogger throws a fit.

e.Message.IsBodyHtml = true;
e.Message.Body.Replace("<%Link%>", url);
}

After running the code, nothing happened. I set a breakpoint and tried again. Nothing. I commented out several options that the book carried as extra steps that I thought might interfere. Still nothing.

I then tried hooking the UserCreated event to the SendMail event that I defined, but ran into trouble when trying to create EmailEventargs to pass as a parameter.

And luckily, then I googled on "CreateUserWizard MailMessageEventArgs", and came across Bryant's blog.

The last line of code now reads:

e.Message.Body = e.Message.Body.Replace("<%Link%>", url);

And lo and behold, problem solved, and I find a neat link included in my mail :)

Bryant, I owe you a beer!

And to those still unconvinced by the Internet, I predict that some day it will be a big hit!

E-mailing from ASP.NET

2007-09-03T21:19:00.000+02:00

I've been working on a website for a while now. As a base, I use the book ASP.NET 2.0 Website Programming: Problem - Design - Solution (Programmer to Programmer). Now using that, you actually add code to have E-mail sent from within your application using SMTP.

However, I couldn't get it to work. My E-mail address seemed valid enough, as did the code in my web.config:

Since I was planning on adding a new feature to the existing code to E-mail a new member a link they have to click before having their account activated, it seemed like a good idea to first sort out the whole mail thing.

I fiddled with a few things, but kept getting a: "5.7.1 Unable to relay for " error.

The solution is laughably simple (thankfully). Go to Internet Information Services (under Administrative Tools), expand your IIS server, and select right-click the Default virtual SMTP server. Select Properties, and go to the Access tab.

Under the "Relay restrictions" section, click the "relay" button. There, ensure the "Only the list below" option is selected, and press the "Add" button.

Under "Single computer", fill in 127.0.0.1, and click OK. That's it!

For security's sake, I chose to uncheck the checkbox named "Allow all computers which succesfully authenticate to relay".

A free script to create CRUD procedures.

2007-08-29T19:07:00.000+02:00

Recently, both at my job, as well as while working on the site, I found myself having to write countless CRUD stored procedures in order to facilitate retrieving and storing data in my tables. The majority of these reflected an exact replication of the database table model (i.e. updating all columns), and I found writing those stored procedures to be a real burden. It was boring, repetitive and error prone work (especially the UPDATE procedures, where one has to replicate columns three times (once as input variables (which includes data types), once as columnnames, and once again as variables (but this time without the columnnames).

Being the lazy guy I am, I decided I would automate the matter, and write a script to automatically create the procedures for me. Before I started, I decided I would make a few starting assumptions:

1) I wanted the SELECT procedure to be able to accept either an ID-number or a Name (assuming that like me, you called the column something involving '%Name') for an individual row, or neither, in which case the whole table should be returned.
2) I wanted to be able to have the DELETE procedures to be optional. I achieved this by using a bit-flag and IF statements to see if it ought to be created or not.
3) I wanted the user to be able to specify certain columns to exclude. In the database I'm working on for the site, there are numerous audit columns to keep track of when data is entered or modified, and by who. Naturally, this is not required in all of the procedures, so I wanted a mechanism for the end-user to include or exclude these columns.

The result of my work is the script below. I've briefly tested the generated procedures against my own database, and there, they work like a charm. Perhaps others who run more specialised or customised databases will be able to come up with unexpected errors etc. If so, please do let me know.

Of course, the script will not cover 100% of anybody's specific wishes. In fact, I'm quite sure that some of the procedures will need to be customised to suit your needs, but you will find this script will enable you to start with at least some foundation in place.

I'm releasing this script under the Creative Commons Attribution Non-Commercial No Derivatives license, which means you are free to download and share the script, provided you mention and link back to me. However, you are not allowed to change or commercially use the script. I might change this to a less restrictive license in the future, however for now, I want to be able to evaluate comments etc first. This is the first time I ever released anything under a license, so any comments regarding that are more than welcome.

A list of things I have figured I could enhance the script with:

1) Add error handling to the stored procedures.
2) Add support for schemas in SQL Server 2005.
3) Add paging support to the select procedures, by adding two additional parameters.

Also, I might later on decide to automatically generate .NET custom classes based on the exact database tables.

Click here to download the script.

One last comment, should you decide to alter the generated stored procedures to suit your specific needs, do remember to rename the new procedure. The script will automatically drop its standard procedures, in order to always reflect the latest table schemas.

This work is licensed under a
Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 License.

Hierarchical data with Common Table Expressions

2007-08-28T19:36:00.000+02:00

Today, I finally managed to crack a problem that's been bothering me for some time. Some weeks ago I started working on the database schema that the site will be using, and the Entropia auction system gave me some trouble.

You see, the auction in the game is built up hierarchically, and I eventually want to show individual items in the database under the auction section they belong to (as with a lot of items, it's unclear exacty where to find them).

That in itself was easy enough, but not if you want to store the data in third normal form, but at the same time want to use some form of a cookie-trail, so the user will know exactly where to go. (With cookie-trail I mean something along the lines of "Items \ Tools \ Misc Tools"). I chose to store my data in a table with an AuctionCategoryID, AuctionCategoryName, and an AuctionCategoryParentID, the latter of which represents a SELF-JOIN.

(The figure above doesn't actually represent my own table. I chose to give the very bottom nodes of the tree a ParentID of -1, and did not define -1 as an AuctionCategoryID. Hence, creating a Foreign Key is just not possible. The figure is just for clarification).

I first tried meddling with code a bit myself, but found that my code just didn't do the trick. Sure, I could hard-code a number of self-joins, but what if suddenly more categories were added? I'd have to alter my function. That was definitely not an option.

I tried a few more things, but found my code was not robust enough, and that there were sorting troubles (among other things).

I Googled a bit, and initially ended up with a solution like this (Joe Celko describes a similar method in his "SQL for Smarties"). Hardly elegant, and hardly dynamic (although it would be possible, of course, to ensure there would be a difference of a few numbers between each of the individual "nodes". However, that still potentially means I could run out of space, and would then have to manually alter stuff again. Not an option if you ask me).

Enter the recursive Common Table Expression. BOL doesn't seem very helpful, but hopefully the code below will make things easier to see.

The first query within the Common Table Expression defines the root level. As you see, the -1 value I mentioned above is hard-coded. The UNION ALL is the key to ensuring the self-join will work for an indefinte number of child nodes. The second query just fills in those child nodes.

Note that it is possible to potentially create an infinite loop, though CTEs also offer a way to cap this.

The code:

WITH AuctionPath -- The name of the CTE
(
[Name]
, [AuctionCategoryID]
, [Path]
, [Level]
)
AS
(
SELECT
AuctionCategoryName -- Name
, AuctionCategoryID -- AuctionCategoryID
, CONVERT(varchar(255), AuctionCategoryName) -- Path
, 1 -- Level
FROM
AuctionCategories
WHERE
AuctionCategoryParentID = -1 -- Only the parent nodes here
UNION ALL
SELECT
AuctionCategoryName -- Name
, AuctionCategories.AuctionCategoryID -- AuctionCategoryID
, CONVERT(varchar(255), Path + ' \ ' + AuctionCategoryName) -- Path
, [Level] + 1 -- Level
FROM
AuctionCategories
INNER JOIN AuctionPath ON AuctionCategories.AuctionCategoryParentID = AuctionPath.[AuctionCategoryID]
)
SELECT
[Name]
, [AuctionCategoryID]
, [Path]
, [Level]
FROM
AuctionPath
ORDER BY AuctionCategoryID
OPTION (MAXRECURSION 5); -- The maximum amount of recursions

Some things of note. There are certain conditions to recursive CTEs. From BOL:

The number of columns in the anchor and recursive members must be the same.
The data type of a column in the recursive member must be the same as the
data type of the corresponding column in the anchor member.

In the quoted text, anchor is what I have refered to as root node, and recursive member is a child node.

I hope this helps a little if you're facing a situation where you need to do hierarchies. Once you manage to get the trick, there's nothing to it, really :)

Many thanks to both Kalman Toth and russellb over at the sqlmag forums.

Roman numerals

2007-08-26T11:44:00.000+02:00

If you play Entropia Universe (formerly Project Entropia), you're probably aware that mining depositsizes have two distinctive identifiers. The first being the name of the claimsize (i.e. tiny, very poor, poor, all the way to Immense). However, those terms do not clearly indicate which of the two is larger.

In the case of very poor vs. poor, it's obvious, but is Significant bigger or smaller than Substantial? In order to avoid that kind of confusion, MindArk also decided to give claims a Roman numeral indication.

I order to have the database reflect these numerals, I decided to create a function that will translate our number system into Roman numerals. It works surprisingly easy, actually. The function takes an input parameter, which is the number to be translated.

It then determines the regular numbers, the tens, the hundreds, and the thousands, and translates those into their Roman counterparts using a case statement. When it encounters a 4 or a 9 (which, obviously, are the special numbers because they are not a simple repetitive sequence of characters), it will translate those as well.

CREATE FUNCTION [dbo].[fn_ToRoman]
(
@InputNumber INT
)
RETURNS VARCHAR(16)
AS
BEGIN
DECLARE
@Singles INT
, @Tens INT
, @Hundreds INT
, @Thousands INT

SELECT
@Singles = @InputNumber%10
, @Tens = @InputNumber%100/10
, @Hundreds = @InputNumber%1000/100
, @Thousands = @InputNumber%10000/1000

RETURN
(
SELECT
REPLICATE('M', @Thousands) +
CASE
WHEN @Hundreds THEN REPLICATE('C', @Hundreds) WHEN @Hundreds = 4 THEN 'CD' WHEN @Hundreds = 9 THEN 'CM' WHEN @Hundreds >= 5 THEN 'D' +
REPLICATE('C', @Hundreds - 5)
ELSE ''
END +
CASE
WHEN @Tens THEN REPLICATE('X', @Tens) WHEN @Tens = 4 THEN 'XL' WHEN @Tens = 9 THEN 'XC' WHEN @Tens >= 5 THEN 'L' + REPLICATE('X', @Tens - 5)
ELSE ''
END +
CASE
WHEN @Singles THEN REPLICATE('I',@Singles) WHEN @Singles = 4 THEN 'IV' WHEN @Singles = 9 THEN 'IX' WHEN @Singles >=5 THEN 'V' + REPLICATE('I',@Singles-5)
ELSE ''
END
)
END

Yes! Code regions in SQL :)

2007-08-19T12:07:00.000+02:00

Currently I'm working on a long long long SQL script that automatically writes CRUD (Create, Read, Update, Delete) procedures based on a table's definition. If you are like me, you will find that you group certain code together.

In Visual Studio, you can conveniently wrap #region tags around the code, so you can just collapse the region when you know certain stuff works, and doesn't need to be edited anymore. Not so in SQL Query Analyzer (or SQL Server Management Studio for that matter).

SQL Visualizer does offer that option though. You start a region with a -- REGION comment, and end with -- END REGION.
Because it works with comment blocks, your code will work anywhere. Except within this tool, you will be able to now use regions:

I've used other SQL tools before, and quickly found myself abandoning them due to being, well frankly, crap. I'll have to work with this one a bit more before I can actually judge it properly, but regions in SQL code are definitely something I miss in the Microsoft environment.

Update 08/27/2007: I've decided to not use SQL Analyzer a lot, unless it's for developing large scripts like the CRUD script I mentioned before. Reason is that it's not possible to copy/paste output messages (which, if you're building dynamic SQL scripts, is kind of neccessary to test).

SSIS - DataReader issue

2007-08-15T10:49:00.000+02:00

Just a quick note, unrelated to anything to do with the actual website.

Today, at work, I was working on a task that involves moving data from a production database to a Historic database. The production environment has been misused at times in the past, and as a result, there's numerous backups of tables residing in the database.

Due to regulations, just dropping the tables or wiping data is not an option. Hence, we chose to move it to the historic database. Now to accomplish this, I selected SSIS as our prefered tool. Stupidly enough, I chose to use ADO.NET as a data connection. The fun started when I was creating the actual "Data Flow" objects. I must have created a dozen of them when suddenly I was confronted with an error that SSIS was unable to translate from unicode to non-unicode data.

Huh?

I checked my CREATE TABLE scripts, and sure enough, the data types in both source and destination were set to char. I tried altering the Source's External columns, which seemed to work from the colum's properties, but actually didn't if you would check the datatype coming into the Source. I tried fiddling with the Source's Output column, but got an error message that I was not allowed to. I tried explicitly casting the column as char in the Source's SELECT statement. No luck.

A colleague came over and suggested using a Dereived Column object. Again, that didn't work. I ended up Googling, and came to an MSDN link. There, it stated somewhere halfway the page:

"One issue when using ADO.NET is that there is no ADO.NET data type corresponding to VARCHAR (non-Unicode character) in relational databases. Therefore, ADO.NET data sources output all character data as Unicode. This can cause problems with simple components that use ADO.NET sources and destinations that expect single-byte character types. You can work around this by using a Data Conversion transformation."

I tested this, and using a Data Conversion did indeed work. But what a lot of extra work to do something relatively simple. I decided to just switch all connections to OLEDB.

Conclusion: When dealing with non-unicode char columns, OLEDB might be the better choice.

Moan: Commenting while programming

2007-08-10T17:20:00.000+02:00

Att: Microsoft, Oracle, Sun
CC: WWC, Google (they're in on anything, so why not CC them? At the very least they'll get a chuckle out of it)

Subject: Programming comments

Dear Sirs/Madams,

If you can find some time in your undoubtedly busy schedules, would it be possible for you to discuss a universal combination of characters to note a line or block of text within code as comment, please?

As a programmer, I love commenting my code. It makes it easy for me to identify what bits of code do, to indicate why I used a certain approach, and my peers will get the same benefits. All in all, comments are our friends.

However, I have never understood the reason for having numerous ways to define comments. For instance, see the following snippets:

Visual Basic:
' This is a comment. Start the line with an apostrophe.

C# / Java:
// This is a comment.

SQL / C# / Java / Oracle:
/* This is a comment block */

SQ: / Oracle:
-- This is a comment

HTML:
<% -- This is a comment -- %>

Etc.

Could it be arranged so that there is one standard approach that works in every language? It would make things so much easier for those of us who regularly switch between languages/flavours.

Kthanks.

My next moan will demand we get the exact same syntax, too ;)

Preventing accidental data deletion in SQL Server

2007-08-10T10:05:00.000+02:00

After a few hours of working on the proper table definition for your base table, and filling it with data, you sit back, and decide to do some minor maintenance on a few other tables. You truncate a table, and seconds later realise to your horror that you just purged the table you spent quite some time on filling.

Sound familiar?

It recently happened to me, and I decided to put some mechanisms in place to prevent accidental deletion of data. Of course the best way to prevent accidental deletion is by making sure you are always 100% awake, and you simply do not make errors. Unfortunately that is unrealistic, so I have a few tips to help out.

As a very basic and rather obvious tip, I'll advise you to just make a database backup every time you spent a lot of time on filling in basetables. Now for some added technical tricks:

1) To prevent accidental deletion of data, I decided to use INSTEAD OF triggers. BOL again:

INSTEAD OF
Specifies that the DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. INSTEAD OF cannot be specified for DDL triggers.

At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, you can define views on views where each view has its own INSTEAD OF trigger.

INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION. SQL Server raises an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. The user must remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
Specifies the data modification statements that activate the DML trigger when it is tried against this table or view. At least one option must be specified. Any combination of these options in any order is allowed in the trigger definition.

For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE.

The definition of my trigger is similar to this:

CREATE TRIGGER DeleteTable_1 ON Table_1
INSTEAD OF DELETE
AS
BEGIN
IF @@rowcount > 0
BEGIN
RAISERROR( 'Rows in Table_1 cannot be deleted!', 16, 2 )
ROLLBACK
END
END
GO

When I now try to delete rows (using DELETE FROM Table_1), I will see the following errors in my Query result:

Msg 50000, Level 16, State 2, Procedure DeleteTable_1, Line 7
Rows in Table_1 cannot be deleted!
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Unfortunately, that does NOT prevent the table from being Truncated.

2) To avoid accidently dropping a table, I create views over them and use the option WITH SCHEMABINDING. From Bol (my emphasis):

SCHEMABINDING
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Microsoft SQL Server 2005 Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

SCHEMABINDING cannot be specified if the view contains alias data type columns."

The first bit I emphasised clearly shows that using views in this way is a neat little feature to prevent accidental drops of a table.

The second quote is IMHO also important, as it shows that clearly the table should be "final". If you later on decide that the table in fact needs extra (or less) columns, the VIEW will actually make your life harder.

As an extra tip, when you create a view to use WITH SCHEMABINDING, always use the fully qualified name of the table (i.e. dbo.Salaries instead of just Salaries). Also, you cannot use *, but instead you will have to define all the columns in the query statement.

Nice, but still no protection against a TRUNCATE.

3) In fact, the only way to prevent TRUNCATE from executing on your table is to ensure there is a Foreign Key relationship to the table. If you are (like me) still working on the tables that will eventually link to the base table, you can always create a dummy table that references your base table:

CREATE TABLE [dbo].[Table_2]
(
[TableID] [int] NULL,
[Table_1ID] [int] NULL
)
ON [PRIMARY]

GO
ALTER TABLE [dbo].[Table_2]
WITH CHECK
ADD CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([Table_1ID])
REFERENCES [dbo].[Table_1] ([TableID])
GO
ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]

Now when you try to TRUNCATE Table_1, you will receive the following error:

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'TABLE_1' because it is being referenced by a FOREIGN KEY constraint.

Well, that concludes this post. I hope you will find it helpful in preventing you from accidentally destroying some of your hard work. Of course all comments are welcome :)

Welcome!

2007-07-30T22:59:00.000+02:00

Hi there,

We'd like to take the time to welcome you to the official developer blog of http://www.entropiaonline.com/. This blog will be mostly used to describe some pieces of code that we found to be tremendously helpful while developing the site, discussion of programming techniques, and as a way for us to get feedback from our users as to what they would like us to work on next.

As some initial information, the techniques we will be using include ASP.NET (2.0), SQL Server 2005, and C#. Once we feel the content of the site is to our liking, we will most likely also look into AJAX to enhance the user experience.

Thanks for stopping by!