Random String

Validated against:

  • SQL Server 2005.
CREATE VIEW [RandomCharacter]
AS
SELECT SUBSTRING
('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
,CAST(RAND()*62 AS INT)+1
,1
) AS [Character];
GO
CREATE FUNCTION [RandomString]
(@Length SMALLINT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @return VARCHAR(8000);
SET @return='';
WHILE LEN(@return)<>@Length
SELECT  @return=@return+[Character] FROM [RandomCharacter];
RETURN @return;
END;
GO
SELECT [dbo].[RandomString](10);
Posted in SQL 2005 | Tagged | Comments Off on Random String

Random Character

Validated against:

  • SQL Server 2005.
CREATE VIEW [RandomCharacter]
AS
SELECT SUBSTRING
		('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
		,CAST(RAND()*62 AS INT)+1
		,1
		) AS [Character];
GO
SELECT [Character]
FROM    RandomCharacter;
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 was produced “[Flat File Source [1]] Warning: The end of the data file was reached while reading header rows. Make sure the header row delimiter and the number of header rows to skip are correct.”;
• The control flow tasks “delete previous” and “insert” were successful, however both these tasks did NOT actually do anything;
• The “archive” task did move the file!.

This is of course not what we want. We require the package to fail if the header lines can’t be found. To get the SSIS package to error do the following:
• Set the package “maximumerrorcount” to 1 (for some reason this was zero which I think means allow unlimited errors)
• With the package “OnWarning” event hander done the following:
o removed our custom logging;
o Create a script task called “Warning The end of the data file was reached while reading header rows”
o Set the property ReadOnlyVariables to “System::ErrorCode”
o Set the script to:
Public Sub Main()
If CDbl(Dts.Variables(“System::ErrorCode”).Value.ToString) = Microsoft.SqlServer.Dts.Runtime.HResults.DTS_W_ENDOFFILEREACHWHILEREADINGHEADERROWS Then
Throw New Exception(“The end of the data file was reached while reading header rows. Make sure the header row delimiter and the number of header rows to skip are correct.”)
End If
Dts.TaskResult = Dts.Results.Success
End Sub

The latter changes results with the package failing if the header lines can not be read. Which is what we want.

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.

IF OBJECT_ID('tempdb..#AnyTable') IS NOT NULL 
PRINT 'EXISTS'
CREATE TABLE #names 
(forename varchar(15),
 surname varchar(15),
 age int DEFAULT 0 NULL)
 
INSERT #names VALUES ('David','Selwood',40)
INSERT #names VALUES ('David','Selwood',40)
INSERT #names VALUES ('Paul','Selwood',40)

CREATE TABLE #names2
(forename varchar(15),
 surname varchar(15),
 age int DEFAULT 0)

CREATE UNIQUE INDEX removedups ON #names2 (forename,surname,age) WITH IGNORE_DUP_KEY

INSERT #names2
SELECT * FROM #names

SELECT * FROM #names2

forename surname age
————— ————— ———–
David Selwood 40
Paul Selwood 40

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

Cross Tabs

USE pubs
GO
IF OBJECT_ID('author_crosstab2') IS NOT NULL
DROP PROC author_crosstab2
GO
CREATE PROCEDURE author_crosstab2
AS
SET NOCOUNT ON
DECLARE @execsql nvarchar(4000), @AuthorName varchar(80)

CREATE TABLE #autxtab (Title varchar(80))

DECLARE AlterScript CURSOR FOR
SELECT 'ALTER TABLE #autxtab ADD ['+au_fname+' '+au_lname+'] char(1) NULL DEFAULT '''''
FROM authors
FOR READ ONLY

OPEN AlterScript
FETCH AlterScript INTO @execsql
WHILE (@@FETCH_STATUS=0) BEGIN
EXEC sp_executesql @execsql
FETCH AlterScript INTO @execsql
END
CLOSE AlterScript
DEALLOCATE AlterScript

DECLARE InsertScript CURSOR FOR
SELECT execsql='INSERT #autxtab (Title,'+'['+a.au_fname+' '+a.au_lname+']) VALUES ('''+REPLACE(t.title,'''','')+''', ''X'')'
FROM titles t JOIN titleauthor ta ON (t.title_id=ta.title_id)
JOIN authors a ON (ta.au_id=a.au_id)
ORDER BY t.title

OPEN InsertScript
FETCH InsertScript INTO @execsql
WHILE (@@FETCH_STATUS=0) BEGIN
EXEC sp_executesql @execsql
FETCH InsertScript INTO @execsql
END
CLOSE InsertScript
DEALLOCATE InsertScript

SELECT * FROM #autxtab
DROP TABLE #autxtab
GO

EXEC author_crosstab2
GO
Posted in Uncategorized | Tagged | Comments Off on Cross Tabs

Rounding a Date (removing time)

SELECT CAST(CONVERT(char(8),getdate(),112)AS datetime)
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 greater breadth of flexibility when converting between date and time values, fractional numbers and monetary signifiers.

CAST is the more ANSI-standard of the two functions, meaning that while it’s more portable (i.e., a function that uses CAST can be used in other database applications more or less as-is), it’s also less powerful. CAST is also required when converting between decimal and numeric values to preserve the number of decimal places in the original expression. For those reasons, it’s best to use CAST first, unless there is some specific thing that only CONVERT can provide in the work you’re doing.

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:

PRINT 'This message does not display immediately'
WAITFOR DELAY '00:00:05'
RAISERROR ('But this one does', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:05'
PRINT 'It''s over now'
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 depends on the parameters input.

Parameters:
– @CharacterExpression = String expression containing substrings and delimiters. (i.e. ‘DOWNLOAD#MSDE#ADMIN’).
– @Delimiter = Single delimiter character (i.e. ‘#’).
– @Position = Integer with a value of 1 or above. Represents the position of the substring to be returned.

Notes:
If @Position is zero, negative, or greater than the number of delimiters in @CharacterExpression, PIECE returns a null string.

I have written the PIECE SQL routine as a user-defined function, or to be more precise a User-Defined Scalar Function. An example of calling a user-defined scalar function is “SELECT .dbo.Piece(‘DOWNLOAD#MSDE#ADMIN’,’#’,2)”. Since user-defined functions were introduced in Microsoft SQL Server 2000 you shall require this version of SQL Server or higher in order to use this functionality. Should you have Microsoft SQL Server 7 or lower I would recommend that you convert the function to a stored procedure. The latter shouldn’t be that difficult since the routine is no more than 10 lines long.

Examples of Use:

SELECT .dbo.PIECE('DOWNLOAD','#',0) returns NULL'.
SELECT .dbo.PIECE('DOWNLOAD','#',1) returns 'DOWNLOAD'.
SELECT .dbo.PIECE('DOWNLOAD#','#',1) returns 'DOWNLOAD'.

SELECT .dbo.PIECE('DOWNLOAD#MSDE#ADMIN','#',0) returns NULL'.
SELECT .dbo.PIECE('DOWNLOAD#MSDE#ADMIN','#',1) returns 'DOWNLOAD'.
SELECT .dbo.PIECE('DOWNLOAD#MSDE#ADMIN','#',2) returns 'MSDE'.
SELECT .dbo.PIECE('DOWNLOAD#MSDE#ADMIN','#',3) returns 'ADMIN'.
SELECT .dbo.PIECE('DOWNLOAD#MSDE#ADMIN','#',4) returns NULL.

SELECT .dbo.PIECE('DOWNLOAD#MSDE#ADMIN#','#',4) returns ''.

SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN','#',0) returns NULL.
SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN','#',1) returns ''.
SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN','#',2) returns ''.
SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN','#',3) returns 'DOWNLOAD'.
SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN','#',4) returns 'MSDE'.
SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN','#',5) returns 'ADMIN'.
SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN','#',6) returns NULL.

SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN#','#',6) returns ''.

Anyway, here’s the code:

CREATE FUNCTION Piece(@CharacterExpression VARCHAR(999),@Delimiter CHAR(1),@Position INTEGER)
RETURNS VARCHAR(999)--Returns the specified substring from the specified Character Expression within the bounds of a input delimiter.
AS
--Function to Return a Sub String from a Delimited string.
-- PIECE returns the specified substring from the specified Character Expression within the bounds of an input delimiter. The substring returned depends on the parameters input.
-- 
-- Parameters:
-- - @CharacterExpression = String expression containing substrings and delimiters. (i.e. 'DOWNLOAD#MSDE#ADMIN').
-- - @Delimiter = Single delimiter character (i.e. '#').
-- - @Position = Integer with a value of 1 or above. Represents the position of the substring to be returned.
-- 
-- Notes:
-- If @Position is zero, negative, or greater than the number of delimiters in @CharacterExpression, PIECE returns a null string.
-- Example of use: "SELECT .dbo.Piece('DOWNLOAD#MSDE#ADMIN','#',2)" would return 'ADMIN'.
BEGIN
--@Position should be 1 or more.
IF @Position<1 RETURN NULL
IF LEN(@Delimiter)<>1 RETURN NULL--Perhaps its better to add a raise error here !!!
--Work out start position of the substring.
DECLARE @Start INTEGER
SET @Start=1
WHILE @Position>1
BEGIN
SET @Start=ISNULL(CHARINDEX(@Delimiter,@CharacterExpression,@Start),0)
IF @Start=0 RETURN NULL
SET @Position=@Position-1
SET @Start=@Start+1
END
--Work out end position of the substring.
DECLARE @End INTEGER
SET @End=ISNULL(CHARINDEX(@Delimiter,@CharacterExpression,@Start),0)
IF @End=0 SET @End=LEN(@CharacterExpression)+1--If no end delimiter then that's OK. Were just have all remaining characters.
RETURN SUBSTRING(@CharacterExpression,@Start,@End-@Start)
END
GO
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 check using a SQL Server 2000 User Defined Scalar Function. It also shows how to call the user defined function.

CREATE FUNCTION ColumnAlreadyExists(@TableName NVARCHAR(128),@ColumnName NVARCHAR(128))
RETURNS INTEGER--Returns 0 if column does not exist. Returns 1 if column exists.
AS
BEGIN
--See if the Table already contains the column.
IF EXISTS
(SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
WHERE ObjectProperty(O.ID,'IsUserTable')=1
AND O.Name=@TableName
AND C.Name=@ColumnName)
RETURN 1
--Table does not contain the column.
RETURN 0
END
GO

--Example of using SQL Server 2000 User Defined Scalar Function
--Drop then Add BT_FORMATTED column on the Temp_Hotspot table.
IF .dbo.ColumnAlreadyExists('Temp_Hotspot','BT_FORMATTED')=1
ALTER TABLE Temp_Hotspot DROP COLUMN BT_FORMATTED
ALTER TABLE Temp_Hotspot ADD BT_FORMATTED VARCHAR(500)
Posted in SQL 2005 | Tagged | Comments Off on Checking if a Table Column already Exists in a SQL Table