Category Archives: SSRS

by

Dynamic Data Masking keeps playing…keep your hands off my data!

As promised, I have been playing with Dynamic Data Masking and here are some things I have learned.  I downloaded World Wide Importers so I would have a place to play and there were masked columns already included.

This query will show us what has already been masked:

SELECT mc.name, t.name as table_name, mc.is_masked, mc.masking_function
FROM sys.masked_columns AS mc
JOIN sys.tables AS t
 ON mc.[object_id] = t.[object_id]
WHERE is_masked = 1;

Here we can see the column and the table that is being masked and what masking function is being used.

masking 1

This is a great time to talk about the different masking functions and what they do.  The four types in 2016 are Default, Email, Random and Custom String.

Default – For numeric and binary it will show a “0” For a date it will show 01/01/1900 and for strings it will show xxxx’s (more or less depending on the size of the field).

Email – It will expose the first letter of the email address and the suffix at the end of the email (.com, .net, .edu etc.) For example Batgirl@DC.com  would now be bxxx@xxxx.com.

Random – Number randomly generated between a set range. Kind of like the game, “Pick a number between 1 and 10” but for SQL.

Custom String – Lets you get creative with how much you show or cover and what you use to cover (not stuck with just xxxx’s).

Now for fun, let’s create a table that will be masked.

