SSRS - How to Create Combo Chart in SSRS

What is Combo Chart?

A combo chart is a type of chart which is made up of more than one type of chart. A Combo Chart can be a combination
1) 2 Column Charts

2) 2 Line Charts, 

3) 1 Column Chart & 1 Line Chart Together

What is the advantages of Combo Chart?

1) It shows relationship between multiple data attributes in the chart e.g Total Sales & respective profit

2) We can display multiple measures with different scales

3) It provides more analytical information compare to single chart.

Scanario: 

Below is the sample Column Chart report which Year wise sales information 

  

We would like to show the profit information (Line of Profit) also with respect to Total Sales. This can be achieve by creating a Combo Chart.

Following are the steps to create a Combo Chart in SSRS:

1. Go to the Existing Column Chart and add additional measure e.g. Profit
    

2) Change the chart type as Line Chart for the new measure Profit. 
     

3) Change the formatting of line Series like color, width, marker etc per business requirement.

4) Also change chart title if required

5 Now preview the report

    

Conclusion: We can see Combo chart is not only showing total sales per year but also showing respective year profit too.

SSRS - How to dynamically change Series to Category & Category to Series in SSRS Chart(s)

If there is a requirement of dynamically changing of Series to Category & Series to Category in Column or bar Chart then it can be achive by using report parameter & SSRS expression. 

Scanario: 

Below is the sample Column Chart report which shows Region & year wise sales information with 

1) Region as Series

2) Year is as Category

We can change this dynamically to below:

1) Year as Series

2) Category is as Category

     

Following are the steps to convert Series to Category & Category to Series in Column Chart:

1. Create a parameter named "Series" and put the value as Region & Year
    

2. Now go to the chart and change the expression for Series and Category

3. For Series - use the below expression

=IIF(Parameters!Series.Value = "Year",  Fields!Year.Value,Fields!Region.Value)

Change this expression for Series Group, Sorting, & Label

 

4. For Category - use the below expression

=IIF(Parameters!Series.Value = "Year",  Fields!Region.Value,Fields!Year.Value)

Change this expression for Category Group, Sorting, & Label

 

5) Now Run the report. select Series as Region and preview

     

6) Now Run the report. select Series as Year and preview

    

Conclusion: We can dynamically change Series to Category & Category to Series in SSRS Chart using report parameter & expression.

SSRS Dynamic Visualization - How to dynamically change Row grouping & Column Grouping of Matrix in SSRS Report

If there is a requirement of dynamically changing of Row grouping & Column grouping of Matrix then it can be achive by using  report parameter & SSRS expression. 

Scanario:
Below is the sample report which shows Region & year wise sales information with
1) Region is in Row
2) Year is in Column

We can change this dynamically to below:
1) Year is in Row
2) Region is in Column
 

Following are the steps to convert Row grouping to Column Grouping or vice versa:
1. Create a parameter named RowGrouping and put the value as Region & Year
     

2) For Row Grouping & its sorting - use the below expression:
=IIF(Parameters!RowGrouping.Value = "Region",  Fields!Region.Value,Fields!Year.Value)
  

3) Change the same expression for Textbox value & color in Row Grouping
    

4) For Column Grouping & sorting - use the below expression:
=IIF(Parameters!RowGrouping.Value = "Region", Fields!Year.Value,Fields!Region.Value)
  

5) Change the same expression for Textbox value & color in Column Grouping
    

6) Now Run the report. select RowGrouping as Region and preview:
    

7) Now Run the report. select RowGrouping as Year and preview:
     

Conclusion: We can dynamically change Row & column grouping in SSRS using expression.

SSAS - When we need to do full processing of the cube even we have daily incremental processing of the cube.

Following are the scanario(s) in which we need to do full processing of the cube even we have daily incremental processing of the cube:

1) When we are processing the cube for the first time then full processing will load all the data in the cube.
2) When there is any structure changes in the cube then we need to do full processing so that changes in data will reflect in enitre cube.

SQL Server - Write a query to get list of all the schema(s) created by user(s) in database.

