List of Database Engine Error Messages

List of Database Engine Error Messages: "

All system and user-defined error messages in an instance of the Database Engine are contained in the sys.messages catalog view.

Here’s how to query this catalog view to list the Error Messages based on their severity

SELECT * FROM sys.messages 
WHERE language_id = 1033
ORDER BY severity desc

OUTPUT

The error message describes the cause of the error whereas the severity indicates how serious the error is.You can read more on the different Level of Severities over here Database Engine Error Severities

"

Auto Generate AlphaNumeric ID’s in a SQL Server Table

Auto Generate AlphaNumeric ID’s in a SQL Server Table: "

I was recently working on a query to auto generate a Column ID based on an IDENTITY column. I used computed columns to achieve this. If you too have a similar requirement, then here’s the query.

DECLARE @TT TABLE (
CandID as 'C-' + RIGHT('000' + CONVERT(varchar, CourseID),5),
CourseID int IDENTITY(1,1),
ReferrerName varchar(10)
)

INSERT INTO @TT VALUES ('Dhiraj')
INSERT INTO @TT VALUES ('Jack')
INSERT INTO @TT VALUES ('Kelly')
INSERT INTO @TT VALUES ('Kelly')
INSERT INTO @TT VALUES ('Jack')
INSERT INTO @TT VALUES ('Dhiraj')

SELECT * FROM @TT

OUTPUT

Observe how the values of the CandID column are autogenerated based on the values if the CourseID column.

Group and Count Records in SQL Server

Group and Count Records in SQL Server: From SQL Blog

I had recently written a query to Select the Latest Record based on a Condition in SQL Server. A SQLServerCurry.com reader Mazz wrote back asking how to use the same query to count how many times a student took an exam for each course. Here’s the modified query that works on SQL Server 2005/2008.

SAMPLE DATA:

DECLARE @TT TABLE (CandID int, CourseID smallint)

INSERT INTO @TT VALUES (1, 101)
INSERT INTO @TT VALUES (3, 102)
INSERT INTO @TT VALUES (3, 110)
INSERT INTO @TT VALUES (3, 102)
INSERT INTO @TT VALUES (1, 101)
INSERT INTO @TT VALUES (2, 101)
INSERT INTO @TT VALUES (1, 110)
INSERT INTO @TT VALUES (1, 109)
INSERT INTO @TT VALUES (2, 102)
INSERT INTO @TT VALUES (1, 110)
INSERT INTO @TT VALUES (1, 101)
INSERT INTO @TT VALUES (3, 110)
INSERT INTO @TT VALUES (2, 102)
INSERT INTO @TT VALUES (2, 102)

QUERY

;WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY CANDID, CourseID ORDER BY CandID) as ord,
CandID, CourseID
FROM @TT
)
SELECT CandID, CourseID, Count(CourseID) as TotalAttempts
FROM CTE
GROUP BY CandID, CourseID
ORDER BY CandID

OUTPUT

SQL Query to find out who attended office on Saturday

SQL Query to find out who attended office on Saturday:

A client of mine had a report requirement to find out the employees who attended office on Saturday in the last 30 days and their Check-In Check-Out times.

Here is some sample data that does that. For convenience, I have not added the CheckOut time, but hopefully you will get the idea

SAMPLE Data

DECLARE @TT TABLE (EID int, CheckIn DATETIME)
INSERT INTO @TT VALUES (2, 'April 02, 2010 11:25am')
INSERT INTO @TT VALUES (4, 'April 03, 2010 9:55am')
INSERT INTO @TT VALUES (5, 'April 07, 2010 11:24am')
INSERT INTO @TT VALUES (2, 'April 10, 2010 11:22am')
INSERT INTO @TT VALUES (3, 'April 15, 2010 5:27am')
INSERT INTO @TT VALUES (6, 'April 16, 2010 8:21am')
INSERT INTO @TT VALUES (7, 'April 17, 2010 11:55am')
INSERT INTO @TT VALUES (3, 'April 22, 2010 10:16am')
INSERT INTO @TT VALUES (4, 'April 24, 2010 11:35am')
INSERT INTO @TT VALUES (7, 'April 30, 2010 9:49pm')
INSERT INTO @TT VALUES (2, 'May 01, 2010 9:49am')
INSERT INTO @TT VALUES (5, 'May 02, 2010 10:43am')
INSERT INTO @TT VALUES (3, 'May 03, 2010 11:29am')

QUERY

SELECT * FROM @TT
WHERE DATENAME(weekday, CheckIn) = 'SATURDAY'
AND DATEDIFF(DD, CheckIn, GETDATE()) < 30

OUTPUT

Hopefully these people get the bonus they deserve!