Ok time to get side tracked sort of. In my last post I talked about inspecting the Reporting Services web service to gather information about reports and possibly even building that out to execute reports. In order to drive the automatic execution of reports we need a manner in which to hold report names and locations, along with parameters for those reports. In this post I will go over a database I designed to handle feeding this data to an SSIS package to execute reports. It’s worth noting that this structure can be used to feed the data driven subscription engine in SQL Server Reporting Services Enterprise Edition. (You will still be lacking the event driven ability). I'm sure I'll also manage to sneak in a little of my database development and design philosophy and possible a PowerShell script or two I use to automated the build process. Ok let’s start out at the end:
Here is the DB that we will end up with.
To do this we need to create 4 tables. One to hold the report name and its location, one to hold Parameter Names but not values, One to hold the linkage between what reports get what parameters, and finally one to hold the actual data for an given scheduled run of a report ting this data all the way back to an individual report and parameter information.
Here are the scripts, I'll talk a little bit about each one.
Script 0000
use
master
go
if
@@SERVERNAME = 'YOURDEVELOPMENTDBINSTANCE!'
if exists (select * from sys.databases where name ='steves')
begin
print 'killing connections!'
declare @sqlString varchar(100)
while (exists (select spid from sys.sysprocesses s inner join sys.databases db on db.database_id=s.dbid where db.name='steves'))
begin
select @sqlString='kill '+cast(max(spid) as varchar(10)) from sys.sysprocesses s inner join sys.databases db on db.database_id=s.dbid where db.name='steves'
print @sqlString
execute(@sqlString)
end
begin
print 'I hope you know what you are doing!'
drop database steves
print 'steves database dropped!'
end
end
Ok first off you may wonder why I'm dropping the DB in this first script it’s because this is my development methodology. I guess it’s almost test driven database development but with a few differences. I like to start from scratch testing all of my build scripts that get me to the current production version, then I like to run any of the scripts that add metadata to the system, next I run any data loading scripts or SSIS packages necessary to get the system in a active state similar to production, finally I like to add any of the new development scripts to the system. To accomplish this I normally use a combination of SubVersion, PowerShell, and SQL Server Agent. (Maybe in a future post I'll go into my PowerShell,SubVersion, SQL Server interactions, to be honest I would like to move this in Visual Studio and Source Safe but haven't had the time to do a lot of detail looking around) Ok long story longer, If the I'm in the right environment I kill the connections and drop the DB, then create a new DB and schema. Otherwise this script does nothing.
Script 0001
use
master
if
not exists (select * from sys.databases where name ='Steves')
begin
create database steves
print 'database "steves" created!'
end
else
begin
print 'database "steves" already exists'
end
go
use
steves
go
if
not exists(select * from sys.schemas where name = 'RD')
begin
execute ('create schema rd authorization dbo')
print 'schema "rd" created!'
end
else
begin
print 'schema "rd" already exits!'
end
Simple enough create a new database and new schema if they don't exist.
Script 0002
use
steves
go
if
not exists (select * from sys.tables where name = 'reports' and SCHEMA_NAME(schema_id)='rd')
begin
create table rd.reports
(
report_id
int identity(1,1) not null
,report_name varchar(150) not null
,report_location varchar(255) not null
,constraint pk_reports_01 primary key (report_id)
)
print 'table rd.reports created!'
end
else
begin
print 'table rd.reports already exists!'
end
Ok, this script just creates a table nothing special. It does keep up the theme of providing information back based on success of the script. (I get enough information back from a failure, and if we have a need to handle them in a special manner we can introduce try/catch blocks) This is the report table, the report name is a user defined name for a report it can match the actual report name but does not need to. The report location field with hold the location off of the ReportServer/ URL to the name including the name actual rdl file.
Script 0003
use
steves
go
if
not exists (select * from sys.tables where name = 'parameters' and SCHEMA_NAME(schema_id)='rd')
begin
create table rd.parameters
(
param_id
int identity(1,1) not null
,param_name varchar(20) not null
,param_data_type tinyint not null
,constraint pk_parameters_01 primary key (param_id)
)
print 'table rd.parameters created!'
end
else
begin
print 'table rd.parameters already exists!'
end
Once again pretty straight forward table create with a few print statements. This table holds a name for each parameter; this name should match what the name of the parameter in the reports will be. This requires a semi high level of standardization and correlation between report writing and parameter entry into this system. We are not locked into this and if we need to specify a different name for the same parameter we can do so at the report/parameter cross reference level but this is the going to the default name used. You also see param_data_type which holds the data type that this parameter represents, because all parameter data is going to be held in a varchar field it is useful to know what the data type should be. The last thing I want to point out it deals with database standards is the primary key constraint it is named with pk to signifying the constraint is a primary key. Followed by the table name and then the ordinal position of the column(s) that are in the primary key. The reason I am pointing this out is that I do not believe good database design happens by chance, it takes in my opinion forethought and planning to come with a good design.
Script 0004
use
steves
go
if
not exists (select * from sys.tables where name = 'reportsParametersXrefs' and SCHEMA_NAME(schema_id)='rd')
begin
create table rd.reportsParametersXrefs
(
xref_id
int identity(1,1) not null
,report_id int not null
,param_id int not null
,alternate_param_name varchar(40) null
,constraint pk_reportsParametersXrefs_01 primary key (xref_id)
,constraint un_reportsParametersXrefs_02_03 unique (report_id,param_id)
,constraint fk_reportsParametersXrefs_02 foreign key (report_id) references rd.reports(report_id)
,constraint fk_reportsParametersXrefs_03 foreign key (param_id) references rd.parameters(param_id)
)
print 'table rd.reportsParametersXrefs created!'
end
else
begin
print 'table rd.reportsParametersXrefs already exists!'
end
Alright now we are getting somewhere. First I want to point out the continuation of the constraint naming, once again I am not saying this is the only way to do it, I'm just saying think about everything in your database in the planning stages it will make its use and maintenance much easier. Ok this table basically just links reports to parameters, it also gives you a chance to rename the parameter name to one that matches the value in the report, this will be key in the overall Report Delivery Engine we are building.
Script 0005
use
steves
go
if
not exists (select * from sys.tables where name = 'schedules' and SCHEMA_NAME(schema_id)='rd')
begin
create table rd.schedules
(
schedule_num
int not null
,xref_id int not null
,param_value varchar(255) not null
,constraint pk_schedules_01 primary key (schedule_num,xref_id)
,constraint fk_schedules_02 foreign key (xref_id) references rd.reportsParametersXrefs(xref_id)
)
print 'table rd.schedules created!'
end
else
begin
print 'table rd.schedules already exists!'
end
Finally we are ready to pair the data with the report/parameter information. Notice how the parameter value is held in a varchar field. This is done to make this system as flexible as possible. I toyed with the idea of creating a parameter value column for each data type and making those fields nullable and then enforcing the business logic that requires one of the parameter values to be filled with data via a trigger but this seemed overly complex and didn't truly buy me any additional data integrity. I can still enforce domain integrity via a trigger that looks back to the parameter table but I'm not too sure its necessary but I will keep that idea ready just in case.
Script 0006
use
steves
go
create
view rd.reportSchedules
as
select
r
.report_name
,r.report_location
,p.param_name
,t.name
,rpx.alternate_param_name
,s.param_value
,s.schedule_num
,r.report_id
,p.param_id
,rpx.xref_id
,t.system_type_id
from
rd
.reports r
inner join
rd
.reportsParametersXrefs rpx
on
r
.report_id=rpx.report_id
inner join
rd
.parameters p
on
p
.param_id=rpx.param_id
inner join
rd
.schedules s
on
s
.xref_id=rpx.xref_id
inner join
sys.types t
on
t
.system_type_id =p.param_data_type
go
if
(@@ERROR=2714)
begin
print 'view rd.reportSchedules already exists!'
end
else
if exists (select * from sys.views where name = 'reportSchedules' and SCHEMA_NAME(schema_id)='rd')
print 'view rd.reportSchedules created!'
else
print 'error look above'
Finally we have a view to make all of these identity filled normalized tables provide some useful data to the end user. This view just shows all of the value fields along with only one copy of each of the idents across all four tables. This script is also in a different format where I create the object and wait to see what the error value is if any. The reason for this is that the create view statement must be the only statement in its batch. I'm not exactly sure why this is but it is what it is.
What does all this get us? Well nothing, but wasn't that a fun 30 minutes, Seriously this gives a building block, and it gives us a system to record and retrieve parameters for an multiple reports and multiple schedules for any given report. There are few more scripts that can make this an solution for providing data to the Data Driven subscription area on Enterprise Edition of reporting services but I'll save those for another post. (Hint: 1 proc for retrieving data, 1 proc for updating key parameters like begin date and end date, 1 proc for dynamically feeding data to this set of tables prior to a report running).
Ok as a little bonus here is the PowerShell script I use to automate my build process. Basically you pass it a directory and a SQL Server instance and it spins through the directory executing any script that starts with four numeric values in order from lowest to highest. I normally schedule this to run from SQL Agent at a set time every week.
#-----------------------------------------------------------------------------
#
#SFIBICH
#7/2/08
#Version 1.0
#versionControlSQLScript.ps1
#This script looks for files in the directory and executs them in order
#based on the script having an .SQL file extension and the first for characters of the
#name being numeric. It will execute the scripts in ascending order.
#
#-----------------------------------------------------------------------------
#
#$ars[0] - relative path to the folder
#args[1] - the name of the server that the code will be executed on.
#$a = path to this file when executed
#serverName - the name of the server this code will be executed on defaults to DEV
#
#-----------------------------------------------------------------------------
$a=$MyInvocation.InvocationName
$serverName ="localhost"
if ($args[1].length -gt 0) {
$serverName=$args[1]
}
"ServerName:"+$serverName
if ($args[0].length -gt 0) {
$fileLocation=$args[0]
if ($fileLocation -eq '?') {
get-content $a | ? {$_.length -gt 0} | ? {$_.substring(0,1) -eq '#' }
exit
}
else {
[reflection.assembly]::LoadWithPartialName("'Microsoft.VisualBasic")
gci $fileLocation | ? {
$_.name -match "sql" -and [Microsoft.VisualBasic.Information]::isNumeric($_.name.trim().substring(0,4)) -eq "TRUE"} |
sort-object @{Expression={[Int](([String]$_).trim().substring(0,4))};Ascending=$true} |
% {$_.FullName; sqlcmd -S $serverName -E -e -i $_.FullName}
}
}else{
write-warning('Default Directory required - no work executed')
}