Below is the T-SQL query which will give list of all the database schema(s) created by user. It will exclude default Schema(s) created  SQL Server database.:

SELECT Name
FROM sys.schemas
WHERE name NOT IN (
		N'db_accessadmin'
		,N'db_backupoperator'
		,N'db_ddladmin'
		,N'db_datareader'
		,N'db_datawriter'
		,N'db_owner'
		,N'db_denydatareader'
		,N'db_denydatawriter'
		,N'dbo'
		,N'db_securityadmin'
		,N'guest'
		,N'INFORMATION_SCHEMA'
		,N'sys'
		);

SQL Server - Error while creating Excel as Linked Server for SQL Server

Scenario:

1) Created a Linked Server to an Excel file

2) When we try to pull data from linkedserver with select statement , it is working fine

SELECT *
FROM ExcelLinkedServer...[Sheet1$]
--It is giving data without any issue

3) But when we try sp_tables_ex system stored procedure with Linked Server:

 EXEC sp_tables_ex @table_server = 'myExcelLinkedServer'

It is giving below error:

Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 82 

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Excel_Linked_Report" reported an error. The provider did not give any information about the error.

Msg 7311, Level 16, State 2, Procedure sp_tables_ex, Line 82

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Excel_Linked_Report". The provider supports the interface, but returns a failure code when it is used.

Solution:

1) Go to SQL Server Management Studio

2) Go to Server Objects  then -Linked Servers

3) Click on Providers

4) Select the provider that we need to use and Right click

5) In provider options, check  for Allow inprocess.

SQL Server - Adverse effect of using User defined function in the T-SQL Query Performance

Following are the reaons that why UDF degrade query performance:
1) User defined function calls each row & get the data for it which will degrade the query performance.
2) In case of large volume of data, It will be very very slow.
3) The index is not used by the database if there is a function on the column.

Analysis Service - How to create a measure of varchar datatype in SSAS cube

Work round:

1) We can do some work around if we have data like Age (21,50, 75) which is stored in VARCHAR datatype.

2) We can change the Data source view definition for the cube

3) Create a new column (Age_INT) with some numeric data type e.g. Integer and map our varchar column (Age) to this new column say Age_INT.

4) We can use this newly created column (Age_INT) for our calculated member.

SSRS Performance Issue - Large SSRS report causing restart of server

Following things can be checked to resolve the issue:

1) Run the report query in SSMS and see whether it is causing restart or not.

2) Also check whether report query in SSMS is giving output or not.

3) Run the report query for top 50 records and check if SSMS is giving output or not.

4) Check it report query is terminating after some time or keep executing till restart.

5) Check the system logs of the server where we can identify apart from SSRS, is it anything else causing restart of server.

6) Check the Reporting Services log file to see the detail error message.

7) Check ReportServer ExecutionLog to see which part of the report takes more time.

8) Check the ExecutionLogStorage table and let see what is max time report takes to execute if any successful executuion is there.

9) Go to C:\Program Files\Microsoft SQL Server\MSRS10_50.SSRSBI\Reporting Services\LogFiles to check Report Server log  you will get the detail information about the reason causing the restart if problem with SSRS. 

10) Check CPU and Memory utilization while running the report.

11) Execute the report from repor builder /SSDT & from Report Manager and see if any difference in performance.

12) Have you experinced anytime, "Not Responding message" for report in the Task bar.

13) Check the RAM size of the machine and increase if possible.

14) Check the Network Bandwidth between Report Server & Database Server.

15) Check Auto File Growth for ReportServerTemp DB in database property and allow file Growth

16) Change the dataset query and run the report for top 50/100 records and see whether you are getting any restart issue.

17) Run these reports in multiple machine(s) and check that whether all machine(s) are getting restarted.

SSIS - Performance issue with SQL Sever Agent Job exeuting SSIS packages

Following are the suggestions:

1) If your SSIS server is shared instance then there could be possibility that your IO/memory utilization of SSIS Instance is very high & could be reason of taking longer time to load the data.

