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

if OBJECT_ID('tempdb..#tmp_Restores') is null begin SELECT  @@servername as ServerName  , r.command as [CMD_Type]  , r.start_time  , r.percent_complete  , dateadd(second, r.estimated_completion_time / 1000, getdate()) as est_completion_time  /* Remark: 1900-01-01 00:00:00.000 offset !!! */  , dateadd(ss, DATEDIFF(SS, r.start_time, getdate()), 0) as running_time_1900_01_01  , dateadd(ss, r.estimated_completion_time / 1000, 0) as est_time_to_go_1900_01_01  , dateadd(ss, DATEDIFF(SS, r.start_time, dateadd(second, r.estimated_completion_time / 1000, getdate())), 0) as est_elaps_time_1900_01_01  , es.login_name  , es.host_name  , r.wait_type  , r.wait_time  , r.wait_resource  , r.cpu_time  , r.total_elapsed_time  , r.reads  , r.writes  , r.logical_reads  , s.text as SQLStmt into #tmp_Restores FROM    ...

To delete duplicate records from a table without any schema changes or temp tables. You can use CTE. In example below we have duplicate Class entries in class table, first we can generate rownumber in CTE then delete from CTE where rownumber>1 ; WITH    CTE           AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY vchClassName ORDER BY intClassID DESC ) rn ,                         vchClassName                FROM     dbo.Class              )     DELETE  FROM CTE     WHERE   rn > 1 By deleting records from CTE actual tables records are be deleted.