Feeds:
Posts
Comments

Archive for the ‘Database’ Category


-- 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/#

 

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
);

 

-- 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;

 

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;

 

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;

Advertisements

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 »


--SSIS > Script Task > Message Box
------------------------------------
            string strMessage = Dts.Variables["User::strAlterCmd"].Value.ToString();
            MessageBox.Show(strMessage);

--ALTER TABLE CHECK
-----------------------

IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID

('dbo.usama_mdlDataLake_Behaviour')
				AND name in ('carBreakdown'))		-- UA:You will either 

have all or none so just check any one column

--Check if Index exists / Create
-------------------------------
CONSTRAINT PK_tbl_ComplaintExtracts_LoadID PRIMARY KEY CLUSTERED (LoadID)

IF NOT EXISTS (SELECT top 1 *  FROM sys.indexes  WHERE name = 'ix_churn1')

--TempDB:
	IF NOT EXISTS (SELECT top 1 * FROM tempdb.sys.indexes  WHERE name = 'ix_ChurnIndex1') 

create unique clustered index ix_ChurnIndex1 on #t1(customerid)

CREATE NONCLUSTERED INDEX [IX_tbl_BGL_Transactions_FileName] ON [dbo].[tbl_BGL_Transactions] 

([PolicyNumber])

 IF EXISTS (SELECT *  FROM sys.indexes  WHERE name='Index_Name'
    AND object_id = OBJECT_ID('[SchmaName].[TableName]'))
DROP INDEX ix_tblSVV_Permissions_Orchid_CustomerID ON [dbo].[tblSVV_Permissions]

--Table Exists
--------------
IF NOT EXISTS (SELECT top 10 * FROM INFORMATION_SCHEMA.TABLES
   	WHERE TABLE_NAME = 

'DataLoad' -- do not include square brackets
			AND TABLE_SCHEMA = 'stg')
BEGIN       --
  PRINT 'Table Exists'
END

--VIEW Exists
------------------
IF EXISTS(select top 1 * FROM sys.views where name = 'myV')
DROP VIEW myV

--Drop all temp tables
--------------------------

DECLARE @DropTableSQL varchar(max)
SELECT @DropTableSQL = ISNULL(@DropTableSQL + ';', '') + 'drop table ' + SUBSTRING(t.name, 1, 

CHARINDEX('___', t.name) - 1)
FROM tempdb..sysobjects AS t
WHERE t.name LIKE '#%[_][_][_]%'
	AND t.id = OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name) - 1));
PRINT @DropTableSQL
--EXEC (@DropTableSQL)


--Try catch block
----------------------
BEGIN TRY
DECLARE @MyInt INT, @flag bit = 1  

-- the error state is custom number given so that a developer can locate where the error occured
--http://dba.stackexchange.com/questions/35893/what-is-error-state-in-sql-server-and-how-it-can-be-used

	if @flag = 0
		RAISERROR ('my custom error message', 16, 1); -- the error state a number 
	else 
		SET @MyInt = 1/0;
END TRY
BEGIN CATCH
SELECT @flag flag
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH;
GO 


Read Full Post »

You can passs temp table inside a dynamic sql and it will process that for you


declare @s varchar(200)

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

Select 1 as n
into #t

select * from #t -- first time 

set @s = 
	'update #t 
	set n = 2

	insert into #t
	values (3)
	'

exec (@s)

select * from #t -- seccond time

 

Read Full Post »

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:

http://www.instructables.com/id/Disassembling-a-Western-Digital-My-Book/

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 »


-- List of days in the week
;WITH WeekNames
AS
(
    SELECT datepart(weekday,getdate()) AS WeekNumber, getdate() AS ActualDate

    UNION ALL 

    SELECT datepart(weekday, dateadd(day, 1, b.ActualDate)), dateadd(day, 1, b.ActualDate)
    FROM  WeekNames b
    WHERE  DATEDIFF(day,getdate(), dateadd(day, 1, b.ActualDate)) < 7    
)
SELECT WeekNumber, dateName(weekday, ActualDate) WeekName  
FROM WeekNames
ORDER BY WeekNumber

-- List of the months 
;WITH MonthNames
as
(
    SELECT datepart(month, getdate()) AS MonthNumber, getdate() as ActualDate 

    UNION ALL 

    SELECT datepart(month, DATEADD(month, 1, mn.ActualDate)), dateadd(month, 1, mn.ActualDate) 
    FROM MonthNames mn
    WHERE datediff(month, getdate(), dateadd(month, 1, mn.actualDate)) < 12
)
SELECT MonthNumber, datename(month, ActualDate) as MonthName
FROM MonthNames
ORDER BY MonthNumber

--List of last 10 years 
;WITH Last10Years
as
(
    SELECT datepart(year, getdate()) AS YearNumber, getdate() as ActualDate 

    UNION ALL 

    SELECT datepart(year, DATEADD(year, -1, l10y.ActualDate)), dateadd(year, -1, l10y.ActualDate) 
    FROM Last10Years l10y
    WHERE datediff(year, dateadd(year, -1, dateadd(year, -1, l10y.ActualDate)), getdate()) <= 10
)
SELECT YearNumber
FROM Last10Years
ORDER BY YearNumber DESC 





Read Full Post »

« Newer Posts - Older Posts »