MCTS 70-448: Too bloated?

Over the past few years i've really started to embrace the power of SSIS and to a lesser extent SSRS. I think with a lot of these things, its only really possible to get engaged in a feature of SQL Server if you have a practical/real world use for the technology rather than just working through the tutorials and examples on the web. However, one area where I don't even have a basic knowledge of is SSAS mainly due to the fact that i've not had cause to dive in and get my hands dirty with it.

At the moment, i'm looking to complete the various SQL MS exams and have been successful in achieving certification in my "comfort zones" of Development and Administration. The next obvious step to complete the set is to take on the Business Intelligence exam MCTS 70-448 and its here we I have a slight issue. In my mind, this exam seems to be a "catch all" for the features of SQL Server that can be described as BI. Of course, i'm not saying that they aren't BI or indeed their principal features are BI but it seems to me that, particularly with the case of SSIS, the range of features/uses is so large that it warrants an exam all of its own.

I'm keen to demonstrate my knowledge of SSIS/SSRS and the MS certification program is a way of proving to others that you do have some level of expertise in these areas. However, SSAS seems to be such a large topic (akin to the Database Engine) that I think its unfair to expect users of SSIS/SSRS to have to know it to a working level in order to earn the 70-448 certification.

I am working through the self paced training kit for 70-448 but am finding it difficult to really get engaged in the SSAS section. Of course, reading through the material and the completing the exercises do help and I feel I am much further ahead than at the start of the week but I'm not sure i'd feel comfortable implementing an SSAS solution from scratch.

But i'll perserve and hopefully achieve certification soon but I hope in the future Microsoft consider realigning their exam content soon.

SQLCLR: Publish SSRS reports from CLR stored procedure

Over the past few years, an task i've come across on a frequent basis is how to produce reports from within an application. The scenario is typically something like: SSIS Package imports data, transforms it and then produces output into a table. The user then wants the data to be rendered into excel and made available to them.

I'd really like to see a system T-SQL stored procedure to be able to do this- something along the lines of sys.sp_renderreport @reportname, @format, @location etc

However, for now this doesn't exist so its down to you to make it happen with the tools at your disposal. SSRS provides the reportexecution2005 webservice which you can use to render reports and its fairly straightforward to call this from a CLR stored procedure.

Here is the source code for the CLR stored procedure:

using System;
using System.Data;
using System.Net;
using System.IO;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Collections.Generic;
using System.Xml.Serialization;
using RenderReport.ReportExecution2005;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RenderReport(string reportPath, string filePath, string format, SqlXml reportParams)
{
ReportExecutionService res = new ReportExecutionService();
res.Url = "http://DEVSERVER/reportserver_DEV/reportexecution2005.asmx?wsdl";
res.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Prepare Render arguments
string historyID = null;
string deviceInfo = null;
string extension;
string encoding;
string mimeType;
Warning[] warnings;
string[] streamIDs;

int i = 0;

XmlDocument paramXML = new XmlDocument();
paramXML.LoadXml(reportParams.Value);
var paramNodes = paramXML.SelectNodes("//reportparams/param");

ParameterValue[] prms = new ParameterValue[paramNodes.Count];

foreach (XmlNode param in paramNodes)
{
prms[i] = new ParameterValue();
prms[i].Label = param.Attributes["label"].Value;
prms[i].Name = param.Attributes["name"].Value;
prms[i].Value = param.Attributes["value"].Value;
i++;
}

res.LoadReport(reportPath, historyID);

// set params if we have any
if (i > 0)
{
res.SetExecutionParameters(prms, "en-us");
}

byte[] results = res.Render(format, deviceInfo, out extension, out mimeType, out encoding, out warnings, out streamIDs);

// Open a file stream and write out the report
FileStream stream = File.OpenWrite(filePath);
stream.Write(results, 0, results.Length);
stream.Close();
}
};


A couple of things to note,
1) ReportParams need to be set in this format:
<reportparams><param name="MyName"label="My Name"value="Richard"</reportparams>

