SQL Server Problem #2: data dump 4

[vongrunt]

Awright. Puz... problem #2. Nothing with dates

Every once in a while someone asks about SQL data dumping tool. Not DTS or bcp out/in (...damnit). Something like mysqldump -d, capable of generating buncha INSERT statements for each table we specify. Such tool would be quite handy in some situations so...

Write stored procedure that dumps single table as SQL code.

Prototype and output

Let's do it minimalistic. Only one input argument:

adm_dumpTable_<yournicknamehere>( @table_name sysname )

... and one returned result set with two columns:

1. lineNo int
2. SQLCode nvarchar(4000)


Things to take care about

Generated SQL code must be perfectly valid - string data properly quoted, NULLs written as "NULL" etc.
Anything above that is up to you. Imagine all possible scenarios during which generated code may fail or INSERT different values...


Things to ignore

BLOB/CLOB columns - text, ntext, image.
varchar size limit. We don't have tables with 250+ columns, right?
Timestamp columns.
Views and system tables - dump should work only on user tables, for obvious reasons.


Proposed testing

Create blank database. Choose one table, for example table Northwind.dbrders. Generate it's script (with primary key, but no foreign keys!) and dump data from that table with stored procedure from above. Run both script and code in blank DB. Etc etc.


Rules and scoring

Unlike previous problem, this one is hard to measure with stopwatch or something. Speed is irrelevant within reasonable margins - any code that doesn't suffer from excessive cursoritis is good to go. Let's do scoring this way:

- for each feature implemented/supported: +3 points. Example: identity inserts
- for each bug found: -1 point. Example: script crashes on varchar(N) column containing "O'Neill"

I know this scoring schema isn't perfect... but will make things competitive.
Regarding restrictions, you may not use anything outside database or call extended stored procedures.
Same as before - don't show any code until next Friday (Jan 27th).

If something isn't clear - just ask.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

** bump **

This thread went silent... and there are still 24 hours left. Any success so far?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

nope, did not have really any time
I will start during lunch (in 10 minutes)
I have 1 question
do you want the output to be like this?

1, 'insert into table orders values(....)
2, 'insert into table orders values(....)
etc etc etc

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> do you want the output to be like this?

1, 'insert into table orders values(....)

Any format that copied&pasted into QA produces no syntax errors is OK for me. But for extra 3 points this is not enough

(Hint: I said BLOB/CLOB/timestamp columns can be ignored).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[pwilson]

In researching how to do it I found one that does most of what was asked. Didnt want to plagiarize or redo it.

 

[SQLDenis]

i am doing my own and must admit it is a BIG pain in the neck

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> In researching how to do it I found one that does most of what was asked. Didnt want to plagiarize or redo it.

Fair enough.

> i am doing my own and must admit it is a BIG pain in the neck

What is the biggest problem you encountered?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

>>What is the biggest problem you encountered?
looping thru the table without a cursor or dynamic SQL

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> looping thru the table without a cursor or dynamic SQL

FYI this can be done without a cursor, but not without dynamic SQL.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

I did the looping without dynamic SQL (sort of)
but am using dynamic SQL to generate insert scripts

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Yup. Sounds promising.

I finally made it without any looping (no WHILE, no cursors) and with single line of dynamic SQL.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

Here we go 2 procs

proc Number 1
create this first

create proc testInsertSQLMenace 
@table_name varchar(100) 
as


DECLARE				
	@chvTargetTable varchar(800), 	
	@include_column_list bit ,		
	@from varchar(800) , 		
	@include_timestamp bit , 		
	@debug_mode bit ,			
	@owner varchar(64) ,		
	@ommit_images bit ,			
	@ommit_identity bit ,		
	@top int ,			
	@cols_to_include varchar(8000) ,	
	@cols_to_exclude varchar(8000) ,	
	@disable_constraints bit ,		
	@ommit_computed_cols bit 
	,@Column_ID int, 		
		@Column_List varchar(8000), 
		@Column_Name varchar(128), 
		@Start_Insert varchar(786), 
		@Data_Type varchar(128), 
		@Actual_Values varchar(8000),	
		@IDN varchar(128)		

