Add new columns to a table

-- add 2 columns
ALTER TABLE Users
ADD MinAmount decimal(18,2) NULL,
MaxAmount decimal(18,2) NULL
GO

related:
http://fcmendoza02.blogspot.com/2008/07/add-new-not-null-column.html

Remove column from table

-- Delete column
ALTER TABLE Users
DROP COLUMN Nickname
GO

Related:
http://fcmendoza02.blogspot.com/2008/11/remove-fk-from-table.html

Time Only

declare @date DateTime

set @date = GETDATE()

SELECT LEFT(CONVERT(VARCHAR(8), @date, 8), 5) AS TimeOnly

/*
TimeOnly
--------
15:04
*/

Grant Event Log permissions to an account

On Windows 2003 Server
go to HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\EventLog:



Then add permissions to a specific account.

related link: http://blogs.objectsharp.com/cs/blogs/bruce/archive/2003/11/03/180.aspx

aspnet_wp vs w3wp

from http://geekswithblogs.net/vivek/archive/2006/10/18/94436.aspx

The way an ASP.NET request is handled by IIS is quite different in IIS 6.0 when compared with 5.0. In 5.0, the ASP.NET worker process is handed off control by the aspnet_isapi extension in IIS. The aspnet_isapi dll runs in the inetinfo.exe process in IIS and functions what is known as the CLR host (a CLR host is the piece of unmanaged code which is responsible for loading the CLR into the memory). So aspnet_isapi “hands over” the processing to the worker process named aspnet_wp.exe, where the request passes through a series of HttpModules and an HttpHandler.

But in IIS 6.0, there is a driver named http.sys which listens to all the incoming requests (aspnet_isapi.dll is not in the picture at this point). The moment an ASP.NET specific request comes in, this driver starts an IIS 6.0 worker process (which is not related to ASP.NET at all) named w3wp.exe. This process now loads the aspnet_isapi.dll (CLR host) and the request follows through a similar sequence of HttpModules and HttpHandlers.

So the important thing to note here is that w3wp.exe is not an ASP.NET worker process (unlike aspnet_wp.exe) but instead specific to IIS 6.0.

Today

declare @today datetime
set @today = CAST(convert(varchar, GETDATE(), 101) as datetime)

SELECT @today AS Today

-- Result (year-month-day):
/*
Today
-----------------------
2009-01-08 00:00:00.000
*/

Create UNIQUE constraint

-- RecordNumber will now be a unique field

ALTER TABLE Clients
ADD CONSTRAINT UN_Clients_RecordNumber
UNIQUE (RecordNumber)

-- LocationID, PhoneNumber and StartDate will be unique per row

ALTER TABLE Clients
ADD CONSTRAINT UN_Clients
UNIQUE (LocationID, PhoneNumber, StartDate)

Mailbox unavailable. The server response was: 5.7.1 Unable to relay for ...

Mailbox unavailable. The server response was: 5.7.1 Unable to relay for ...





end.

Create an Index

CREATE INDEX [IX_Customers_StartDate] ON Customers (StartDate)