2) You can check the schedule of other jobs and try to run your package(s) in idle time to more analysis so that parallel processing / Locking/ Blocking process can be avoided.

3) Is SSIS server & DB server are two different physical server then there could be issue with network bandwidth.

4) Check how much volumne of data is processing. Dont process unnecessary data.

5) Limit the data for processing e.g. Year wise process the data.

6) Check if same performance issue with SSIS when executing from SSIS Repository or SSIS solution.

7)  Avoid SSIS execution log if applicable.

8) Check whether you are using correct SSIS task  to achieve the result.

9) Check whether you have indexes on the table or not.

10) Use incremental load, in place of full load. 

SQL Server -How to check truncation/ all rows deleted status of the table in SQL Server Database

We can check truncation/ all rows deleted status by using the below query. E.g. Employee is a table.

SELECT object_Name(object_id)
	,[Rows] AS RowCounts
FROM sys.partitions 

WHERE object_id = object_id('Employee')

We can use this query in the SQL Agent to notify  required team if there will be a requirement of notification too.

IF (
		SELECT [Rows] AS RowCounts
		FROM sys.partitions
		WHERE object_id = object_id('Employee')
		) <= 0
BEGIN
	PRINT 'Write send email code' 

END 

SQL Server - How to trigger a process to stop SQL Agent Job when data insertion happened in a table

To trigger a process after insertion, we need to create INSERT trigger which trigger the process once data insert into table. 

Following are step(s):

1) Create a INSERT trigger on table

2) In the trigger definition, write the logic to stop the SQL Agent Job(s) based on Insertion if it is running.

USE msdb;
GO

IF EXISTS (
		SELECT 1
		FROM INSERTED
		)
BEGIN
	EXEC dbo.sp_stop_job N'YourJobName';
END
GO

SQL Server Issue - SQL Server Database struck in Recovery Mode

Scenario: We can not restore the same with latest backup because the DB is critical and there is no latest backup to restore

.

Following are the suggestions to handle this situation:

1) If you are able to see .Mdf file of the database then copy this mdf in other folder.

2) create database in some other instance with this mdf file.

3) Review this new database & take backup.

4) Restore it in your required instance.

5) Other option is to restore the database to the nearest point in time.

SSRS Issue - Column not showing in a Tablix

Scenario: 
1) Added a column in Tablix
2) Added an expression into Column
3) But when we preview the report, the column doesn't show in the report.

Following are my suggestions:
1) First, check whether data for required column is coming or not in the dataset/report.
2) First, check the visibility setting for this column.
3) If there is any expression then just set the visibility property to show & check if you are able to see the column.
4) If there is any group for this column then check the visibility property of the group in the grouping pane.
5) For testing purpose, set the visibility property of this group to show.
6) If matrix report then check column-grouping visibility also.
7) Check if column is visible after deployment even it is not showing in SSDT.
8) After Preview, Click on Refresh button so that data will be updated in the report.
9) Recreate the tablix to reproduce the issue.
10) Add new column and see wether you are able to see the column or not.
11) Change the tool for testing purpose e.g. if you have developed report in SSDT then try in Report Builder & Vice Versa.

SSAS Error - KEY NOT FOUND while processing the Cube

1) The KeyNotFound property on the dimension ErrorConfiguration is for snowflake schema.

2) For example, you have employee table which contains empid & department id. Suppose if employee table contains departmentid 8 which does not avialable in department dimension.

3) This situation will generate KeyNotFound event when we process the dimension.

4) When we set KeyNotFound = IgnoreError, Analysis Service will ignore this event.

5) The default is ReportAndContinue which means that we can see the error on the Process window.

6) This error can occur during partition processing, or dimension processing of snowflaked dimensions.

7) ReportAndContinue logs and counts the error

8) It process continues as long as the error count is under the maximum limit.

SSRS Subscription Error Message :Failure sending mail: The operation has timed out.Mail will not be resent

Following are the suggestion(s) which may not be solution but we can try if you are pulling all the data in single page and it is tabular report.

