- Part 2

Posted on April 17, 2012April 17, 2012 by admin

SQL Server 2012 Cheatsheet

Pinal Dave has released his latest cheatsheet regarding the latest SQL Server 2012. Whats in there: SSMS Shortcuts Columnstore Index SQL Server 2012 Datetime Functions SQL Server Ranking Functions SQL Server 2012 Analytic functions Download from here.

Posted on December 15, 2011December 15, 2011 by admin

Validate asp.net multiline TextBox

Because it is rendered to TextArea the MaxLength property does not work. One way around this is to attach a regular expression validator to that control. What is important is to set the ValidationExpression to something like “^[\s\S]{0,255}$“. Where 255 is the maximum length allowed. Hope this helps 🙂

Posted on September 23, 2011September 23, 2011 by admin

What is coming with VS2011 and Framework 4.5

Read here from Scott Hanselman’s post. Download Links You likely only need this one download: Visual Studio 11 Developer Preview Visual Studio Team Foundation Server 11 Developer Preview Visual Studio Agents 11 Developer Preview Visual Studio 11 Developer Preview Remote Debugger .NET Framework 4.5 Developer Preview

Posted on September 5, 2011September 5, 2011 by admin

Readmission to hospital within 28 days of discharge

This is very common question when we deal with patients in NHS data. Here is how the task is defined – Readmission rates and HES. In short we need to know how many times(or when) a patient was readmitted within 28 days of his last hospital discharge. Here is how I do that in SQL
Continue reading Readmission to hospital within 28 days of discharge

Posted on June 7, 2011June 7, 2011 by admin

Clean whole database from unwanted string

Often we end up having an unwanted string injected into our database. It might be a virus or any text that you don’t need. This stored procedure is doing the hard work to remove all the unwanted instances.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[UpdateAllTables]
(
	@SearchStr nvarchar(100)
)
AS
BEGIN

	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
	SET @SearchStr = QUOTENAME('' + @SearchStr + '','''')

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				EXEC
				(
					'UPDATE ' + @TableName + ' SET ' + @ColumnName + '=REPLACE(' + @ColumnName + ','+ @SearchStr + ','''') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END	
	END

END

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROC [dbo].[UpdateAllTables]

(

@SearchStr nvarchar(100)

)

BEGIN

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

SET @SearchStr = QUOTENAME('' + @SearchStr + '','''')

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ''

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

), 'IsMSShipped'

) = 0

)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)

AND TABLE_NAME = PARSENAME(@TableName, 1)

AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

AND QUOTENAME(COLUMN_NAME) > @ColumnName

)

IF @ColumnName IS NOT NULL

BEGIN

EXEC

(

'UPDATE ' + @TableName + ' SET ' + @ColumnName + '=REPLACE(' + @ColumnName + ','+ @SearchStr + ','''') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

)

END

Posted on June 1, 2011 by admin

Thursdays links part 2

What is Node.js? Let’s start with V8. V8 is the JavaScript implementation used within Chrome. It utilises “Just In Time” compilation to achieve performance that was previously unattainable in JavaScript. In fact, these improvements lift V8 JavaScript into the same realms of performance as Clojure, Java or Go. Should I use HTML5 or Silverlight? I
Continue reading Thursdays links part 2

Posted on February 10, 2011 by admin

Thursdays links

Countdown to Ext JS 4: Drawing and Charting Build a Data-Driven Enterprise Web Site in 5 Minutes Understanding the Dynamic Keyword in C# 4

Posted on December 16, 2010 by admin

Programming Windows Phone 7 Free eBook

This book is from the famous Charles Petzold. Here is the introduction: This book is a gift from the Windows Phone 7 team at Microsoft to the programming community, and I am proud to have been a part of it. Within the pages that follow, I show you the basics of writing applications for Windows
Continue reading Programming Windows Phone 7 Free eBook