Wednesday, January 28, 2009

Setup 301 Redirect handler for all domains on an IIS Server

There are lots of different ways out there to redirect non-www domains to their correct www.domain.com equivalent.

For SEO purposes this is of the utmost importance because without redirects Google (and other search engines) assume data is being duplicated on two domains, and thus importance (and hence ranking) is distributed between them.

I wanted to create a way to make one single fix so that all domains setup on the server with the "www." only and not the non-www domain, would have their non-www equivilent redirected with a recommended 301 redirect.

This would be done with the Default Website in IIS, making use of UrlRewriter.net and a redirect.ashx handler. Setting up IIS to handle all extensions using these instructions we ensured that all requests are handled in the following way in the web.config:
<rewrite to="~/redirect.ashx?$1" url="^/(.*)$">
Here's the snippet from the handler itself:
public void ProcessRequest (HttpContext context) {
   string url = context.Request.ServerVariables["HTTP_HOST"];

   context.Response.Clear();

    if (!url.Contains("www."))    {
        url = "http://www." + url + "/" + context.Server.UrlDecode(context.Request.QueryString.ToString());

        context.Response.Status = "301 Moved Permanently"; 
        context.Response.AddHeader("Location", url);
    }
    else
    {
        context.Response.Status = "404 Not Found";
    }

    context.Response.End();

}

Now all the host headers without a binding to a website in IIS will find it's way here if the DNS is setup similarly, so domain.com (and any requests within) redirects successfully to www.domain.com.

Google is happy. So I am happy.

Compiling ASP.Net 1.1 Projects without VS 2003

So we've moved on from many of our early projects, some we have not converted to 2.0 because very little maintenance is ever needed. The problem is that we still need to recompile these projects on minor updates. Here's how, using CSC.exe, inside of a bat file in the parent folder, assuming that the project exists in domain.com sub folder.

"C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\csc.exe" /t:library /debug /recurse:domain.com\*.cs /out:domain.com\bin\domain.com.dll /r:domain.com\bin\a_ref_library.dll /r:domain.com\bin\another_ref_library.dll
pause

Friday, January 23, 2009

Find String in Stored Procedures/Triggers/Jobs

The following SQL samples are very useful when searching your Database for a certain string. For example if you change a column name you may want to search Tables, Views, Procedures and Triggers throughout the Database for any references to the old column name.

1. Use the following SQL when searching for *Tables which contain the old column name:
use dbname
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE CHARINDEX('OldColumnName', COLUMN_NAME) > 0
*Note: The above example also returns Views which reference the old column.

2. Use the following SQL when searching for Stored Procedures which contain the old column name:
use dbname
SELECT ROUTINE_NAME, OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE CHARINDEX('OldColumnName', OBJECT_DEFINITION(OBJECT_ID(ROUTINE_NAME))) > 0
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME

3. Use the following SQL when searching for Triggers which contain the old column name:
use dbname
SELECT DISTINCT sysobjects.name AS [Object Name],syscomments.text
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type = 'TR'
AND sysobjects.category = 0
AND CHARINDEX('OldColumnName',syscomments.text) > 0

4. Use the following SQL when searching for Jobs which contain a certain procedure name:
SELECT DISTINCT msdb.dbo.sysjobs.name AS [Object Name], msdb.dbo.sysjobsteps.command
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobsteps ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobsteps.job_id
WHERE msdb.dbo.sysjobsteps.command LIKE '%ProcName%'

Monday, January 5, 2009

Creating refCount for new Databases

Found I was tired copying table structure and then content for refCount to new databases. Below is a simple piece of SQL to create a large refCount quickly and easily.

USE [dbname]
GO

/****** Object: Table [dbo].[refCount] Script Date: 01/05/2009 12:25:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[refCount](
[Count] [int] NOT NULL,
CONSTRAINT [PK_refCount] PRIMARY KEY CLUSTERED
(
[Count] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--0 entered
INSERT INTO refCount ([Count])
SELECT 0

--1 entered
INSERT INTO refCount ([Count])
SELECT 1

DECLARE @MaxCount int
SELECT @MaxCount = MAX([Count]) FROM refCount
--3 entered
INSERT INTO refCount ([Count])
SELECT ([Count] + @MaxCount + 1) FROM refCount

DECLARE @Y int
--IMPORTANT!!
--Change this to suit your requirements where MaxCount will be [2 to the power of (Y + 2)]
SET @Y = 10 --MaxCount of your refCount after running will be 4096 ie [2 to the power of (10 + 2)]

DECLARE @Count int
DECLARE c1 CURSOR FOR SELECT * FROM refCount WHERE [Count] < @Y
OPEN c1
FETCH NEXT FROM c1 INTO @Count

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @MaxCount = MAX([Count]) FROM refCount
--entered
INSERT INTO refCount ([Count])
SELECT ([Count] + @MaxCount + 1) FROM refCount

FETCH NEXT FROM c1 INTO @Count
END

CLOSE c1
DEALLOCATE c1 --End Vendor Due