CREATE TABLE SuperHero
(HeroId INT IDENTITY PRIMARY KEY
,HeroName VARCHAR(100)
,RealName VARCHAR(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL
,HeroEmail VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NULL
,PhoneNumber VARCHAR(10) MASKED WITH (FUNCTION = 'default()') NULL);

Let’s add some data that we will want to mask:

INSERT SuperHero (HeroName, RealName, HeroEmail, PhoneNumber) VALUES
('Batman', 'Bruce Wayne', 'batsy@heros.com', '5558675309' ),
('Superman', 'Clark Kent', 'manofsteel@heros.com','5558675308' ),
('Spiderman', 'Peter Parker', 'spidey@heros.com','5558675307' );

SELECT * FROM SuperHero;

and finally we add some low level permissions of people who will look at the masked version of the data:

CREATE USER CommonPeople WITHOUT LOGIN; 
GRANT SELECT ON SuperHero TO CommonPeople;

Now the test to see if CommonPeople have access to see all of our Superhero secrets:

EXECUTE AS USER = 'CommonPeople';
SELECT * FROM SuperHero; 
REVERT;

Try it out and see for yourself how it looks. Now you have experienced Dynamic Data Masking 101 in SQL Server 2016!

The song for this post is Good Charlotte – Keep Your Hands Off My Girl

by

I won’t be late for this, late for that because I have Time Zone Info….

One of the new items in SQL Server 2016 is the super awesome time_zone_info table.  When I heard about it, I started to think about all the cool things that it could help me do.  First, let’s look at the table.

SELECT *
FROM [sys].[time_zone_info]

time_zone_info

Yes, it is 132 rows of magic! Now that we have this super cool table, how do we use it? Let’s pretend that my data is time-stamped in US Mountain Standard Time, but I want to display it in Western Australia Standard Time.  I would do it like this:

SELECT GETDATE() AS GETDATE_Time,
 GETDATE() AT TIME ZONE 'US Mountain Standard Time' AS Mountain_Time,
 GETDATE() AT TIME ZONE 'US Mountain Standard Time' 
   AT TIME ZONE 'W. Australia Standard Time'AS W_Aus_Time;

I am including the GetDate column so you can see that GetDate is using my time zone, but I have to tell it what time zone it is before I can convert it to another one.

time-zone-query

Caution: If I put in the Hawaiian time zone instead of Mountain time on the GetDate, SQL won’t correct me, it will just do the math like a good little system and assume I know what time zone I am using to start.

I am am really loving the new features in SQL 2016! I hope you are enjoying it too!

This posts song is Cleopatra by The Lumineers

by

Yeah, that’s my kind of T-SQL!

I have a Dates table and I really love it. This week I needed to do a calculation where I take my monthly budget, divide it by the number of working days in a month and then times it by the working days for the week. That will give me a weekly budget number without having to store it, plus my company only gives me monthly numbers. I had already figured out the daily number and had the calculation working for a 5 day work week when New Years day made everyone notice that it was 5 and I was cheating. So I added a Column to my Dates table to tell me on any given day the number of working days in that week. My weeks run from Sunday to Saturday. I have a Date in my dates table for both the start of the week and the start of the next week. I have a flag (1,0) that says whether or not a day is a weekday and another for whether a day is a holiday. Using this flags, I can pass in a range of dates and get the number of working weekdays.

UPDATE Dates 
SET WorkDaysInWeek = WeekDays 
FROM Dates D 
INNER JOIN  
(SELECT BegW, SUM(IsWeekDay) - SUM(IsHoliday) AS WeekDays 
FROM Dates 
WHERE FullDate Between BegW AND NextWeekStart 
GROUP BY BegW) W ON D.BegW = W.BegW

—————UPDATE—————

But wait, there’s more. Rob Farley who is on twitter @rob_farley sent me an even better way to do it. It gets rid of the need for a WHERE or GROUP BY because we are using OVER. We hoped it would eliminate the sub-query and it does if our query is only a SELECT, but when I go to do an UPDATE, it says that “Windows functions can only appear in the SELECT and ORDER BY clauses”. Rob suggested I use a CTE instead.  I hadn’t ever used a CTE without joining back to it so he taught me how.

This is the super awesome SELECT:

SELECT BegW,SUM(IsWeekday - IsHoliday) OVER (PARTITION BY BegW) AS WeekDays
FROM Dates

Here is what the UPDATE ended up looking like.

WITH d AS (
SELECT *,SUM(IsWeekday - IsHoliday) OVER (PARTITION BY BegW) AS WeekDays
FROM Dates)
UPDATE d SET WorkDaysInWeek = WeekDays;

The other question that Rob had was what if we have holidays on weekends. This is a great question. At my company, the holiday would be shifted to one of the days of the week, so the counts would still be correct. But if you are in a situation where that is not the case you can change the where clause like this:

WHERE [DayOfWeek] NOT IN (1,7)

As I was writing my where clause, I noticed I did something bad and used a reserved word as a column name. If you happen to make this mistake as well, just make sure to put square brackets [] around the word when you use it.

Huge thank you to Rob for being so kind to help me be better! One of my new goals is to play with OVER and understand how to use it and when. I am also going to be learning more about CTE’s and not having to use joins. Yay! New toys!!!!!

by

Come in closer and learn how to fix SSRS Subscriptions…

Greetings to the Kingdom,

Over the weekend we had a datacenter move and in the the process moved the database for SSRS.  I knew that my subscriptions would automatically move, so imagine my surprise when they didn’t.  Our SSRS databases were located on a new server that had the same name as the old server.  After running down the error “The EXECUTE permission was denied on the object ‘xp_sqlagent_notify’, database ‘mssqlsystemresource’, schema ‘sys’”, I found a super awesome blog post.  It noted that I was missing assignments to my RSExecRole role.  Once I ran the script, I stopped and restart SSRS and all the subscriptions came over.  Huge sigh of relief.  Have a great day!

http://karthikeyandba.wordpress.com/2011/07/15/the-execute-permission-was-denied-on-the-object-xp_sqlagent_notify-database-mssqlsystemresource-schema-sys/#comment-35

 

This error will occur when you are trying to create subscriptions for a reports

 

Execute the below script will solve the issue.

 

GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole

GO

 

GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole

GO

GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole

GO

 

USE msdb

GO

 

– Permissions for SQL Agent SP’s

 

GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole

GO

GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole

GO

GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole

GO

by

I can hear music when Power Pivot is near…

Guess who presented for the first time at a SQL Saturday? It was also the Utah Big Mountain Data Conference.  I wanted to share my presentation in case anyone else wants to learn Power Pivot.

I love Power Pivot and think it can make a difference in how you look at data everyday.  If you have questions that you need answers to, I am happy to help.

Big Thanks to everyone that was so supportive with this presentation!

Beach Boys Data Demo

SQLSaturday Powerpoint – Unleash the Power of Power Pivot

by

Would You Let Me See Beneath Your Beautiful SSRS? Query XML from SSRS Content Field in Catalog Table.

Greetings!

I recently needed to find all the reports that access a certain database.  Easy enough, just get all those with that data source, right? Well not exactly.  I have cross database calls happening on some of my reports, some meaning over 200 of them. I found this really cool way to do it and want to save it for the next time that I have to do it.  I hope you enjoy too!

--ItemBin gets the content as a varbinary(max)

WITH ItemBin AS
(
SELECT
ItemID,Name,[Type]
,CASE Type
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other'
END AS TypeDescription

,CONVERT(varbinary(max),Content) AS Content
FROM ReportServer.dbo.Catalog
WHERE Type IN (2,5,7,8)
),
--ItemNoBOM removes the BOM if it exists...this is some extra junk that you don't need right now.
ItemNoBOM AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CASE
WHEN LEFT(Content,3) = 0xEFBBBF
THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
ELSE
Content
END AS Content

FROM ItemBin )
--ItemXML gets the content in xml form
,ItemXML AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CONVERT(xml,Content) AS ContentXML
FROM ItemNoBOM
)
--We can then use the XML data type to extract the queries, along with their command and text and place them in a temp table
SELECT
ItemID,Name,[Type],TypeDescription,ContentXML