1) Check the Query in SQL Server and optimize it. (if Source is SQL).
2) If in case, Query is fine then check at report definition.
3) For testing purpose, try to use page break.
4) Adding page breaks can improve the performance of the reports because  processing of full report & page break report is lttle different.  
5) SSRS process each page one by one in case of page break.
6) Displaying all data on a single page will typically result in a performance issue. 
7) In full report processing, report will not generate until it completes the report processing. Sometimes, it got time out.
8) Instead, use page breaks to display the data.

Hope this will help to resolve the issue of report processing using email subscription. 

SSAS - Performance issue with Date Dimension

When we dont have a proper date dimenison then SSAS wil give performance issue(s). Performance issue mostly come when we directly use date or datetime column for relationship between dimension & Fact table.

The best solution for overcome this performance issue is:

1) We must have a dateId column in our date dimension in our cube.
2) Our fact table should contain all key columns including DateId & respective fact values.
3) DateId will be key column for date dimension.
4) In this way, when we put filter on date range or any other date related filter, data retrieval will be fast.
5) We can indexing in dateId column if required for faster retrieval of data.

SSAS Tabular Model Issue: The file name(s) would be too long for the destination folder. You can shorten the file name and try again, or try a location that has a shorter parth

This issue occurred when we move/copy the SSAS solution to a different folder. if this is not getting solve by any of the way then the workaround that can be done is to create a new project by importing the project from Tabular instance using "Import from Server" in SQL Server Data Tools in case you have copy of solution deployed in SSAS tabular instance.

SQL Server - How to get a alert or notification if SQL Query is taking more than 5 mins

DECLARE @StartTime DATETIME
DECLARE @EndTime DATETIME
DECLARE @duration INT

--Capture timestamp for start of execution
SET @StartTime = GETDATE()

-- Add the code for your T-SQLscript/ stored procedure
SELECT 'Add the code for your T-SQLscript/ stored procedure'

--Capture timestamp for start of execution
SET @EndTime = GETDATE()
-- Capture the difference in minutes
SET @duration = DATEDIFF(MINUTE, @StartTime, @EndTime)

--Check condition of 5 mins
IF @duration > 5
BEGIN
	SELECT 'Sending email mentioning Query execution taking longer than 5 mins'
		--Code for sending the email
END
GO

SSRS - How to migrate Report Subscriptions from one server to another server

SQL Server always create Subscription jobs in the instance where reporting service is installed/Configured. Or we can say SQL server instance where ReportServer database is configured.

Following are the steps for Report Subscription Migration:
1) We dont need to migrate Report Subscription jobs as part of migration from one server to another.
2) We can take the backup of ReportServer DB and restore to new server for migration.
3) Report server DB contains all subscription definitions.
4) Once you schedule these subscription in new server it will automatically create subscription jobs in SQL Server instance under SQL Server Agent folder==> Jobs folder.
5) Now schedule those Jobs per business requirement
6) Your subscription will start working in new server.

SQL Server - How to reorganize indexes in SQL Server Database

 Reorganizing the indexes consists of two processes

1) Detect the fragmentation: By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

2) Remove Framentation: once you have a complete report on your database fragmentation, you can reorganize the indexes by following steps

1) In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.

2) Expand the Tables folder.

3) Expand the table on which you want to reorganize an index.

4) Expand the Indexes folder.

5) Right-click the index you want to reorganize and select Reorganize.

6) In the Reorganize Indexes dialog box, verify that the correct index is in the Indexes to be reorganized grid and click OK.

7) Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.

SQL Server - How to run a query for all the database(s) in SQL Server Instance

We will use sp_MSforeachdb procedure which is an undocumented procedure that allows you to run the same command against all databases. Below is the sample code:

DECLARE @command VARCHAR(2000)

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE [?] SELECT [name] FROM sys.database_principals WHERE type in (''U'',''G'') 
AND name LIKE ''%$''  END '

EXEC sp_MSforeachdb @command

SSRS - Representation of data in Parent child relationship

For showing the data in Parent Child relationship, we need to create grouping. Let's take an example of Product Category, Product Sub Category, Product & Order Quantity(QTY). 