SET @IDN = ''
SET @Column_ID = 0
SET @Column_Name = ''
SET @Column_List = ''
SET @Actual_Values = ''

set @owner = 'dbo'

set @chvTargetTable =@table_name
	set @include_column_list = 0	
	set @from  = NULL		
	set @include_timestamp  = 0
	set @ommit_images  = 0			
	set @ommit_identity  = 0		
	set @top  = NULL			
	set @cols_to_include  = NULL	
	set @cols_to_exclude  = NULL	
	set @disable_constraints  = 0	
	set @ommit_computed_cols  = 0
begin




		SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@chvTargetTable,@table_name)) + ']' 		




SELECT	@Column_ID = MIN(ORDINAL_POSITION) 	
FROM	INFORMATION_SCHEMA.COLUMNS (NOLOCK) 
WHERE 	TABLE_NAME = @table_name AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)




WHILE @Column_ID IS NOT NULL
	BEGIN
		SELECT 	@Column_Name = QUOTENAME(COLUMN_NAME), 
		@Data_Type = DATA_TYPE 
		FROM 	INFORMATION_SCHEMA.COLUMNS (NOLOCK) 
		WHERE 	ORDINAL_POSITION = @Column_ID AND 
		TABLE_NAME = @table_name AND
		(@owner IS NULL OR TABLE_SCHEMA = @owner)

		IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1 
		BEGIN
				SET @IDN = @Column_Name
		END
		
		
		
		

		SET @Actual_Values = @Actual_Values  +
		CASE 
			WHEN @Data_Type IN ('char','varchar','nchar','nvarchar') 
				THEN 
					'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
			WHEN @Data_Type IN ('datetime','smalldatetime') 
				THEN 
					'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')'
			WHEN @Data_Type IN ('uniqueidentifier') 
				THEN  
					'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'
			WHEN @Data_Type IN ('text','ntext') 
				THEN  
					'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'					
			WHEN @Data_Type IN ('binary','varbinary') 
				THEN  
					'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'  
			WHEN @Data_Type IN ('timestamp','rowversion') 
				THEN  
					CASE 
						WHEN @include_timestamp = 0 
							THEN 
								'''DEFAULT''' 
							ELSE 
								'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'  
					END
			WHEN @Data_Type IN ('float','real','money','smallmoney')
				THEN
					'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ',2)' + ')),''NULL'')' 
			ELSE 
				'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name  + ')' + ')),''NULL'')' 
		END   + '+' +  ''',''' + ' + '
		
		SET @Column_List = @Column_List +  @Column_Name + ','	
		
 		SELECT 	@Column_ID = MIN(ORDINAL_POSITION) 
 		FROM 	INFORMATION_SCHEMA.COLUMNS (NOLOCK) 
 		WHERE 	TABLE_NAME = @table_name AND 
 		ORDINAL_POSITION > @Column_ID AND
 		(@owner IS NULL OR TABLE_SCHEMA = @owner)




	END

SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)
SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)




	BEGIN
		SET @Actual_Values = 
			'SELECT ' +  
			CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END + 
			'''' + RTRIM(@Start_Insert) + 
			' ''+' + '''(' + RTRIM(@Column_List) +  '''+' + ''')''' + 
			' +''VALUES(''+ ' +  @Actual_Values  + '+'')''' + ' ' + 
			COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
	END






IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)
	BEGIN
		IF @owner IS NULL
			BEGIN
				SELECT 	'ALTER TABLE ' + QUOTENAME(COALESCE(@chvTargetTable, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
			END
		ELSE
			BEGIN
				SELECT 	'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@chvTargetTable, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
			END

		
	END



exec (@Actual_Values)


END

GO

then create this one

testScriptSQLDenis 'orders'
create procedure testScriptSQLDenis
@table sysname
as
set nocount on
create  table #testPrintString (id int identity, PrintString varchar(8000))


insert into #testPrintString
exec testInsertSQLMenace @table

	
declare @Identityflag bit
select @Identityflag = 0



declare @IDColumnName varchar(500)

SELECT @IDColumnName =column_name
from information_schema.columns where table_name = @table and COLUMNPROPERTY( OBJECT_ID(@table),column_name,'IsIdentity') =1
--SELECT @IDColumnName

print 'SET NOCOUNT ON'
if @IDColumnName is not null
print 'SET IDENTITY_INSERT ' +  QUOTENAME(@table) + ' ON'


declare @print varchar(800)
declare @TableLoopid int
declare @TablemaxID int
select @TablemaxID = max(ID) ,@TableLoopid =1
from #testPrintString
while @TableLoopid <=@TablemaxID
begin
select @print = PrintString from #testPrintString where ID = @TableLoopid
print @print
select @TableLoopid = @TableLoopid + 1
end

if @IDColumnName is not null
begin
	print 'SET IDENTITY_INSERT ' +  QUOTENAME(@table) + ' OFF'
	
	create table #testmaxId (MaxID int)
	declare @SQl varchar (500)
	select @SQl ='insert into #testmaxId select max( ' + @IDColumnName + ') from ' +  QUOTENAME(@table)
	exec (@SQL)
	
	declare @MaxIdentityID VARCHAR(20)
	select @MaxIdentityID = convert(varchar,MaxID)+1 from #testmaxId
	
	print 'DBCC CHECKIDENT (' + QUOTENAME(@table) + ', RESEED,' + @MaxIdentityID +')'
end

drop  table #testPrintString,#testmaxId
print 'SET NOCOUNT ON'
set nocount off

Okay, now create new DB and script out table I took orders

use master
create database testscript
go
use testscript
go

create TABLE [Orders] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
	[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EmployeeID] [int] NULL ,
	[OrderDate] [datetime] NULL ,
	[RequiredDate] [datetime] NULL ,
	[ShippedDate] [datetime] NULL ,
	[ShipVia] [int] NULL ,
	[Freight] [money] NULL CONSTRAINT [DF_Orders_Freight] DEFAULT (0),
	[ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [PK_Orders] PRIMARY KEY  CLUSTERED 
	(
		[OrderID]
	)  ON [PRIMARY] 
		
) ON [PRIMARY]
GO

Run the procedure like this

exec testScriptSQLDenis 'orders'

Copy the print statements
go to the testscript DB
paste the copied print statements and hit F5
do a select * from table

BTW I did borrow some code from Vyas but i modified it quite a bit
the second proc (testScriptSQLDenis) is complete my code

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Any other people with code???
did someone run the code that I provided and did it run without errors?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Here are some random observations:

Good
Dump worked on table Orders
Identity inserts only when table has identity column - OK.
Strings quoted, dates in non-ambiguous format - OK.
Some ownership issues solved - OK.

Not so good
Sproc is very sensitive to table name argument:

- if name has some spaces then runtime error happens (cannot drop table #testmaxId)
- if table does not exist, buncha runtime errors happen. Ditto for already quotenamed table name ([Orders])
- if name is system table name, result = runtime errors + useless INSERTs

OK, I'm nit-picking things.

Identity reseed - I think RESEED value is set for 1 too high (next value will become 11079, while last one is 11077)
Disabled constraits - kewl. But shouldn't that state be restored back after INSERTs?
Not sure routine will work on larger binary/varbinary columns (integer overflow)

Considering the fact entire problem is kind of time-consuming - job well done.

I'll post my ramblings later afternoon (ET).

Anybody else?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

>>- if name has some spaces then runtime error happens (cannot drop table #testmaxId)
>>- if table does not exist, buncha runtime errors happen. Ditto for already quotenamed table name ([Orders])
>>- if name is system table name, result = runtime errors + useless INSERTs

you are right about these, but I only started yesterday afternoon that shouldn't be that bad to correct
a couple of if statements.....


>>Identity reseed - I think RESEED value is set for 1 too high
I don't even think I needed to do that, it was a bonus from me, I have it as MaxID + 1 so I could just say MaxID

constraints are not disabled since set @disable_constraints = 0

I can work on improving this monster...who needs red-gate anymore haha

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> I don't even think I needed to do that, it was a bonus from me, I have it as MaxID + 1 so I could just say MaxID

Or simply do RESEED without value. Or do RESEED with exactly the same IDENT_CURRENT() value from source table, dunno.

> I can work on improving this monster...who needs red-gate anymore haha

To be honest, extra pair of eyes for this problem doesn't hurt. My original intention was: take the best from everything posted - and FAQ it.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

You are right it should be a FAQ
very useful

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Nobody else is taking the challenge so... here come my rants&ramblings (warning: long post!).

Suppose we want to dump the following table (named myTable):

CustomerID LastName    FirstName   DOB         YearlySalary
int        varchar(30) varchar(30) datetime    money
----------.-----------.-----------.-----------.------------
         1 Smith       Joe         1974-04-23        40,000
         2 Gates       William     NULL                NULL
         3 O'Reilly    Mary        1969-08-12        45,000

Expected output should look like:

LineNo SQLCode
------.------------------------------------------------------------------------
     1 INSERT INTO myTable values (1, 'Smith', 'Joe', '19740423', 40000)     
     2 INSERT INTO myTable values (2, 'Gates', 'William', NULL, NULL )
     3 INSERT INTO myTable values (3, 'O''Reilly', 'Mary', '19690812', 45000)

Regarding explicit column list (INSERT INTO myTable (column_list, ...)) ... it is sometimes required. Examples:

- if table has identity column, server will refuse INSERTs if explicit column is not specified.
- like we said, some data types can be (must be?) skipped - text, ntext, image, timestamp. If table has column(s) of any of these datatypes...
- if there are no guarantees physical order of columns in target table is identical then explicit column list is at least welcomed

Here are some other things to take care about:

- if table has identity column
- generate SET IDENTITY_INSERT <tablename> ON|OFF lines before and after INSERTs
- reseed identity after INSERTs
- square braces around object/column names - just to be sure (test case: table "Order Details" from Northwind DB)
- save & restore state for table constraints (and maybe triggers?)
- no data loss in exported data - especially for numeric data types
- NULL values written as "NULL" (without quotes)
- exported dates in non-ambiguous format - or extra generated SET DATEFORMAT line of code.
- no weirdos with binary/varbinary types

About implementation... procedural logic is relatively simple. Two nested loops: one over rows, another over columns (or vice versa), build INSERT statements. Arguably the best approach is to generate single dynamic SQL statement that generates (ugh!) VALUES() lists:

SELECT dbo.any2sql([CustomerID], 'int') + ', ' +
		dbo.any2sql([LastName], 'varchar') + ', ' +
		dbo.any2sql([FirstName], 'varchar') + ', ' +
		dbo.any2sql([DOB], 'datetime') + ', ' +
		dbo.any2sql([YearlySalary], 'money')
FROM [myTable]

The rest of generated INSERT statement is constant, so we can SELECT it later. That way we simply avoid looping over rows.

Here is what I did:

1. User-defined function that converts any expected data type to valid SQL code: (... I think)

create function any2sql( @value sql_variant, @data_type varchar(32) )
returns nvarchar(1000)
as
begin
	declare @ret nvarchar(1000)
	declare @quotes bit; set @quotes = 0

	if @value is null or @data_type in ('text', 'ntext', 'image', 'timestamp')
		set @ret = 'NULL'
	else 
	begin
		if @data_type in ('datetime', 'smalldatetime')
		begin
			set @ret = convert(varchar(8), @value, 112) 
			if convert(datetime, @value) <> @ret set @ret = @ret + ' ' + convert(varchar(12), @value, 114)
			if @data_type = 'smalldatetime' set @ret = left(@ret, 14)
		end
		else if @data_type in ('float', 'real', 'decimal', 'money', 'smallmoney', 'bigint', 'int', 'smallint', 'tinyint', 'bit')
			set @ret = convert(varchar(1000), @value)
		else if @data_type in ('uniqueidentifier', 'nvarchar', 'nchar', 'varchar', 'char')
			set @ret = replace(convert(varchar(1000), @value), char(39), char(39)+char(39))
		else if @data_type in ('varbinary', 'binary')
			set @ret = master.dbo.fn_varbintohexstr(convert(varbinary, @value))

		if @data_type in ('datetime', 'smalldatetime', 'uniqueidentifier', 'nvarchar', 'nchar', 'varchar', 'char')
			set @ret = char(39) + @ret + char(39)
	end
	return @ret
end
go

Hey, sql_variant finally comes handy.

2. Conversion code.

create procedure adm_dumpTable( @table_name sysname )
as
set nocount on

-- resolve object ID
declare @objid int, @quotedname sysname
set @objid = OBJECT_ID(@table_name)
if objectproperty( @objid, 'IsUserTable' ) is null raiserror('Object does not exist', 16, 1)
if objectproperty( @objid, 'IsUserTable' ) = 0 raiserror('Object is not a user table', 16, 1)
if @table_name not like '\[%\]' escape '\' set @quotedname = quotename(@table_name) else set @quotedname = @table_name

-- temporary table holding converted values and additional lines of code
create table #blah
(	[lineNo] int identity(1, 1) primary key,
	isInsert bit default 1,
	SQLCode nvarchar(4000)
)

-- check for identity column
declare @identValue bigint; set @identValue = ident_current(@table_name)
if @identValue is not null insert into #blah(isInsert, SQLCode) values (0, 'SET IDENTITY_INSERT ' + @quotedname + ' ON')

-- build column list and dynamic expression for conversions
declare @collist varchar (1000), @dynsql varchar(4000)

select @collist = coalesce( @collist + ', ', '') + quotename(column_name),
	@dynsql = coalesce( @dynsql + ' +'', ''+ ', '') + 'dbo.any2sql(' + quotename(column_name) + ', ''' + data_type + ''')'
from information_schema.columns 
where table_name = @table_name
	and data_type not in ('text', 'ntext', 'image', 'timestamp') 
order by ordinal_position

-- don't create column list if all columns are available (no text/ntext... types) and table has no identity column
set @collist = '(' + @collist + ')'
if @identValue is null and	(objectproperty(@objid, 'TableHasTimestamp') | objectproperty(@objid, 'TableHasTextImage') = 0) 
	set @collist = ''

-- do it!
exec( 'INSERT INTO #blah (SQLCode) SELECT ' + @dynsql + ' FROM ' + @quotedname )

-- restore identity status/last value if necessary
if @identValue is not null
begin
	insert into #blah(isInsert, SQLCode) values (0, 'SET IDENTITY_INSERT ' + @quotedname + ' OFF')
	insert into #blah(isInsert, SQLCode) values (0, 'DBCC CHECKIDENT(' + @quotedname + ', RESEED, ' + convert(varchar(16), @identValue)+')')
end

select [lineNo], 
	case isInsert
		when 1 then 'INSERT INTO ' + @quotedname + @collist + ' VALUES(' + SQLCode +')'
		else SQLCode
	end as SQLCode
from #blah
order by [lineNo]

drop table #blah
go

No loops of any kind . Note I traded some speed (not the most important thing for this purpose) for readability (UDF).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

And now, the conclusion.

Star goes to: SQLDenis.
Honorable mention: pwilson.

With some cleanup and testing this is FAQ material for sure.

Next puzzle please?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

Of course I had to give you a star also
Yes what is the next puzzle?
Let me think, I will come up with something over the weekend if no one else comes up with anything


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/