SSRS Show Dates on Chart with Zero Counts

The users wanted a chart that had a count by day of a set of events. Some days did not have events and they wanted to see these days on the chart even though the days had a zero count. I tried to accomplish this with a CTE but I couldn’t get the CTE to return the data I wanted. So instead I looked for an SSRS solution.

My output looked like so:
2014-01-30_10-27-47

So then in SSRS I created a stacked bar chart, and did the following.

  1. Right Click on the Horizonal Axis and click Horizontal Axis Properties
  2. Change the Axis Type to Scalar (Number/Dates)
  3. Change the Interval to 1
  4. Change the Intervalt Type to Number
  5. Click OK

Executed the report and it is working as planned.

2014-01-30_10-31-17

Advertisements

CLR20r3 SSMS

Hit this wild problem today.

2014-01-15_10-45-48

Turns out the problem was caused by the Print Spooler Service. The Print Spooler service was disabled. I enabled it and started it and the problem went away.

Server Manager was also crashing with the same error.

SQL Server Replication Health Check

Here are some scripts I use to check on replication.

Undelivered commands in Distribution database

--Undelivered Commands in Distribution Database
Select		da.publication, sum(ds.UndelivCmdsInDistDB) UndelivCmdsInDistDB
from		[distribution].[dbo].[MSdistribution_agents]da
			join [distribution].[dbo].[MSarticles] msa on 	msa.publisher_db = da.publisher_db
			join [distribution].[dbo].[MSdistribution_status] ds ON ds.agent_id = da.id and ds.article_id = msa.article_id and ds.agent_id = da.id
GROUP BY	da.publication

With Article Details

--Undelivered Commands in Distribution Database - Article Details
Select		da.publication, msa.article, msa.article_id, sum(ds.UndelivCmdsInDistDB) UndelivCmdsInDistDB, GETDATE() QueryTime
from		[distribution].[dbo].[MSdistribution_agents]da
			join [distribution].[dbo].[MSarticles] msa on 	msa.publisher_db = da.publisher_db
			join [distribution].[dbo].[MSdistribution_status] ds ON ds.agent_id = da.id and ds.article_id = msa.article_id and ds.agent_id = da.id
GROUP BY	da.publication, msa.article,msa.article_id
ORDER BY UndelivCmdsInDistDB DESC

Hot Articles
This script will show you what articles have the most commands at the moment.

--Hot Articles
--Command count for all articles
SELECT COUNT(1), c.article_id, a.article
FROM distribution.dbo.MSrepl_commands c JOIN distribution.dbo.MSarticles a on c.article_id = a.article_id
GROUP BY c.article_id, a.article
ORDER BY 1 DESC

Big Ticket Batches
This script will show you your large batches waiting to be distributed.

--Big Ticket Batches
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT [xact_seqno], count(1)
FROM [distribution].[dbo].[MSrepl_commands]
GROUP BY [xact_seqno]
HAVING count(1) > 1
ORDER BY count(1) DESC

Inspect Specific Batch
You can use the following procedure to inspect a batch. You can get a batch number from the Big Ticket Batches query above.

--Plug in the xact_seqno for the batch into this Stored Procedure
EXEC distribution.dbo.sp_browsereplcmds
	@xact_seqno_start = '0x000223DE000147D8001B',
	@xact_seqno_end ='0x000223DE000147D8001B'

Dynamic USE statement

If you have tried to excute dynamic SQL on multiple databases at once then you have probably had problems with the USE statement. Here is how I worked around it. You can use the sp_executesql stored procedure and provide it database context like below.


DECLARE @sql VARCHAR(MAX)
SET @sql = 'YOUR AWESOME SQL STATEMENT HERE';
EXEC(@dbname + '..sp_executesql N''' + @sql + '''');

The trickiest part of this solution for me was handling the quotes in the EXEC statement. You can play around with the command by using the PRINT statement to work on the command.


DECLARE @sql VARCHAR(MAX)
SET @sql = 'YOUR AWESOME SQL STATEMENT HERE';
PRINT(@dbname + '..sp_executesql N''' + @sql + '''');