Archive for the ‘Database’ Category

I had a HDD taken out of my NAS. Although the HDD was fine but NAS was loosing its connectivity capabilities and eventually not not working one day.

I took the HDD out and found through using different software from Windows that it had Ext4 and RAW file format.

I have tried many software but I was not being able to see my folders and files but rather some other folders and files that I did not recognise (these were the files and folders used by the NAS).

After much looking further I stumbled upon http://www.reclaime.com/.

I tried the trial software and within a few clicks I could see all my files and folders. I was amazed to able to browse the folders and files that belonged to me. I was also really amazed at how easy it was to use the software.

I went ahead and bought the software. Although it was pricey (£190.93) it exactly did what it said on the tin.



Read Full Post »

-- Please note that this is not my code and I have found it somewhere on the net

CREATE FUNCTION [dbo].[udf_Split] 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
        INSERT INTO @output (data)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

Read Full Post »

--Please note section 3 nneds to be run each time an Insert, Update or Delete is performed. 
--If section 3 is not run after each DML then only the last DML can be garaunteed.

/*  Section 1: Run to reset. This recreates the tables and makes it ready from scratch

if object_id('tempdb..##org') is not null drop table ##org

if object_id('tempdb..##hist') is not null drop table ##hist

create table ##org
	id int identity 
	, name varchar (50)
	, age int 

create table ##hist
	sur int identity 
	, id int 
	, name varchar (50)
	, age int 
	, isCurrent bit 
	, EffectiveFrom datetime
	, EffectiveTo datetime

insert into ##org values 
	, ('dick','29')
	, ('harry','40')

insert into ##hist
select * , 1, GETDATE(), '9999-1-1'
from ##org

select * from ##org 
select * from ##hist


/*		Section 2: Perform an opertion. Perform an Insert, Update or Delete 

-- insert 
insert into ##org values ('ron','19')

-- update 
update ##org set name = 'jerry' where id =1

-- delete 
delete from ##org where id = 3

select * from ##org  
select * from ##hist


--Section 3: Run this each time an above DML is performed and the changes will be saved in the history table (##hist)

insert into ##hist (id, name, age, isCurrent, effectiveFrom, effectiveTo) 
select id, name, age, 0 as  isCurrent, effectiveFrom, effectiveTo
	merge ##hist as target 
		using ##org as source 
			on		source.id = target.id 

	when MATCHED  
				and isCurrent = 1
				and exists -- UPDATE - when record exists but some other fields changed (the bleow also handles nulls)
						(select source.id, source.name, source. age
						select target.id, target.name, target. age)

	then  -- UPDATE - when record exists but some other fields changed
		UPDATE set
			Target.name = Source.name
			, Target.age = Source.age
			, Target.isCurrent = 1 
			, Target.EffectiveFrom = getdate()
			, Target.EffectiveTo = '9999-1-1' 

	when NOT MATCHED BY TARGET -- INSERT -- new records exits in the source 
		INSERT --(id, name, age, isCurrent, EffectiveFrom, EffectiveTo) 
		values (source.id, source.name, source.age, 1, getdate(), '9999-1-1')
	when NOT MATCHED BY SOURCE -- DELETE -- if a record is not found then UPDATE EffectiveTo date as getdate() adn set isCurrent to 0
		UPDATE set
			Target.isCurrent = 0
			, Target.EffectiveTo = getdate()
	-- Getting the previous row before the update is comminted 
	output $ACTION ActioinOut
			,  deleted.id, deleted.name, deleted.age, inserted.isCurrent as IsCurrent, deleted.effectiveFrom, getdate() as effectiveTo
	) RowBeforeMerge
where RowBeforeMerge.ActioinOut = 'Update'
	and RowBeforeMerge.isCurrent = 1 -- this helps not to write the the row that is deleted twice
									-- when DELETED inserted.isCurrent is equal to 0

select * from ##org  
select * from ##hist

Read Full Post »

declare @t table&nbsp;( f1 int identity , f2 varchar(100))
insert into @tselect 'hello'unionselect 'world'

select * from @t&nbsp;

-- Table to XML&nbsp;
select&nbsp; ( select 1 as f1r1 , 2 as f1r2 for XML path ('f1'), type ),
 ( select 'hello' as f2r1 , 'world' as f2r2 for XML path ('f2'), type )for XML path (''), root('sample')

-- XML to table&nbsp;declare @myXML xml
set @myXML =&nbsp;'&lt;sample&gt;&nbsp; &lt;f1&gt;&nbsp; &nbsp; &lt;f1r1&gt;1&lt;/f1r1&gt;&nbsp; &nbsp; &lt;f1r2&gt;2&lt;/f1r2&gt;&nbsp; &lt;/f1&gt;&nbsp; &lt;f2&gt;&nbsp; &nbsp; &lt;f2r1&gt;hello&lt;/f2r1&gt;&nbsp; &nbsp; &lt;f2r2&gt;world&lt;/f2r2&gt;&nbsp; &lt;/f2&gt;&lt;/sample&gt;'

select&nbsp; a.b.value('f1[1]/f1r1[1]','int') as f1 , a.b.value('f1[1]/f1r2[1]','int') as f2
 , a.b.value('f2[1]/f2r1[1]','varchar(10)') as f3 , a.b.value('f2[1]/f2r2[1]','varchar(10)') as f4from @myXML.nodes('sample')a(b) --read this as @myXML.nodes('[root]') [table name]([column name])

Read Full Post »

declare @columnList varchar(5000) = ''&nbsp;
-- The will give you a actual name of all the Fields in a table&nbsp;select @columnList = @columnList + case when column_id = 1 then '' else ',' end + '[' + name + ']'from sys.columns cwhere object_id = object_id('schema.aTable')
print 'Field Captions: ' + @columnList

set @columnList = null -- reinitialising.&nbsp;-- The below will give you the a list of all values of a field as a CSV&nbsp;set @columnList = null -- reinitialise&nbsp;SELECT @columnList= isnull(@columnList + ',[' + FieldName + ']' ,'[' + FieldName + ']')FROM aTable
print 'Values of a field: ' + @columnList

Read Full Post »

-- Please note this is not my work.
-- Code cpoied and altered from:


IF OBJECT_ID ('tempdb..#books') IS NOT NULL
DROP TABLE #books;

BookTitle nvarchar(50) NOT NULL,
ModifiedDate datetime NOT NULL


-- declare @InsertOutput1 table variable

DECLARE @InsertOutput1 table
BookID int,
BookTitle nvarchar(50),
ModifiedDate datetime

-- insert new row into #books table
INTO @InsertOutput1
VALUES(106, 'One Hundred Years of Solitude', GETDATE());

-- view inserted row in #books table
--SELECT * FROM #books;

-- view output row in @InsertOutput1 variable
SELECT 'INSERT', * FROM @InsertOutput1;


DECLARE @UpdateOutput2 table
OldBookID int,
NewBookID int,
BookTitle nvarchar(50),
OldModifiedDate datetime,
NewModifiedDate datetime,
DiffInSeconds int

-- update row in #books table
UPDATE #books
BookID = BookID + 1,
ModifiedDate = GETDATE()
DATEDIFF(ss, DELETED.ModifiedDate, INSERTED.ModifiedDate)
INTO @UpdateOutput2
WHERE BookTitle = 'One Hundred Years of Solitude';

-- view updated row in #books table
--SELECT * FROM #books;

-- view output row in @UpdateOutput2 variable
SELECT 'UPDATE', * FROM @UpdateOutput2;


DECLARE @DeleteOutput1 table
BookID int,
BookTitle nvarchar(50),
ModifiedDate datetime

-- delete row in #books table
DELETE #books
INTO @DeleteOutput1
WHERE BookID = 107;

-- view updated row in #books table
-- SELECT * FROM #books;

-- view output row in @DeleteOutput1 variable
SELECT 'DELETE', * FROM @DeleteOutput1;

Read Full Post »

This is an ongoing post that will be appended with new additions.

In a SQL Task the “Parameter Mapping” and the SQL executed must be in SYNC else SSIS cannot complete the task and it stays Waiting (yellow colour) and will NOT throw an error. For example if you are passing a parameter the query needs to have a a question mark.


Read Full Post »

Older Posts »