Blank Pages in SSRS Reports PDF Render

Recently I faced an issue within SSRS where a Tabular Report in SSRS report got blank pages in-between the rows when rendered/downloaded as PDF.

I tried to read about it, but usually people think it’s about Keeping Header in the report or Portrait/Landscape orientation of the report. But its not!

Report must be having dynamically generated columns or variable length of the columns. When an SSRS report is rendered, it usually keep additional space from right end of body same as when Report was in design mode.

When columns are generated in report render extra space taken is converted into blank pages, but its nothing but extra space on right side report body.

1. Decrease the column length in Design Mode
2. Change report orientation to landscape (if not already), so it takes more horizontal length
3. Fix report column names (instead of dynamically generated) if possible
4. Create new report for more information and decrease overall horizontal length of report

Use any of the above options to get this fix. Unfortunately its not a permanent kind of fix but this is all we got!

Let me know if you come across this kind of situation.

Execute one query in all SQL Server databases at once

We come across situations like searching for a table in all the databases in one server, or executing any kind of query to all the databases. In-fact sometimes we want to write queries for all the databases with small twist.

For example, I wanted to search for a table name if it used in any View/StoreProc definition within any of the database before I delete the table.

Following query does the same:

DECLARE @cmd VARCHAR(1000)
	,@search_string VARCHAR(200)


SET @cmd = 'SELECT DISTINCT ''?'', s.name AS Schema_Name, o.name AS Object_Name, o.type_desc 
			FROM [?].sys.sql_modules m INNER JOIN [?].sys.objects o ON m.object_id = o.object_id 
			INNER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id 
			WHERE m.definition Like ''%' + @search_string + '%'''

-- Uncomment the following if you have problems with your command and want to see the command
-- PRINT @cmd


--Run for every database on the server
EXEC sp_msforeachdb @cmd

Above kind of query can help you with :

a. Find a table name in complete SQL Server Databases

b. Prepare complete query which will be run on all the databases

c. Update something in all the databases at once

d. Example used with Dynamic query can help achieve many ad-hoc work done on SQL Server