Monthly Archives: June 2011

Random String

Validated against: SQL Server 2005.

Posted in SQL 2005 | Tagged | Comments Off on Random String

Random Character

Validated against: SQL Server 2005.

Posted in SQL 2005 | Tagged | Comments Off on Random Character

SSIS Processes An Incorrect File with Success

Whilst loading data into production I made the mistake of giving SSIS the wrong text file for processing!. What happened was that the file was processed! Investigation into why resulted with the following finding: • On ‘PreExecute’ the following warning … Continue reading

Posted in SSIS 2005 | Tagged | Comments Off on SSIS Processes An Incorrect File with Success

Using INSERT to Remove Duplicate Rows (IGNORE_DUP_KEY)

Create a duplicate structure of the table concerned and build an IGNORE_DUP_KEY index over the second table that includes all the original table candidate keys and insert rows into it. forename surname age ————— ————— ———– David Selwood 40 Paul … Continue reading

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Using INSERT to Remove Duplicate Rows (IGNORE_DUP_KEY)

Cross Tabs

Posted in Uncategorized | Tagged | Comments Off on Cross Tabs

Rounding a Date (removing time)

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Rounding a Date (removing time)

Cast or Convert

Syntax for CAST: CAST ( expression AS data_type [ (length ) ]) Syntax for CONVERT: CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) CONVERT is specific to SQL Server, and allows for a … Continue reading

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Cast or Convert

Forcing the Screen Buffer to be Displayed

In order to force the contents of the screen buffer to be outputted staight away please use the following SQL:

Posted in SQL 2005, SQL 2008, SQL 2008 R2 | Tagged | Comments Off on Forcing the Screen Buffer to be Displayed

Function to Return a Sub String from a Delimited String

This SQL function is similar to the VB Split function and also similar to the Mumps (M) database $Piece function. PIECE returns the specified substring from the specified Character Expression within the bounds of an input delimiter. The substring returned … Continue reading

Posted in SQL 2005 | Tagged | Comments Off on Function to Return a Sub String from a Delimited String

Checking if a Table Column already Exists in a SQL Table

On odd occasions it is necessary to test a table to see if a column already exists. This is basically accomplished by checking the database system tables ‘SysObjects’ and ‘SysColumns’ for the relevant data. The example below shows such a … Continue reading

Posted in SQL 2005 | Tagged | Comments Off on Checking if a Table Column already Exists in a SQL Table