Following are the steps:
1) First create a parent group for "Product Category"
2) Create a child group for "Product SubCategory" under parent group for "Product Category"
3) Create another child group for "Product" under child group for "Product SubCategory"
4) Use SUM aggregation for Order Quantity
5) We can use visibility property of Groups so that we can implement Drilldown functionality
6) In the Drill down functionality, We can see summay data for parent group and other child groups will be hidden.
7) Once we click on (+) sign then it will show data for its immediate child group.
8) Below is the screenshots:

a) Parent Child Grouping for Product Category, Product SubCategory, Product


b) Then you need to set the visibility property of child group toggled by its parent group.

c) First Parent Group

d) Second Parent Group

d) Third Level

SQL Server - Reason for stored procedure not returning any data

Following could be reason(s):

1) Multiple Exit Point in the stored procedure. We may have RETURN statement before stored procedure forming final output

2) All data is getting filtered. It may be due to wrong WHERE condition or JOIN condition.

3) Error Occurred and execution went to CATCH block

4) Data COLLATION issue

5) Data mismatch issue

6) Access issue with some table(s) or schema

Representation of data in Table & Matrix & Correlation

The objective of this article is provide a visualization for data in tablix as well as matrix and how we can correlate it. 

Tablix:

1) Tablix shows data at row level. 

2) We can group the data at row level.

3) We can show Total at the end or begining of Row Group

4) Mostly used for displaying detail data

Matrix:

1) Matrix shows data at row level as well as Column level 

2) We can group the data at row level as well as column level

3) We can show Total at the end or begining of Row Group

4) We can show Total at the end or begining of Column Group

5) Mostly used for displaying summary data

Below is the report showing Sales information at Region and Year wise. We are showing this informaiton in Table as well as Matrix. We can easily correlate information between Tablix & Matrix visualization.

 

SSRS: How to put filter at visual level

Here is the sample Sales Report which shows Region & Year wise Sales Amount.
 

If we want to put filter at tablix level then right click on tablix, go to tablix property then filter and set the filter for Year e.g 2023. Now run the report. 
 

We can see tablix level filter is working fine.
 
This advantage of report item level filter is that the information filtered out need to show in Dashboard but not required in the particular visualization.

SSRS Reporting - How to add a Reference Line in Line Chart

Sometimes, requirement comes to add a reference line in the line chart which will act as benchmark to measure the progress. Here is a workaround to show a reference line in the Line Chart in SSRS. Below is the sample report:

1) Create a Parameter named "BenchmarkValue" and provide some available values based on business requirement.

2) Add this newly created  "BenchmarkValue" parameter in the Value section of the chart

3) Once we added the  "BenchmarkValue" in the chart, Chart will display multiple lines for this measure.

4) Change the formatting of Reference Line so that we can easily differentiate  Reference line and data line.

 
5) Go to the Series property of  "BenchmarkValue" then Legend then check the box -- "Do not show this series in a legend".

6) Now run the report.

7) select another value for  "BenchmarkValue" and view the report..

High Level Architecture of Web & Database Server with Load Balancing & High Availability

1) Web application would be hosted on 2 web servers -Web Server 1 and Web Server 2.

2) Both the web server(s) would be configured with load balancing to distribute the load as well as fault tolerance in case of any server failure.

3) Database would be configured with SQL server high availability Always-On feature.

4) There would be 2 instance of database - Primary & Secondary as part of HA Always-On feature.

5) It would keep database active on primary node with Web Server(s).

6) In case of database failure, database would be available from secondary node.

7) Once the primary is up, database would be switched back to Primary node.

8) If any web server fails, a load balancer immediately redirects its workloads to a backup server, thus mitigating the effect on end users.

9) Load Balancer also maximize network capacity and ensuring high performance to end user(s).

SQL Server Agent - How to enable SQL Agent Job using T-SQL script

USE MSDB;
GO

