Friday, November 02, 2007

How to search every table and field in a SQL Server Database

Today I had an issue with Umbraco and a copy of a deleted page appearing in the menu, I'll post how I fix it if I ever do find the answer but while trying to track the issue down I came across a really useful piece of T-SQL from Narayana Vyas Kondreddi (Vyas) that searches each table in a database and then each field in the table. I had to expand it to include integers etc but all credit to him! For reference here's a copy of the code:

Search all tables and fields in a SQL Server Database

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS

begin...end

BEGIN
    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Tested on: SQL Server 7.0 and SQL Server 2000
    -- Date modified: 28th July 2002 22:50 GMT
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL

    begin...end

    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

        begin...end    

        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
            IF @ColumnName IS NOT NULL

            begin...end

            BEGIN
                INSERT INTO #Results
                EXEC
                (

                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + , 3630) + @TableName + ' (NOLOCK) ' +

                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' (NOLOCK) ' +

                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END

        END    

    END

    SELECT ColumnName, ColumnValue FROM #Results
END

How to search every table and field in a SQL Server Database

Useful Links:            Comments [1] 

Tags: SQL Server

 Wednesday, October 17, 2007

Bartering for everyday items

It was recently that time of year again when my bank balance takes a massive hit as various premiums are taken out for things like car tax, car insurance, house insurance etc so I get a little anal about finding the best deal.

This year, my target was to get my car insurance below £1,000 which seeing as I'm now (boo-hiss) over 25 shouldn't have been an issue but I had a feeling it wouldn't be an easy feat seeing as I drive a sports car, heck I like a challenge so off I set.

As I didn't have an issue with my current insurer I thought I'd see what deal they could offer me so they were my first port of call. I knew what they had sent through the post (over £1,500) which I thought was a little steep so I'd give them a chance to knock it down. Success! They took over £100 off -only another £400 to go!

I then went through Money Supermarket's online insurance comparison site to see what else was on offer. It came back with a few closer to £1,200 so I started calling -once again I called my current insurer who came down to £1,100 so I called the next cheapest on the list (£1,200) and told them if they could match the other quote I'd be interested. They of course did and came in at around £900 which was pretty dandy!

This went on for a while, every time I got a quote I would call around each company and give them the chance to "beat" the other one until I was batting between two companies -one being my previous insurer. After careful negotiation I ended up paying just shy of £600 for my insurance and actually ended up with a higher miles allowance than I did at £1,500 -despite what you're thinking, the insurances were otherwise exactly the same! That's a whooping £900 saving for a little phoning around!!

This got me thinking, are we regularly unknowingly paying more for our goods/services? I tend to barter out of principle if I can, usually just as a challenge but is it the same as banks have gone with financing¹ in which case I wonder what other companies are doing it? I know companies often factor in a small % to accommodate the discount requests etc but does that mean we should barter for everything?