2) You will need to follow the steps in this KB article.
3) You will find that you need to deploy this using the EXTERNAL ACCESS permission set.
4) EXTERNAL_ACCESS CLR routines will be executed as the SQL Service Account so it will need appropriate permissions if you want to save reports to a remote location.

T-SQL: Check for invalid views - remember to refresh!

Recently on the MSDN forums, I came across a post which wanted to find orphaned views - those which have had their base tables changed/removed so that they are no longer valid.

I suggested running sp_refreshview 'Schema.View' for each view which would highlight any which aren't able to be resolved. However, the poster came back with a solution which made use of the INFORMATION_SCHEMA view VIEW_TABLE_USAGE. This was something that had slipped under my radar in the past so i thought i'd take a look into it to and see how useful it could be.

On the face of it, the query posted on the forums seemed to be tickety boo! However, on closer inspection, i noticed that it didn't have quite the behaviour i expected. See the following example for an explanation.


-- create some test objects
CREATE DATABASE [Test]
GO
USE [Test]
GO
CREATE SCHEMA schemaa
GO
CREATE SCHEMA schemab
GO
CREATE TABLE schemaa.tbla
(
int1 INT,
int2 INT
)
GO
CREATE TABLE schemaa.tblb
(
int1 INT,
int2 INT
)
GO
CREATE VIEW schemaa.vwa
AS
SELECT a.*
FROM schemaa.tbla a
INNER JOIN schemaa.tblb b
ON a.int1 = b.int1
GO
-- check the results in INFORMATION_SCHEMA.
-- Perfect! 2 rows to appear for each table in SchemaA.vwA
SELECT *
FROM information_schema.VIEWS v
LEFT OUTER JOIN information_schema.view_table_usage vtu
ON v.table_catalog = vtu.table_catalog
AND v.table_name = vtu.view_name
AND v.table_schema = vtu.table_schema
-- now remove one of the tables
DROP TABLE schemaa.tbla
GO
-- check the results in INFORMATION_SCHEMA.
-- Perfect! only 1 row appears in SchemaA.vwA as expected
SELECT *
FROM information_schema.VIEWS v
LEFT OUTER JOIN information_schema.view_table_usage vtu
ON v.table_catalog = vtu.table_catalog
AND v.table_name = vtu.view_name
AND v.table_schema = vtu.table_schema
GO
-- recreate the dropped table
CREATE TABLE schemaa.tbla
(
int1 INT,
int2 INT
)
GO
-- sp_refreshview
EXEC Sp_refreshview 'SchemaA.vwA'
GO
-- check the results again in INFORMATION_SCHEMA.
-- Perfect! 2 rows to appear for each table in SchemaA.vwA
SELECT *
FROM information_schema.VIEWS v
LEFT OUTER JOIN information_schema.view_table_usage vtu
ON v.table_catalog = vtu.table_catalog
AND v.table_name = vtu.view_name
AND v.table_schema = vtu.table_schema
GO
-- this time change schemas of objects
ALTER SCHEMA schemab transfer schemaa.tbla
ALTER SCHEMA schemab transfer schemaa.tblb
ALTER SCHEMA schemab transfer schemaa.vwa
GO
-- check the results in INFORMATION_SCHEMA.
SELECT *
FROM information_schema.VIEWS v
LEFT OUTER JOIN information_schema.view_table_usage vtu
ON v.table_catalog = vtu.table_catalog
AND v.table_name = vtu.view_name
AND v.table_schema = vtu.table_schema
-- again 2 rows BUT the VIEW_DEFINTION still shows references to the old schemas.
-- so you could be fooled to thinking that this view is still valid when it isn't
GO
-- try and run refreshview
EXEC Sp_refreshview 'SchemaB.vwA'
-- ha ha, an error!
GO

So you can't rely on INFORMATION_SCHEMA to be a true indicator of whether your views are valid/orphaned or not. Instead, the safest way is to run sp_refreshview to find any that are unable to be resolved.