Archive for August, 2017

-- command line to execute a package 

-- Package model deployment was sued ins SSIS 2008 and earlier.
-- In SSIS 2012 to 2014 you could no deploy individual packages from IDE os instead you had to use DTExec.
-- Ther are multiple ways packages can e deployed:
-- 1. Server
-- 2. File System 

--1. Pakcege in a Server
dtexec /sq MyPackageName /ser MyServerName /va

 -- /va stands for validate. This si used when we do not want to EXECUTE a package but rather jsut validate it

start /min -- windows command to start an application minimised

dtexec -- if the executable cannot be found then include the whole path (whereever SSIS/dtexec is installed) as below:
"c:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTEXEC.exe"

/file the attribute to indicate the dtsx file will follow:

/SET --use this only if variables that need to be initiased (this is also known as parameter passing)
"\Package.Variables[User::Variable name].Properties[Value]"

;%1 -- %1 is first the argument that will be passed from cmd. To hard code this do the following:
    ;"hard coded value".

for second or nth parameter follow the same principle as the first one above but increase the number by 1:

/SET "\Package.Variables[User::Second variable].Properties[Value]",%2
/SET "\Package.Variables[User::nth variable].Properties[Value]",%n

Read Full Post »

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 »

Older Posts »