Category Archives: Database

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

Writing to Word from SQL Server

Never a man to walk away from a challenge, Phil Factor set himself the task of automating the production of Word reports from SQL Server, armed only with OLE automation and a couple of stored procedures….

3 Writing to Word from SQL ServerHaving helped Robyn with her Excel Workbench, I couldn’t get out of my head the idea of achieving the same effect with MS Word. After all, from the data viewpoint, MS Word documents are just a series of paragraphs and tables aren’t they? Surely, it should be easy to read and write data between SQL Server and Word.
view original

Speed Optimization in ASP.NET 2.0 Web Applications

Developing a web application that responds to users requests quickly is a target and a challenge for all web developers. ASP.NET was designed with speed optimization and performance improvements in mind. The set of enhancements like: Pages compilation, and Automatic storage on the server are new to ASP.NET. However, these enhancements are not enough to protect you from slow performance when a large number of http requests are simultaneously processed by your web application.

To write a well performing web application you must make a balance between four common performance measures.
view original

Ten Common Database Design Mistakes

No list of mistakes is ever going to be exhaustive. People (myself included) do a lot of really stupid things, at times, in the name of “getting it done.” This list simply reflects the database design mistakes that are currently on my mind, or in some cases, constantly on my mind.
view original