Jay Hollingum - Works in IT

One of the features of EF CodeFirst is the ability to automatically drop and recreate a database if the model changes or if the database does not exist, which is pretty useful when just doing development work. In a web app this is done in the Global.asax file within "protected void Application_Start()" as per this post from Scott Guthrie -  http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx . An example would be: protected void Application_Start() { Database.SetInitializer<yourdbcontex>(new DropCreateDatabaseIfModelChanges<yourdbcontext>()); AreaRegistration.RegisterAllAreas(); RegisterGlobalFilters(GlobalFilters.Filters); RegisterRoutes(RouteTable.Routes); } However when using the Database.SetInitializer and trying to rebuild the database, the following exception may be encountered, especially if using SQL Server Express: "This operation requires a connection to the 'master'...

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...