--Declaring Variable
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @Cnt INT
DECLARE @Min INT
--Declaring Table Variable
DECLARE @SQLAgentJob (
	Id INT IDENTITY
	,Job_Id UNIQUEIDENTIFIER
	)

-- Setting minimum value to 1
SET @Min = 1

--Inserting all the Job IDs which are disabled for my application MyAppProcessing
INSERT INTO @SQLAgentJob
SELECT job_id
FROM msdb.dbo.sysjobs
WHERE enabled = 0
--Remove this filter in case need  to enable all the available jobs in SQL instance
AND Name LIKE 'MyAppProcessing%'

-- Count of total Job disabled
SELECT @Cnt = COUNT(Job_Id)
FROM @SQLAgentJob

--While Loop
WHILE @Min <= @Cnt
BEGIN
	SELECT @job_id = job_id
	FROM @SQLAgentJob
	WHERE ID = @Min

	EXEC msdb.dbo.sp_update_job @job_id = @job_id
		,@enabled = 1

	SET @Min = @Min + 1
END

SQL Server Agent - How to disable SQL Agent Job using T-SQL script

To do unattended tasks like if error comes need to disable the job. In this case, we need to disable the job through T-SQL script ony. We can use this script in Job's step, if any job step failed then this script wil disable the job. This is very useful in keeping database safe from unnecessary actions from SQL Agent Job etc.

USE MSDB;
GO

--Declaring Variable
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @Cnt INT
DECLARE @Min INT
--Declaring Table Variable
DECLARE @SQLAgentJob (
	Id INT IDENTITY
	,Job_Id UNIQUEIDENTIFIER
	)

-- Setting minimum value to 1
SET @Min = 1

--Inserting all the Job IDs which are enabled for my application MyAppProcessing
INSERT INTO @SQLAgentJob
SELECT job_id
FROM msdb.dbo.sysjobs
WHERE enabled = 1
AND Name LIKE 'MyAppProcessing%'

-- Count of total Job enabled
SELECT @Cnt = COUNT(Job_Id)
FROM @SQLAgentJob

--While Loop
WHILE @Min <= @Cnt
BEGIN
	SELECT @job_id = job_id
	FROM @SQLAgentJob
	WHERE ID = @Min

	EXEC msdb.dbo.sp_update_job @job_id = @job_id
		,@enabled = 0

	SET @Min = @Min + 1
END

SSRS - How to use filter in Report Dataset

We can use the filter by using the filter property of the dataset. This is very useful when we need to pull data from Non relational data sources where we can not put filter while pulling the data for the report. We can filter the dataset by providing static value or we can use parameter. Below is the sample example where we are filtering data with parameter:

1) SQL dataset without any filter condition:
     

2) Report without filter:
     


3) Dataset with Parameter:
     


4) Report with filtered data:

     

SQL Server High Availability - How to capture Server(s) status whether it is online or offline using T-SQL programming

 Below is an example to capture Server(s) status whether it is online or offline. We are using XP_CMDSHELL extended stored procedure to ping and get response from the server. We are using a table in which we are maintaining list of server(s) to which we need to monitor the status. Whenever we run this code, table will be refreshed with latest status. This is very useful and helpful when we need to develop affordable high availability solution with in SQL Server.

--Create a table which will contain list of ServerName that needs to be monitored with their status & timestamp
CREATE TABLE [dbo].[HA_Server_Health_Monitoring] (
	ID INT
	,IDENTITY
	,[Fully_Qualified_Server_Name] NVARCHAR(500)
	,[IPAddress] NVARCHAR(500)
	,[Health_Flag] NVARCHAR(500)
	,[Last_Refresh_Date] DATETIME
	)

--Cleaning of table
TRUNCATE TABLE [dbo].[HA_Server_Health_Monitoring]

-- Populating Server details
INSERT INTO [dbo].[HA_Server_Health_Monitoring] (
	[Fully_Qualified_Server_Name]
	,[IPAddress]
	,[Health_Flag]
	,[Last_Refresh_Date]
	)
VALUES (
	'IND-SQL-01'
	,'127.0.0.1'
	,NULL
	,NULL
	)

