Jay Hollingum - Works in IT

Below is just a quick and simple function to convert a short string to proper case. It's not the most efficient but will do its job for short strings. The first character of the string is converted to upper case, subsequent characters that are preceded by a space are converted to upper case. e.g. "this IS A TEST sentence. followed BY ANOTHER." will be changed to "This Is A Test Sentence. Followed By Another." Function: CREATE FUNCTION [dbo].[fncProperCase](@string VarChar(1000)) RETURNS VarChar(1000) AS BEGIN DECLARE @pos int DECLARE @len int DECLARE @char VarChar(1) DECLARE @temp VarChar(1000) SET @string = LOWER(LTRIM(RTRIM(@string))) SET @temp = '' SET @pos = 1 SET @len = LEN(@string) WHILE @pos <= @len BEGIN SET @char = SUBSTRING(@string, @pos, 1) IF @pos = 1 BEGIN SET @temp = @temp + UPPER(@char) END ELSE BEGIN IF SUBSTRING(@string, @pos - 1, 1) = ' ' BEGIN SET ...

The following SQL function will remove and strip all non-alphanumeric characters from a string. CREATE FUNCTION [dbo].[fncRemoveNonAlphanumericChars](@Temp VarChar(1000)) RETURNS VarChar(1000) AS BEGIN WHILE PatIndex('%[^A-Za-z0-9]%', @Temp) > 0 SET @Temp = Stuff(@Temp, PatIndex('%[^A-Za-z0-9]%', @Temp), 1, '') RETURN @TEmp END Example: SELECT dbo.fncRemoveNonAlphanumericChars('abc...DEF,,,GHI(((123)))456jklmn') Result: abcDEFGHI123456jklmn

A bit of old school SQL Server 2000 stuff that I had to delve in to, to resuscitate a dying database using sp_who2. Running the command sp_who2 gives details of the current sessions, users and processes running in an instance of SQL Server. It's an undocumented version of the sp_who command ( http://msdn.microsoft.com/en-us/library/ms174313.aspx ) but sp_who2 gives a bit more info as to what certain processes are doing. From this, you can get a feel for what sessions and processes are hanging the database, or are being blocked, or just generally causing problems in the current running sessions of the instance of SQL Server. Once the SPID (Server Process ID) is known that is causing issues, this can be used to get the sql_handle from sysprocesses. The sql_handle is a binary key that references queries in the procedure cache. After getting the sql_handle, the function fn_get_sql can be used to reveal the SQL statement that seems to be causing issues. DECLARE @Handle varbinary...