Monday 2 June 2014

Exam 70-461: Querying Microsoft SQL Server 2012

Resources to help when sitting the 70-461: Querying Microsoft SQL Server 2012 Certification Exam.

This is the overview page of the Exam. I strongly suggest going over the Skills Measured section. Ensure you have a good understanding of them. Including all the different options for Triggers, XML, Indexed Views etc.
Exam 70-461 Querying Microsoft SQL Server 2012

The Training Kit book is a great reference resource. In the front it highlights the skills measured and which chapters focus on those. One word of warning, the practice test that comes with the book is far easier than the actual exam. Use it as a guide on how the questions will be asked.
Amazon - Training-Kit-Exam-70-461-Microsoft

Microsoft 70-461 Test Preparation - This is a playlist of 10 videos which will give you a good overview. The videos dont cover everything but its a great start. I suggest even if you are not planning on taking the Exam its a good set of videos to watch to improve your knowledge. 
Playlist of 10 Videos on You Tube

MCSA Certification Prep | Exam 461: Querying Microsoft SQL Server 2012 - This is from Microsoft to give you an overview and tips on what to study on
Microsoft Exam Preperation Guide

What's New in SQL Server 2012 (Part 2 of 13) - New Transact-SQL Enhancements - New Features of SQL 2012 which will of course be asked about during the exam
New Features of SQL 2012 - Querying

The Microsoft Virtual Academy has some good videos as well. 
Querying Microsoft SQL Server 2012 Databases Jump Start

Microsoft Certification Offers - there can be some great discounts or double shot offers that you can take advantage of

My tip would be if you are unsure of an answer rule out what is not right. I found I could easily rule out at least half of the answers by looking for obvious wrong answers. For example if it asks for columns A, B & C make sure all options give this columns, it could be something as simple as that to narrow down the options and if you still don't know at least you will have a higher chance when guessing.

Sunday 27 April 2014

A Fan Asks Mike Rowe For Life Advice… His Response Is Truly Brilliant.

Some great advice from Mike Rowe from Dirty Jobs.
Although at the same time I do think people should aim high but they shouldn't cloud their ambitions with unrealistic criteria that must accompany them.

http://www.lifebuzz.com/mike-rowe/?utm_content=buffera2714&utm_medium=social&utm_source=facebook.com&utm_campaign=buffer

Saturday 29 March 2014

A Gamification view of the future

I did a Gamification course on Coursera a while back and this video was referenced during it. While not the greatest topic it is a great view of what the future may hold and really what is shown doesn't seem so far fetched. Until the last seconds perhaps.

Believe in Gamification! [A Futuristic Short Film…: http://youtu.be/ziHCvpikLh8

Microsofts in 2019

I do enjoy seeing these future vision clips. It does give inspiration and ideas for today.

Microsoft in 2019 [HQ]: http://youtu.be/RWxqSEMXWuw

Tuesday 18 March 2014

The world is one big dataset

This is a TED talk, seeing things like this really do inspire you.

Dan Berkenstock: The world is one big dataset. No…: http://youtu.be/7pVPmmwSeJQ

Sunday 9 March 2014

Search SSRS Datasets in SQL

The XML for SQL Server Reporting Services Reports are stored in the ReportServer Database of the server. Its possible to pull the XML out and then use the contents to find which reports are using which tables etc. This can be very handy if you there is a change to a schema where logic in reports may need to be updated but you dont know which reports need to be looked at.

The below will place the XML into a table so you can use it to filter what you are after.

CREATE TABLE [dbo].[ReportContents](
 [CatalogItemID] [uniqueidentifier] NOT NULL,
 [Type] [int] NOT NULL,
 [Path] [nvarchar](425) NOT NULL,
 [Name] [nvarchar](425) NOT NULL,
 [ItemContent] [xml] NULL
) ON [PRIMARY]

INSERT INTO ReportContents
 (CatalogItemID, [Type], [Path], [Name], ItemContent)


SELECT
 ItemID
 , [Type]
 , [Path]
 , [Name]
 , cast(CONVERT(VARCHAR(MAX), CONVERT (VARBINARY(MAX), [Content])) as xml) As [ItemContent]
FROM ReportServer.dbo.Catalog
WHERE [Type] <> 1     --Folder
   AND [Type] <> 3 --Image


The below queries the table you created, adjusting the value of the @SQLToCheckFor variable will adjust which text to look for within the SQL Datasets of the SSRS XML. Note this is set up for SQL2008, from memory its just a change of the http address from 2008 to 2005 etc.

--Check all the reports to find a bit of SQL

Declare @SQLToCheckFor varchar(500) = 'FACTMemberBalance'--'dPlanClassification' --Enter your bit of SQL here

select MydataSets.CatalogItemID, MydataSets.ReportName , MyDataSets.DataSetName,
MydataSets.CommandText, MydataSets.ReportPath
from
 (
  select
   CatalogItemID,
   [Name] As ReportName,
   [path] as ReportPath
   ,nref.value('@Name', 'nvarchar(255)' ) As DataSetName
   ,nref.query('.') As DataSetXML
   ,nref.value('declare namespace p1="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
     (./p1:Query[1]/p1:CommandText)[1]', 'varchar(max)') As CommandText
   ,nref.value('declare namespace p3="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
      declare namespace p1="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
      declare default element namespace "http://schemas.microsoft.com/AnalysisServices/QueryDefinition";
     (./p1:Query/p3:MdxQuery/QueryDefinition/QuerySpecification/From)[1]', 'varchar(max)') As Cube   
  from ReportContents
  cross apply ItemContent.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
            declare namespace p1="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
  //DataSet') as R(nref)
--A filter can be added here to limit what reports you are looking through
 )MyDataSets
where CommandText like '%' + @SQLToCheckFor + '%'



How Target Figured Out A Teen Girl Was Pregnant Before Her Father Did

A favorite article of mine, was brought to my attention by a friend of mine. Its pretty impressive what Target was able to do but at the same time you have to wonder how far should organisations go to understand their customers.

How Target Figured Out A Teen Girl Was Pregnant Before Her Father Did

Saturday 8 March 2014

Telstra fields 40,000 government data requests

That seems pretty high to me. I understand there are times in emergencies etc but I would like to know a bit more about the rest.

Tuesday 4 March 2014

Westpac using big data to woo customers with offers made to measure

An interesting article on how Westpac is using its data to target communications to its customers.
Nothing really new but it is a bit of a milestone a lot of Business would love to get to.

The below comment caught my eye and made me wonder what their definition is of Big Data and why they would have more than one data warehouse. Maybe its just the initial extracts from the Web that is the "Big Data" component which is then transformed into structured data in a Warehouse?

"Our data sources are growing very fast and customer interactions are growing very fast," Ms Ganschow said. "We know who you are paying. We know where you are shopping and what you are buying. There is a lot of data pouring into our data warehouses."


Saturday 1 March 2014

Microsoft Power BI

Interesting stuff, but like most of these tools you really need to use them first before you can make a call on them.

http://www.techrepublic.com/article/microsoft-power-bi-brings-big-data-to-the-little-guys/

SQLCLR

I found this a useful article on SQL Server Central.
SQLCLRs are a great way of integrating SQL Server with other applications and/or to create better ways of achieving your results.
The Level 2 page is very in depth, I wish I had access to such information years ago when I first started playing with them.
My favorite SQLCLR I created was to trigger the SSRS Web Service so we can schedule and run Reports with variables set in a table. Very handy when you need hundreds/thousands of reports scheduled to run only after certain events complete etc.

http://www.sqlservercentral.com/stairway/105855/