INSERT INTO [dbo].[HA_Server_Health_Monitoring] (
	[Fully_Qualified_Server_Name]
	,[IPAddress]
	,[Health_Flag]
	,[Last_Refresh_Date]
	)
VALUES (
	'IND-SQL-02'
	,'127.0.0.2'
	,NULL
	,NULL
	)

INSERT INTO [dbo].[HA_Server_Health_Monitoring] (
	[Fully_Qualified_Server_Name]
	,[IPAddress]
	,[Health_Flag]
	,[Last_Refresh_Date]
	)
VALUES (
	'IND-SQL-03'
	,'127.0.0.3'
	,NULL
	,NULL
	)

--Declaring Variables
DECLARE @Cnt INT
DECLARE @Min INT
DECLARE @CMDString NVARCHAR(2000)
DECLARE @FLAG BIT
DECLARE @IPAddress NVARCHAR(100)
DECLARE @ErrorMessage NVARCHAR(100)

--Setting Minimun value as 1
SET @Min = 1

-- Count of total server(s)
SELECT @Cnt = COUNT(*)
FROM [dbo].[HA_Server_Health_Monitoring]

--- Starting WHILE loop
WHILE @Cnt >= @Min
BEGIN
	SELECT @IPAddress = IPAddress
	FROM [dbo].[HA_Server_Health_Monitoring]
	WHERE ID = @Min

	--Making command string
	SET @CMDString = 'PING' + @IPAddress

	--Executing extended stored proccedure and capturing the result into variable
	EXEC @FLAG = xp_cmdshell @CMDString
		,no_output

	-- 0 -- Success
	-- 1-- Failure
	-- Updaing Status with timestamp
	UPDATE [dbo].[HA_Server_Health_Monitoring]
	SET Health_Flag = CASE 
			WHEN @FLAG = 0
				THEN 'Online'
			WHEN @FLAG = 1
				THEN 'Offline'
			END
		,Last_Refresh_Date = GETDATE()

	IF @FLAG = 1
	BEGIN
		SET @ErrorMessage = 'Server is offline. Need immediate attention & action'
	END

	SET @Min = @Min + 1
END

SSRS Use Case - Role based display of Charts in the Reports

The requirement is to display some of the common charts to everyone  + specific charts to specific team only in the same reporT. For example, Finance team will be able to see common Charts + Finance Specific charts only. similar way, Sales team, Marketing Team.

We need to design the report in such a way that some of the charts should overlap the other chart and need to set the visibility properties based on the logic or way you are displaying charts on role basis.

For Example We have 3 charts Chart -A, Chart- B , Chart -C

Chart A - All user
Chart B - Only Finance group
Chart C - Only Sales Group

In this case, Chart A position will be fixed. Chart B & Chart C should overlap in the design in same place. Set the visibility property for charts based on role. so

When Finance Group memeber runs the report. User will see

1) Chart A
2) Chart B

When Sales Group memeber runs the report. User will see

1) Chart A
2) Chart C in the place of Chart B

SQL Server - How to pull data from Source table to Target table ensuring it will pull only data which is not present in Target table.

To achieve the requirment of loading data from Source table to Target table  with ensuring it will pull only data which is not present in Target table, we need to use JOIN and WHERE Condition to achieve it. Here we are going to use LEFT JOIN & WHERE condition with IS NULL.

Sample code:

--Source Table
SELECT *
FROM MyProduct

--Target Table
SELECT *
FROM MyProductHistory

--INSERT INTO MyProductHistory
SELECT P1.*
FROM MyProduct P1
LEFT JOIN MyProductHistory P2 ON p1.ProductID = p2.ProductID
WHERE p2.productID IS NULL

Query Execution Explaination:
1) This query will pull all the matching ProductID from MyProduct table (Source table) and MyProductHistory table (Target Table)

2) This query will pull all the new products from MyProduct table (Source table) 
3) At last, It will filter data based on Products not available in MyProductHistory table (Target Table)
4) In this way, Output will be only products which are not available in MyProductHistory table (Target Table)