,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
INTO SSRSXMLTemp
FROM ItemXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemXML.ContentXML.nodes('//*:Query') Queries(Query)

-- Next we join them to the original Catalog table so I can get the Name and Path while also having the command and then filter out what I don't want
--From the command.

SELECT A.NAME, Path, CommandText
FROM SSRSXMLTemp A
INNER JOIN Catalog C ON A.ItemId =C.ItemId
WHERE CommandText LIKE '%Something I am looking for%'
ORDER BY Name

by

Take a good hard look, I’m not okay – I created a cursor!

Last week I did something I promised myself I would never do again. It was horrible, dirty and made me feel like the worst DBA in the world. I didn’t drop a production database or truncate the most important table, this was way worse. I created a cursor. I am hoping that can I appeal to your kindness and understanding. Please help me forgive myself so the healing may begin.

It all started when I needed to build an Error Log report in SSRS. We are using a PowerShell script to go out to all our servers and bring a copy of the latest SQL error log to a centralized database and table. I was making a call to this table and only pulling back the last 4 days worth of data. I have learned over the years that with SSRS if you try to feed it too much data, you don’t get a report, you get a big red “X”. I was getting that “X”. Since I wasn’t allowed to modify the base table incase we needed to review it for other troubleshooting matters, I created a new table that just had the last 4 days worth of data. I was still getting the red “x”. I knew I had to filter my data more. We have a table of specific errors that we want to show up on this report. But they are not the complete error, just parts of it so the error remains generic enough to pull it from any server. In order for me to compare these fields, I need to use a “LIKE” clause. I tried joining with the “LIKE” clause, but I didn’t have any luck. I even tried a “Cross Join”  and a “Hard Join” but it still didn’t work.

In my despair, I was talking to a friend of the kingdom and he suggested a cursor. I backed away in shock. “No, no! Cursors are bad!” I cried. He calmly looked at me and said, “You are trying to use a sledge-hammer to hang a picture right now, try the cursor.”

I sat back down, arguing with myself. Could I do this? Betray what I have always believed and actually use a cursor? I figured I would at least write one and hope that I came up with a better solution in the process.

I know you are all excited to see the monster that I have created, truly a Royal Pain:

SET ANSI_PADDING ON
GO

Declare @objid int
Declare @ErrorMsg Varchar (255)

DECLARE SQLErrorLog_Cursor CURSOR FOR

/*Here is where I load my table of filters. This will allow me to add more errors to the report later without rebuilding anything.*/

SELECT [objid],[ErrorMsg]
FROM [LightsOn].[dbapp].[SQLErrorLogParseList]
OPEN SQLErrorLog_Cursor

FETCH NEXT FROM SQLErrorLog_Cursor
INTO @objid, @ErrorMsg
WHILE @@FETCH_STATUS = 0
BEGIN

/*This is my main query that I need to apply the filter to. InstanceSQLLog is the really large table I am filtering down.*/
INSERT INTO dbapp.SQLErrorLogFilter
SELECT InstanceName, LogDate, Text, P.FullName AS PrimaryOps, P.ID AS PrimaryOpsID,P3.FullName AS SecondaryOps, P3.ID AS SecondaryOpsID
FROM dbapp.InstanceSQLLog ISL
LEFT JOIN dbapp.Instance I ON ISL.InstanceId = I.InstanceID
LEFT JOIN People P ON I.PrimaryOpsDBAId = P.Id
LEFT JOIN dbo.People P3 ON I.SecondaryOpsDBAId = P3.ID
WHERE LogDate > GETDATE()-4 AND TEXT LIKE '%' + @ErrorMsg + '%'

FETCH NEXT FROM SQLErrorLog_Cursor
INTO @objid, @ErrorMsg
END

CLOSE SQLErrorLog_Cursor
DEALLOCATE SQLErrorLog_Cursor
GO

This is one of the steps of a 4 part job that I auto run early in the morning so the reports are in the specified DBA’s Email when they arrive at work (each report is customized to the DBA and the servers that are their Primary responsiblity.).

The whole process goes like this: I truncate the dbapp.SQLErrorLogFilter Table, run this cursor, Truncate the final grouped and filtered table for the report and repopulate that so SSRS gets a very clean and easy to read table. The whole process takes about 8 minutes. I am still trying to find a better way, but this works for now and the team is happy.

I have been toying with the idea of adding FastForward to the cursor. Does anyone have any opinions of why I should or should not do that?

Thank you for being so kind to read. I love how included the community makes me feel and how much everyone is willing to teach each other.

Have a magical week!

Andrea