Sql Server & SSRS Tips

Basic Example of error handling using TRY and CATCH statement. In this example i am trying to store a varchar value to an int variable which cause error: BEGIN TRY -- trying to store a varchar value to an int variable to raise error, to be catched in CATCH statement     DECLARE @propertyID INT;     DECLARE @propertyName VARCHAR(50);     SET @propertyName = 'ABC';     SET @propertyID = @propertyName;     SELECT  @propertyID; END TRY BEGIN CATCH     SELECT  ERROR_LINE() AS ErrorLineNumber ,             ERROR_MESSAGE() AS ErrorMessage ,             ERROR_NUMBER() AS ErrorNumber ,             ERROR_PROCEDURE() AS ErrorProcName ,             ERROR_SEVERITY() AS ErrorSeverity ,             ERROR_STATE() AS ErrorState; END CATCH;

To kill all the active processes of a database except your own process execute the below query after providing the database name in parameter (@DBName). It provides you the Kill statements with SPID's in result set, Now copy and paste the result set in new window and execute that statements, it will kill all processes of a database except yours. SQL CODE: IF OBJECT_ID('TEMPDB..#SP_WHO') IS NOT NULL     DROP TABLE #SP_WHO; IF OBJECT_ID('TEMPDB..#ProcessToKill') IS NOT NULL     DROP TABLE #ProcessToKill; DECLARE @DBName NVARCHAR(150); SET @DBName = 'YourDatabaseName'; CREATE TABLE #SP_WHO     (       IDX INT IDENTITY(1, 1) ,       SpID INT ,       ECID INT ,       STATUS VARCHAR(50) ,       LOGINNAME VARCHAR(100) ,       HOSTNAME VARCHAR(100) ,       BLK INT ,       DBNAME VARCHAR(150) ,       CND VARCH...

Grouping sets allow multiple grouping on columns in a single data set. Rollup and Cube also used for summary rows (allows multiple grouping same as Grouping sets) Example: DECLARE @t TABLE     (       idx INT IDENTITY(1, 1) ,       Country VARCHAR(50) ,       City VARCHAR(50) ,       NoOfJobOpenings INT     ); INSERT  INTO @t         ( Country ,           City ,           NoOfJobOpenings         )         SELECT  'Pakistain' ,                 'Karachi' ,                 50         UNION         SELECT  'Pakistain' ,                 'Lahore' ,             ...

Before Script these are the one line description of information_schema which will use. INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS Returns one row for each FOREIGN KEY constraint in the current database. INFORMATION_SCHEMA.TABLE_CONSTRAINTS Returns one row for each table constraint in the current database. INFORMATION_SCHEMA.KEY_COLUMN_USAGE Returns one row for each column that is constrained as a key in the current database. Script to list down all constraints of a database is:  SELECT  K_Table = FK.TABLE_NAME ,         FK_Column = CU.COLUMN_NAME ,         PK_Table = PK.TABLE_NAME ,         PK_Column = PT.COLUMN_NAME ,         Constraint_Name = C.CONSTRAINT_NAME FROM    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C         INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME   ...