Nominated for MSSQLTips.com Rookie of the Year Award – Please Vote!

As Most of you are aware that I have been writing at MSSQLTIPS.COM. It’s a wonderful community with highly experienced authors worldwide and it is a pleasure for me to be part of this great community.

This year MSSQLTIPS has come up again with two awards – MSSQLTips.com Author of the Year and MSSQLTips.com Rookie of the Year.

I have been nominated for the MSSQLTips.com Rookie of the Year award. I urge each one of you to follow the voting link and vote for  me (Ghanesh Prasad).

Voting Link: https://www.mssqltips.com/mssqltips-author-voting.asp

Voting Directions: Select the Author of the Year in the left column and the Rookie of the Year in the right column (Ghanesh Prasad) then press the "Submit" button at the bottom of the page to cast your vote. NOTE - One vote per day per IP address.

Interactive Sorting for a SQL Server Reporting Services Report

You created a report for your client and the report default sorting is in descending order which was applied at the dataset level. But what if the client wants to see the data in ascending order? Or what if the client wants to be able to switch back and forth as needed? In this tip we look at how to make the sorting option interactive, so after the report has been rendered the user can changed the sort order.

Check out complete article here.

Dynamically Refresh SQL Server Reporting Services Report Date Parameters

You created a report in SQL Server Reporting Services that has four parameters Year, Month and two Calendar Date Picker parameters StartDate and EndDate. The default values for StartDate and EndDate are determined by theYear and Month parameters. So when a new Month or Year is selected the value for StartDate should be the first day of the Month and the EndDate should be the last day of the Month. When you set default values for StartDateand EndDate the parameter values are correct the first time, but if you change the Month or Year the values forStartDate and EndDate don't change. In this tip we show how to solve this problem.

Check out complete article here.https://www.mssqltips.com/sqlservertip/3560/dynamically-refresh-sql-server-reporting-services-report-date-parameters/

SSIS Toolbox is not visible in SQL Server Data Tools

The SSIS Toolbox is very important in SQL Server Integration Services to allow you to add components to the SSIS package. If you close the SSIS Toolbox sometimes making it display again doesn't work. In this tip we look at what is happening and how to resolve this issue.

check out complete article here.

SQL Server Encrypt Column data using Symmetric Key

USE TEACHMESQLSERVER
GO

-- STEP 1 CREATING A DUMMY TABLE

CREATE TABLE EMP(EMPID VARCHAR(12))
GO

-- STEP 2 INSERTING DUMMY ROW

INSERT INTO EMP VALUES('111-111-111')
GO

-- STEP 3 CREATING MASTER KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='ABCD1234'
GO

-- STEP 4 CREATING CERTIFICATE

CREATE CERTIFICATE CERTIFICATE1 WITH SUBJECT ='PROTECT DATA'
GO

-- STEP 5 CREATING SYMMETRIC KEY

CREATE SYMMETRIC KEY SYMMETRICKEY WITH ALGORITHM =AES_128
ENCRYPTION BY CERTIFICATE CERTIFICATE1
GO

-- STEP 6 CREATING A NEW COLUMN WITH DATA TYPE VARBINARY

ALTER TABLE EMP ADD ENCRYPTEDEMPID VARBINARY(MAX)
GO

-- STEP 7 ENCRYPT THE COLUMN DATA USING UPDATE COMMAND

OPEN SYMMETRIC KEY SYMMETRICKEY
DECRYPTION BY CERTIFICATE CERTIFICATE1
GO
UPDATE EMP SET ENCRYPTEDEMPID = ENCRYPTBYKEY(kEY_GUID('SYMMETRICKEY'),EMPID) FROM EMP
GO
SELECT * FROM EMP
CLOSE SYMMETRIC KEY SYMMETRICKEY


-- STEP 8 DECRYPT AND READ THE COLUMN DATA

OPEN SYMMETRIC KEY SYMMETRICKEY
DECRYPTION BY CERTIFICATE CERTIFICATE1
SELECT CAST(DECRYPTBYKEY(ENCRYPTEDEMPID) AS VARCHAR(MAX)) AS DECRYPTED FROM EMP
CLOSE SYMMETRIC KEY SYMMETRICKEY

-- STEP 9 INSERT THE ENCRYPTED  DATA

OPEN SYMMETRIC KEY SYMMETRICKEY
DECRYPTION BY CERTIFICATE CERTIFICATE1
INSERT INTO EMP(ENCRYPTEDEMPID) VALUES(ENCRYPTBYKEY(KEY_GUID('SYMMETRICKEY'),CAST('121-345-765' AS varchar)))
CLOSE SYMMETRIC KEY SYMMETRICKEY
SELECT * FROM EMP
GO

Get detailed explanation here.