Feeds:
Posts
Comments

Archive for the ‘Uncategorized’ Category


--command line to execute a package 

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:
"C:\SSIS\TestPackage.dtsx"

/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

Advertisements

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) 
) 
RETURNS @output TABLE(data NVARCHAR(MAX) 
) 
BEGIN 
    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)
        
    END 
    RETURN 
END

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 
	('tom','34')
	, ('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
from 
	(
	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
						except 
						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 
	then 
		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
	then 
		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:
https://www.red-gate.com/simple-talk/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/#

&nbsp;

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

CREATE TABLE #books
(
BookID int NOT NULL PRIMARY KEY,
BookTitle nvarchar(50) NOT NULL,
ModifiedDate datetime NOT NULL
);

&nbsp;

-- declare @InsertOutput1 table variable

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

-- insert new row into #books table
INSERT INTO #books
OUTPUT INSERTED.*
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;

&nbsp;

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

-- update row in #books table
UPDATE #books
SET
BookID = BookID + 1,
ModifiedDate = GETDATE()
OUTPUT
DELETED.BookID,
INSERTED.BookID,
INSERTED.BookTitle,
DELETED.ModifiedDate,
INSERTED.ModifiedDate,
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;

&nbsp;

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

-- delete row in #books table
DELETE #books
OUTPUT DELETED.*
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 »