SQL Tact

pointers, solutions and scripts for the SQL DBA
not intended to replace common sense


Fresh New Horror: PK on a Float Data Type

Here's a new thing to be scared about in the night, as if you weren't already terrified of floats after my previous two posts about the ills of the approximate-number data types.

Creating a primary key on a float data type can lead to what appear to be duplicate primary key values. Attempting to convert these values from float to an integer data type results in a primary key violation.

In fact, that's how we found it at the client site. Having already made the discovery that floats were to blame for randomly-appearing approximation problem that surfaced as business rule issues, we set about altering the column's data type to decimal (19,1). The error we received was "Violation of PRIMARY KEY constraint 'PK_invoicehdr'...".

Here's the grisly proof of good, well-intentioned people doing very bad things to an innocent database.

Parental Guidance: May contain some disturbing images. Viewer discretion advised.

Exhibits 1 and 2 above, we have the primary key declared on the invno column, declared as a float data type. (If it wasn't already very clear, this is a really bad idea.)

You ready for it?

There is nothing wrong with your television set. Do not attempt to adjust the picture. This is very real.

Pretty bad, right? Remember, this is their PRIMARY KEY.

Let's recreate the problem by manually inserting data that would conflict, or rather, would not conflict.

Turns out, in a table with millions of rows, we found only three instances where this was happening. I'm shocked that there weren't more - Murphy's Law ensured it happened, but it should have led to more than three times out of millions. Still, pretty bad.

Not surprisingly, this has lead to a pretty significant business rules problem.

Still working out the details of the fix to the database to recode those numbers before we try to convert the data types to decimal. And still working on changes to the offending .NET code (also probably using float data types).


SSRS Utility Reports: Subscription Inventory, Failed Subscriptions

When delivering a SQL Server Reporting Services (SSRS) solution with lots of subscriptions, it's helpful to also include some utility reports about those subscriptions.

Here are a pair of queries you can use against the ReportServer database (in this case, SQL Server 2014) to generate some quick internal reports for an inventory of report subscriptions (who is receiving what, when, in what format, including parameters?) and failed report subscriptions (what subscriptions have failed and why?), each with helpful info, timestamps and URL's.

Don't forget to change the url path's servername for these reports to reflect your own setup, keeping in mind that if you're using a named instance, the yourservername/Reports/ may look more like yourservername/Reports_instancename/.

Report Subscription Inventory

This is an easy way to provide business users with an accurate and easy list of "who's getting what" that is pulled directly from the ReportServer metadata - it'll never be out of date and it's live.
SELECT Catalog.Name AS ReportName
,'http://yourservername/Reports/Pages/Report.aspx?ItemPath=' + Catalog.Path + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab' AS ReportSubscriptionMgrUrl
,Subscriptions.Description AS SubscriptionDescription
,'Next Run Date' = CASE next_run_date
WHEN 0 THEN null
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
, 'Next Run Time' = isnull(CASE len(next_run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))

) as [To]
, Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RENDER_FORMAT"])[1]','nvarchar(150)')
) as [Render Format]
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(150)') as [Subject]
FROM [dbo].[ReportSchedule]
INNER JOIN [dbo].[Schedule]
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN [dbo].[Catalog]
ON ReportSchedule.ReportID = Catalog.ItemID
INNER JOIN [dbo].[Subscriptions]
ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [dbo].[Users]
ON Subscriptions.OwnerID = Users.UserID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),[ReportSchedule].ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id

Screenshot of Design view from my version, your results may vary

Report Subscription Failures

Allows the user to see if any subscriptions have failed most recently, for handling typical email or permissions errors, in the past 30 days. Because it uses the subscription's [laststatus] field (the same one you'll see in Report Manager), failures will drop off this report if they succeed again.
SELECT Catalog.Name AS ReportName
,'http://yourservername/Reports/Pages/Report.aspx?ItemPath=' + Catalog.Path + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab' AS ReportSubscriptionMgrUrl
,Users.UserName AS SubscriptionOwner
,Subscriptions.Description AS SubscriptionDescription
FROM [dbo].[ReportSchedule]
INNER JOIN [dbo].[Schedule]
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN [dbo].[Catalog]
ON ReportSchedule.ReportID = Catalog.ItemID
INNER JOIN [dbo].[Subscriptions]
ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [dbo].[Users]
ON Subscriptions.OwnerID = Users.UserID
WHERE ((Subscriptions.DataSettings IS NULL AND Subscriptions.LastStatus LIKE 'Failure%') -- handle standard subscription errors
OR (Subscriptions.DataSettings IS NOT NULL AND RIGHT(Subscriptions.LastStatus, 11) <> '; 0 errors.'))
and Subscriptions.LastRunTime > dateadd(day, -31, getdate())

Screenshot of Design view from my version, your results may vary


Live Microsoft Event - Power BI Dashboard in a Day - Baton Rouge

Join BRSSUG Founder and Microsoft TSP Patrick Leblanc to learn how to use Microsoft’s self-service Business Intelligence platform, Power BI. In the first part of the session, the instructor will lead you through a Microsoft dataset and demo teaching you the basic elements of Power BI.

Each attendee should bring his or her laptop with a copy of Microsoft Office 365 Pro Plus or Professional Plus 2013 installed, specifically Excel.  In addition, each person should ensure that Power Query has been installed on their machine.  Power Query can be downloaded from this link: http://www.microsoft.com/en-us/download/details.aspx?id=39379

As part of this course, students will be creating:
·         Data Mash-ups: Discover and Combine Data using Power Query
·         Create Data Models using Power Pivot
·         Share Data: Learn how to add your data to the catalog for others to use
·         Explore, Visualize Data using Power View

12:00 PM – Arrivals & Networking
12:30 PM– Introductions and Overview
1:00 PM - Power BI Lab Walk through
2:30 PM - Break
4:15 PM – Wrap-up & Close
Space at these exclusive events is limited, so please reserve your spot today.

Registration Required!

Starts: Tuesday, April 14, 2015 12:00 PM
Ends: Tuesday, April 14, 2015 4:30 PM
Time zone: (GMT-06:00) Central Time (US & Canada)
Welcome Time: 11:30 AM
Location: Sparkhound Corporate Office
Suite 600 (6th floor, look for signs)
2900 Westfork Drive
Baton Rouge Louisiana 70827

Registration Page Link Here