Archive for the ‘How To’ Category

I had a HDD taken out of my NAS – it was the “My Book World Edition II (White Light)”. Although the HDD was fine but the NAS was loosing its connectivity capabilities day by day and eventually stopped working one day.

I successfully took the HDD  out by following the instructions in the below link:


Using varieties of free software from within Windows 10 I discovered that it was partitioned with Linux file format (Ext4 and RAW file formats). I have tried more 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 »

In this example we will be using a Tablix as the objects but really it could be a Matrix, Graphs, Textbox etc.

Assume a scenario. In Tablix A (on the left hand side) we have a list of fruits :
Melons and Apples

If we click on Melons then on a second Tablix B (on the right hand side) we will display the colours Yellow and Green. If apples is selected then we will show Red and Green.

Create a Parameter called DrillThrough of type Text and make the visibility Hidden.

Goto the textbox property of the cell holding Tablix A’s values.
This is where the magic happens:
Select Action.
From there select “Go to report”
Under the Specify a Report select the name of the report you are currently on (this is so that it refer’s back to itself).
Then click the “Add” parameter button.
Select the parameter DrillThrough and under value select the field from Tablix A.

You can now test if you wanted using a text box by creating a text box and in the expression put in =Parameters!DrillThrough.Value
Delete the text box if yout testing is complete.

Finally create a Dataset where the parameter DrillThrough is used so that depending on what is clicked the list of Fruits in Tablix A that will be sent through to display the list of relevant colours in Tablix B.

Read Full Post »

 Cross Join: 
 Each row in the first table is paired with all the rows in the second table. 
if OBJECT_ID('tempdb..#a') is not null 
	drop table #a 

if OBJECT_ID('tempdb..#b') is not null 
	drop table #b 

create table #a 
	acol varchar(10) 
insert into #a values 

create table #b 
	bcol varchar(10) 
insert into #b values 

select * from #a 
select * from #b 

select * from #a cross join #b 

Read Full Post »

Command like Sql can be used to run sql scripts saved in disks. Therefroe if there is a bunch of scripts

(files) they can all be added with ttheir path and file name in notepad and have it saved with anyname with a

cmd extension. Then running this file would execute all the lines in the file.

Use “sqlcmd” instead of osql because “osql” cannot handle alot of special characters like the “£” sign.

Here is an example:
sqlcmd -S MyServerName -E -d My_dbName -i “C:\MyTSQL\MyScript.sql”

The above if run in commandline will execute the script in the path.

This is usefull when we need to run batches of scripts.

To make scripts upload/commit to a server by using the above command make sure the SP/Function or other

objects are alwasy dropped and recreated e.i. starts with the create statement as below:

--For SP:
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mySP]') AND type in (N'P', N'PC'))


--For Function:
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myUDF]') AND type in (N'FN', N'IF', 

N'TF', N'FS', N'FT'))

create FUNCTION [dbo].[myUDF]
	@var AS VARCHAR(100)

Read Full Post »

Assume the below scenario:

A system checks a stock level for a grocery store that sells 3 items and whenever a certain item runs out it flags up a message.

Say the 3 items are:

To make use of binary masking (also known as bitwise) to be able to track which needs to be highlighted, we need to do the following:

1) We will assign a binary number to each one of the item, 1 for denoting we need to flag a message and 0 f0r doing nothing. Each of the the items in turn will have a fixed position. So in this instance there are three position for three items and we can assign it as below:

Item Position Binary Value
—– ——— ————-
Mango 0 1
Orange 1 0
Apple 2 1

2) Now, we need a single decimal number that represents which items jave a binary 1 to flag a message and which don’t. To do that we use the Position value and treat that as the power of 2.
So, we use Position 1 and 3 (because those have the binary value 1 binded) and it should equate to:

(2^0) = 1
+ (2^2) = 4
Total = 5

3) The value 5 can then be sent to the section of the system which flags messages. It should then have a script to reverse it back to a binary number which is 101.

The position number is fixed and therefore can be easily be deduced as to which binary number is binded to whcih item.

This way by only sending an integer number we can for a group of item attached and interchagne flag data.

>Returns the binary representation of a number:

-- courtesy to a script somewhere in the internet
CREATE FUNCTION [csrt].[udf_ReturnBinaryPattern] 
	@Byte INT


    DECLARE  @Pattern CHAR(8) 
    SET @Pattern = '' 

    SELECT @Pattern = convert(VARCHAR,+(@Byte & 1) / 1) + 
    convert(VARCHAR,(@Byte & 2) / 2) + 
    convert(VARCHAR,(@Byte & 4) / 4) + 
    convert(VARCHAR,(@Byte & 8) / 8) + 
    convert(VARCHAR,(@Byte & 16) / 16) + 
    convert(VARCHAR,(@Byte & 32) / 32) + 
    convert(VARCHAR,(@Byte & 64) / 64) + 
    convert(VARCHAR,(@Byte & 128) / 128) 

    RETURN (@Pattern) 