¹ I've found when looking for funding, if you want £100 and ask for £100 you tend to get £75 as the bank assumes you have over-inflated your request to accommodate their % reduction so the next time you go in, you ask for £130 instead and so it goes on, each pre-guessing what the other person is after in an environment of distrust leaving those people who don't want to play "the game" (or don't know about "the game") out of pocket.

Bartering for everyday items

Useful Links:            Comments [0] 

Tags: Business | General

 Tuesday, October 16, 2007

Identify which application pool is associated with which W3WP.exe process

Today I needed to identify a site that was causing the W3WP.exe process to run at 100% CPU. I had hoped that there was some clever way of identifying the site from the process id but no such luck. The issue was escalated because we have multiple sites under each application pool.It was done like this to keep the overheads minimal (each W3WP.exe process needs circa 25MB to run) but it makes identifying rogue code difficult.

If you need to identify which W3WP.exe relates to which Application Pool, open CMD, navigate to your System32 directory and type:

cscript iisapp.vbs

That'll then list the relevant W3WP.exe processes, process id and their app pool name.:) -simple and useful, just the way I like it!

Identify which application pool is associated with which W3WP.exe process

Useful Links:            Comments [1] 

Tags: Server Maintenance | Server Management

 Saturday, October 13, 2007

Why should we use you?

While at the recent Startups Live event I got asked a question that I really should have been prepared for "Why should we use you?". At the time I was tired and hungry (no excuse I know) and so I was a little thrown.

I think it's important to look at networking as a form of job interview but without the job at the end of it. What I mean by this is you should have a set of questions, answers and interesting topics to discuss¹ prepared before you go into the event.

¹ Make sure you know what you're talking about though -you never know, they may know you're bull-shitting which isn't a good start to an ongoing relationship!

I've steered clear of a fair few networking events in the past on the basis that they're often pissing contests but networking itself is an important part of any business and so shouldn't be avoided. So how should you answer "Why should we use you?". This is a silly question in my eyes because as the purchaser you have the power, you should already have a list of criteria on what you're looking for from a supplier. I can understand if you're looking to find out whether my list matches yours but you're most likely going to get the same responses:

• "We're the best" -you're really going to take your word for it?
• "Just because" -they clearly don't care about their company, do you really want to do business with them?
• "We've got a proven track record" -fair play, good response, now you've got to do your research

Either way, whatever response you get it's most likely going to be a conversation killer and so, not something you want to ask while networking, if you want to ask this, I would keep it for an initial meeting.

So how did I respond? "That's a good question" -not a good response by any means but Stacey has come up with a superb answer in my view, put the ball back into their court and respond with

Why do I like this response? Well because it's honest and gives the client control, you could baffle them with sales talk till the cows come home but if they don't like you or get on with you then doing business isn't going to be fun (and business should be fun!). Get rid of the question and move onto something more interesting, save the grilling for the initial meeting!

Why should we use you?

Useful Links:            Comments [0] 

Tags: Business | Business Start-up Advice | The Site Doctor

 Thursday, October 11, 2007

Tim Smit and Startups Live visit Bristol

Seeing as I don't seem to have time to post my long, beautifully formatted posts at the moment -and that I don't think people really care whether they're beautifully formatted or not- I'm just throw this one on...

Last night I went to the first in a new round of Startup Live events. I've come across them in the past but never paid much attention to them as I thought it would be another '99 venture capitalist haunt and I wasn't really interested in wasting my time with it. The event however was better than I was expecting. Well, it was and it wasn't.

Sadly we got there a little late (what's new!) and missed the start of Tim Smit's talk however I really have to complement him on his talk, it was absolutely brilliant. It was probably one of the best -if not THE best- and most inspirational talks I've heard in a long time.

For those of you who aren't aware who Tim Smit is, apart from having a great name and having been involved in the Lost Gardens of Heligan he's the founder of the Eden project. Tim Smit is clearly very passionate about the work that he's involved in which is really conveyed to the audience during his talk and I really do recommend you go and see him if you have a chance as you won't regret it.

I think one of the most amusing things about the night was the speaker from Natwest who was clearly there to show how friendly and accommodating Natwest are but ended up demonstrating how far out of touch he is with their actual processes which was a shame as he really could have pulled the audience in and had them all signing up there and then.

Other than Tim Smit however the majority of the event was pretty much as I expected which I was a little disappointed about but I guess that's the way it goes. At the end of the day, if you can come away with one small nugget of information/inspiration the event was worth it. Luckily last night Tim Smit was able to produce the goods ;)

Tim Smit and Startups Live visit Bristol

Useful Links:            Comments [0] 

Tags: Business | Business Start-up Advice | The Site Doctor

 Friday, September 21, 2007

What have I been up to?

It's been rather quiet on my blog recently, if you're wondering why (and don't chat to me on/off-line) I thought I would share with you what we've been working on recently.

For the past month or so The Site Doctor has been developing a new web site (www.wineandhampergifts.co.uk) for Porter and Woodman Gifts Ltd - a local company that produces personalised corporate hampers and gifts. It's been quite a challenge as they have a rather unusual ordering system that allows multiple recipients/addresses multiple items. Looking at it now, it's not so complicated but the delivery charge calculations and initial specs took a while to fully grasp. It's been really enjoyable.

I'll probably cover aspects of the site over the forthcoming months but there are a few really nice features to the Wine and Hamper Gifts site (or at least I think so), some of which the end user will never know about such as the use of generics to calculate the address/recipient/gift variations) and those that they may -for instance the use of the JavaScript¹ Zoom function on the product details page (courtesy of LuckyZoom), also the design created by our excellent designer Gareth Brown all adds up to what has to be one of the best sites I've developed to date.

¹ Yes, I did just say I've integrated some JavaScript into the site ;)

I doubt most of my readers are interested on the in's and out's of the project itself but from an SEO perspective, I for one am expecting pretty decent results. We opted to use the URL Rewriting ISAPI from Helicon this time round over our usual IISMods URL Rewriting ISAPI as for some reason the IISMods site has been offline for a while (and checking now has been converted into a very weird site).

Another aspect that some people may be unaware of is that the majority of the Wine and Hamper Gifts site operates the same without JavaScript as it does with JavaScript, this is important not only for screen readers but also search engines. There is only one area of the Wine and Hamper Gifts site that I'm aware of that doesn't operate without JavaScript and that is the "Personalise this gift" link on the cart page that allows the user to either edit the existing message or add one that doesn't already exist, that's because it uses a LinkButton, but I may find a way around that later.

Other features that I really like are little things like the way the drop down lists on the left hand menu are created -they're not actually drop down lists but unordered lists that are then manipulated using JavaScript, I think the JavaScript could do with a little tweaking but the result is superb. The Wine and Hamper Gifts site also creates a PDF receipt for the user which is emailed to them, this is something I've been meaning to look into for some time but haven't had the chance, luckily while I was developing the site, Sean Ronan posted to the MsWebDev list about an ASP.Net PDF library iTextSharp (a port from a Java library) which, despite a few oddities from the POV of the Java port does exactly what I wanted. The library is pretty easy to use once you get your head around it and certainly produces some nice results.

There's still more work that's needed to finalise the content and various aspects of the Wine and Hamper Gifts website but if you have a chance, check out the new Porter and Woodman Gifts Ltd Wine and Hamper Gifts website and leave a comment here letting me know what you think :D

Oh, and they've given us a pretty high target to get before Christmas so if you're thinking about treating your customers to a personalised corporate hamper or gift give a little thought to using www.wineandhampergifts.co.uk

What have I been up to?

Useful Links:            Comments [0] 

Tags: AJAX | ASP.Net | C# | CSS | Design | SEO | The Site Doctor | Web Development

 Tuesday, September 11, 2007

Wording a letter/email to get something

When running any business, there will inevitably be a time when you need to ask a client for permission for something, whether it's to use their name as a reference or to bill them for a service. These letters are always tricky, get it wrong and your client will be able to avoid taking action on your request, get it right and you'll be able to reap the rewards of success (or so I'm told!).

I had one such occasion recently and thought it may be of use to share my experience/findings with others. Take a look at these two emails (semi-fake), one got what it was after, the other not. Once you've read them, I'll explain why and how you should word something so you can get what you want (which is most likely money!).

Example Email 1

Example Email 2

It should be fairly obvious which one got what it was after but incase it isn't, the second email got what it was after (an SLA of 10 hours a month) but why?

Lets look at the two emails in more detail:

Does the reader really care what you feel? Unlikely. Do they care about avoiding unexpected invoices? Most likely yes but would they be worse off paying the odd (semi) unexpected email? Probably not as if you're an ethical company you'd keep them up to date with their time usage at any time...

This first call-to-action required the reader's input for something, if (and this is most likely the case) your reader is either a business owner or executive, their motive to respond is almost nill as it's unlikely they're going to want to respond to your request just so they can give you money.

You may also notice that there's no penalty to this first request, it's open ended i.e. if the reader doesn't do anything, he's no worse off -in fact, he's actually better off!

Now lets look at the second email:

Although similar in wording and still expressing what you would like to happen, you're not over complicating the issue and remaining factual.

Notice that this time, the call-to-action is reversed, instead of asking for action to do something, you're asking the user to action something if he doesn't want it to happen? This may only read like a small difference, but it's a massive difference from your POV.

The second thing to notice is the deadline (or penalty) -this time, if the reader doesn't respond by the given deadline, the action will go on regardless of whether they have given their input.

 So there you have it, two ways of writing what looks like the same email but with two very different results, just remember, next time you want something, tell your client it'll happen if they don't do anything -I'm sure you'll get more success!

Wording a letter/email to get something

Useful Links:            Comments [0] 

Tags: Business | Business Start-up Advice | The Site Doctor

 Friday, August 24, 2007

The Controls collection cannot be modified because the control contains code blocks

Another day, another issue ;)

This had me going around in circles for a while until I realised what it was, if you're getting this error you can bet your bottom dollar that you have <%= %> somewhere in your page's header -furthermore I'd hazard a guess that you've got it in some JavaScript to reference an ASP.Net control on the page- and then you're trying to add a control to the header programmatically (or a custom control from someone like Telerik is trying to). Am I right¹?

¹ I'm not allowed to ask you to so I won't, but if I was right, then spend that bottom dollar clicking on one of the Google Ads :P

I can't tell you exactly why this occurs but my understanding of it is that ASP.Net can't re-create the header if it has Response.Write somewhere in the header (<%=) -most likely due to when the header is created it's not available (will look into it). No doubt you want to know the fix?

The Fix
The fix is simple, remove the inline code blocks and JavaScript and move it to your code behind i.e.:

string _manageSearch = String.Format(@'...'"lblFindAGift"");

string _manageSearch = String.Format( @" 
        function ManageSearch(){{
                var lbl = document.getElementById(""lblFindAGift"");
                var txt = document.getElementById(""{0}"");
                var btn = document.getElementById(""{1}"");

                .Do Something with it..

        }}",
        txtSearch.ClientID);

this.Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "ManageSearch", _manageSearch, true);

Remember: You need to escape the curly brackets otherwise you will get a "String.Format- Exception of type System.Web.HttpUnhandledException was thrown"

Update: Thanks to Julian Voelcker for sending me this alternative "fix" for the problem, can't say I like it though ;) basically instead of using <%= ... %> you would write the databinding expression of: <%# ... %>

The Controls collection cannot be modified because the control contains code blocks

Useful Links:            Comments [4] 

Tags: ASP.Net | C# | Web Development

Understanding email server connection checks

One of the reasons I'm fanatical about Rackspace as a hosting partner is that if you're unsure about something, you know you're able to ask an expert and get a top-notch response to your quandary.

I was recently speaking with another host who was talking about greylisting their emails -in short this is the process of rejecting the first email from a given email address/server and waiting for it to be (automatically) resent by the server later as unlike genuine email servers, most spam servers do not try to re-send an email if it's rejected by a server. We're not able to greylist our emails so I thought I would check that our spam filter settings were up-to-date.

The guys at Rackspace had a look through our spam filter settings and recommended we disabled the statistical filters as they were somewhat outdated technically and increase our connection checks -more importantly, deleting the email after it fails a number of checks. Historically I've been adverse to deleting emails on the server as there's no way to recover them so I asked how accurate connection checks were and thought I would share their easy-to-understand response about what the connection checks do.

Understanding email server connection checks

Useful Links:            Comments [0] 

Tags: Business | Server Management | The Site Doctor