>Optional – Explaning & bitwise AND operator:
The binary representation of 170 (a_int_value or A) is 0000 0000 1010 1010. The binary representation of 75 (b_int_value or B) is 0000 0000 0100 1011. Performing the bitwise AND operation on these two values produces the binary result 0000 0000 0000 1010, which is decimal 10.

SELECT 170 & 75

Result is 10 (thisis a decimal number)

The & operator tranforms number or charters into binary and then adds them up:

170 = 0000 0000 1010 1010
75= 0000 0000 0100 1011
10 = 0000 0000 0000 1010

Another artice: http://sqlfool.com/2009/02/bitwise-operations/

Read Full Post »

The different options available to eliminate/handle decimals

declare @value as float = 123.9876
select ceiling(@value), floor(@value) -- eliminate decimals 
select round(@value,2)

Find triggers inside a database (Courtesy to Harry Lee)

select st.name, strig.name, st.name
from sys.triggers strig inner join sys.tables st
	on strig.parent_id = st.object_id inner join sys.schemas sch
	on st.schema_id = sch.schema_id

Format number as currency

SELECT CONVERT(varchar, CAST(987654321 AS money), 1) 

Find column names in a given table

SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'dbo.Foo' --you can change text to other dataTypes

Note on – pivoting accuracy with numbers
I have seen from experience that when Pivoting its better to use float or decimal (if you results can contain fractional values) and not integers to get more accurate results. If integer is required then the numbers need to be converted after the Pivotting.

Check IF Table is empty

To chekc if a record or a certain record exists

if exists(select top 1 * from TableName) — use the where clause to check for a certain record

The if exists checks whether any rows were returned. If I use top 1 in the selected statement that supposedly sohuld be more optimized because it does not need to show the showl table.
The star is used so that it can just be copied and reused by just changing the table name.

Generate Scripts (Backup scripts)
Right click on a database
> Task
> Generate Scripts

Select the type of objects
Click the Advances button to see what how you want the script to be produced.

This way you would have all or your selected object include tables and its data all scripted out in a file.

Read Full Post »

Fix 100% issue

-- Sometimes working with fractional number and reconciling them to sum up to 100 will not happen and to trick it into doing that the below could be used.
-- Courtesy to Tibor Bicsak 

/**** FIXING the 100% ISSUE **********  ****************************************************/
/* Where the total percentage < 100 and the lowest number is 0 will be incremented with the difference (1) */
/* Where the total percentage < 100 and the lowest number is not 0, the highest number will be incremented with the difference (1) */
/* Where the total percentage > 100 and the highest number will be decreased with the difference (1) */

select * 
,LineNumber = 
     WHEN Percentage IS NULL THEN 1
into #MyGlobalResultSet -- drop table #MyGlobalResultSet
from ##GlobalResultSet
order by Company,Sub

select Company ,Sub 
,sum(Percentage) Percentage
,min(Percentage) as min_per
,max(Percentage) as max_per
,min(LineNumber) as min_LineNumber
,max(LineNumber) as max_LineNumber
into #t1 -- drop table #t1
from #MyGlobalResultSet
--and Company = 'Average'
--and Sub = 'EL'
group by Company ,Sub 
having sum(Percentage) <> 100
order by Company ,Sub 

update g set
Percentage =
    when t.Percentage < 100 and min_per = 0 then 100 - t.Percentage 
    when t.Percentage < 100 and min_per <> 0 then g.Percentage + (100 - t.Percentage)
    when t.Percentage > 100  then g.Percentage - (t.Percentage - 100)
    else g.Percentage
--  select g.*,t.*
from #MyGlobalResultSet as g
join #t1 as t on t.Company = g.Company  and t.Sub = g.Sub 
			and  (
			    (min_per = 0 and t.Percentage < 100 and g.Percentage = min_per and g.LineNumber = min_LineNumber)
			 or (min_per <> 0 and t.Percentage < 100 and g.Percentage = max_per and g.LineNumber = max_LineNumber)
			 or (t.Percentage > 100 and g.Percentage = max_per and g.LineNumber = max_LineNumber)

--drop table ##GlobalResultSet 

select About,Company,Sub,Percentage 
into ##GlobalResultSet2 
from #MyGlobalResultSet 


Read Full Post »

Older Posts »