pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

11/05/2015

Announcing the Baton Rouge Power BI User Group

Baton Rouge is home to one of the inaugural Power BI User Groups, starting up soon! I'll be serving as its initial organizer, but I've already received a lot of feedback from the community and hope to form a new leadership team for the group soon.

Power BI is quickly becoming the hub for all things SQL Business Intelligence related, and will soon have both cloud-based and on-premise options. This exciting technology will continue to expand as SQL 2016 is announced. 

Stay tuned for more information on the official website and first meeting of the Power BI user group, which will be in either December or January. We already know that Baton Rouge native and current Microsoft employee Patrick Leblanc, founder of the Baton Rouge SQL Server User Group, will be our virtual speaker in January.

Get involved with Power BI today at the Power BI Community site, and our page here: http://community.powerbi.com/t5/Baton-Rouge-Power-BI-User-Group/gp-p/BRPUG


10/14/2015

Congratulations New 2015-2016 BRSSUG Officers!

We elected new officers tonight for the Baton Rouge SQL Server User Group Official PASS Chapter, the second year in a row we've made an effort to do so.

We departed from a singleheaded user group mostly on the shoulders of a single volunteer. Prior to 2014, making things happen for the BRSSUG was mostly on William (me), with sporadic help from volunteers. Prior to 2011, it was mostly on Patrick Leblanc (now with Microsoft), who started the group in <2008, with sporadic help from volunteers like myself,

So the rest of this blog post is intended for the audience of user group leadership, no matter what structure (or lack thereof) your user group leadership takes.

We started electing officers in 2014 (after our annual SQL Saturday) in order to serve four goals:

  1. Capitalize better on enthusiasm in the user group
  2. Foster more ownership-type thinking about the user group, with the hopes of increasing volunteerism
  3. Allow others the career/resume/networking benefits that leadership of a user group can provide
  4. Share the load, Mr Frodo
To be honest, we had mixed results in the first year. We're still rounding out who some tasks should be owned by, and peeling more and more tasks away from myself, and finding the right sense of volunteerism between meetings.

If you're considering having "elections" for user group officers, it's worth a try. Don't be surprised if the spirit of hesitance and collegiality leads all officers to be elected unopposed, with humble candidates try to out-humble eachother to cede victory to the other, or for some folks to run in absentee, or both. And that's totally okay. 

This isn't supposed to be competitive officership. And if someone doesn't "win" an office and still wants to help? Great! As a leader, never shut down this sort of enthusiasm. Find some way to involve and foster that sense of ownership. Then, trust them to get the job done. Trust, but verify.

Remember, enthusiasm is the primary job qualification for volunteer user group leadership.

I'm very excited about our new officers in 2015-2016:
Adrian Aucoin (@CajunSQL) - President
Matthew Tessier (@MatthewTessier2) - VP of Media
Me (@william_A_dba) - VP of Logistics
Kenny Neal (@KennyNeal) - VP of Education and Court Jester


If you're curious (and these aren't original to us), here's the officers we elected tonight. Our buddies in the Baton Rouge .NET User Group elected their four as well.

President
  •  SQLPass Chapter Leader and SQLPass Representative
  •  Scheduling, Event Planning and Facility
  •  Website announcements (cooperatively with VP of Media)
  •  Officer communication and commitment followup
  •  Social Media communication
VP of Media
  • Website updates, images, photography and announcements
  • Mailing Lists spamming
  • Lead Social Media (especially Twitter, Facebook, new outlets, brand management)
VP of Logistics
  • Host facility communication/logistics/cleanup organization
  • Organizing/verifying Monthly Food order
  • Swag/prize receiver and collector
  • Purchaser/Accounts Payable 
VP of Education
  • Books requests from various book publishers or training providers
  • Applications for freebees, sponsorships, giveaways
  • Work with regional UG's to arrange speaking tours
  • Organize study groups, certification prep groups, other learning opportunities
Shared Officer Responsibilities
  • Speaker recruitment and centralized scheduling
  • UG sponsor recruitment and centralized scheduling
  • Act as master of ceremonies for UG meetings, raffle giveaways

10/03/2015

Presentation Downloads from SQL Saturday Columbus GA 2015

Thanks for joining us at SQL Saturday Columbus today on a gorgeous day to go inside and learn about SQL Server! And even bigger congrats to those of you who use Google maps and were still able to find the brand new event location at the Troy State University Riverfront campus!

My colleagues Cody Gros and John Walker joined me on a trek from Louisiana earlier this week and I was so happy to see my SQLSaturday friends, including the great and magnificent SQL Saturday empress Karla Landrum, once again.

A special thanks and job well done to Tim Radney for organizing this top notch event throughout a rough week and the loss of his father. Friends are all over the place Tim, glad so many of us could be here today to tell you this in person instead of from afar. Great job, great facility, great event, great people.


Here are the downloads for two presentations. These presentation files are also available on the SQLSaturday event website.

SQL + SharePoint: Friends Forever (William Assaf and Cody Gros)
12:30pm in room 213
http://www.sqlsaturday.com/439/Sessions/Details.aspx?sid=40086

SQL Admin Best Practices with DMVs
3pm in room 310
http://www.sqlsaturday.com/439/Sessions/Details.aspx?sid=40049

Downloads here:
http://1drv.ms/1YWsU2C

Sorry, no, we won't be uploading our Sparkhound Jeopardy game from the lunch session. Can't be giving away all our answers! Er, questions.

9/28/2015

Why I Don't Use the Architect Title

I get nervous when I see the title "architect" in IT job roles.

I've had this title before as an employer's standard, but lobbied against it. I instead prefer separating job titles from project roles.

Why?

"Architect" as an IT role descriptor carries an appropriate level of technical expertise and broad experience - but typically, for too long. I don't like "Architect" because it sticks.

The verb "architect" implies a leadership and guidance in big system design decisions.
The noun "architect" can be a bludgeon in a software planning meeting.

Putting egos aside when making platform and architecture decisions can be next to impossible if someone decides to wield the "architect" hammer.

Can you think of a situation when large amounts of dated technical experience did not make for quality technical guidance in the future? I bet you can. I bet you know exactly the type I'm talking about.

Platforms, language versions, feature sets and even product suites change regularly, so a clingy title like "architect" may be appropriate for only one or two projects or generations of a given technology. A few major releases, framework versions or platform changes later, and the "architect" title may be as stale and waning as the technical skillset behind it. Without continuing education, "architect" becomes synonymous with "boat anchor".

When the next project comes, the "architect" resource from the original project may be (not as a rule) more gentrified than skilled. And while the "architect" doesn't need to be the most technically savvy member of a project team, the assumption that "architect" was an applicable title a few years ago - and so shall ever be - is a dangerous trap.
  • The "architect" for your SQL 2005 project needs to get up to speed on the latest changes (ex: data types, high availability, and columnstore indexes) before serving in a similar role for your new SQL Server 2014 project.
  • The "architect" for your .NET v2.0 project that's now in maintenance would perhaps better serve as a developer for your new .NET 4.5 project. 
  • The "architect" for your first-iteration Entity Framework 4 project might not be ready to carry the lead that new EF6 application.
  • The "architect" for your SQL Server 2008 R2 SSAS multidimensional business intelligence platform may not be aware of architecture features involved in your new SharePoint-based business intelligence project leveraging SSAS Tabular and PowerBI.
Instead of your top-tier developers assuming the title of "architect", try using the names with transient intent, like Project Lead, Team Lead, Technical Lead, Designer, Analyst or Developer.

In summary, please make sure that you and your coworkers aren't treating "architect" like academia treats tenure. It's not a lifetime appointment, and the ego boost that comes with "architect" needs to give way to honesty, humility and continuing education.

9/24/2015

I Was Once a Very Crappy Carpenter + SQL Bolts to Buzzwords

Tonight I spoke to Dr. Alkadi's CMPS 411 class, which was hijacked by the Hammond .NET User Group onsite at Southeastern University! Thanks once again to Sparkhound who sponsored!

Here's the slide deck for my presentation, which combined two topics I wanted to talk about. The first, I Was Once a Very Crappy Carpenter, is about my personal journey early in my career from unmotivated "carpenter" to motivated "carpenter," which I think is relevant for folks in the IT industry at any experience level. The second, SQL Bolts to Buzzwords, is a ground-floor introduction to SQL Server database technology and core concepts about ACID fundamentals, data types, and disaster recovery that are crucial for developers/students to grasp.

Remember, stay away from floats and guids, stay in school, and eat your vegetables (pizza counts).

Thanks for attending, hope you enjoyed the presentation!




9/13/2015

Houston TechFest Presentation Downloads

Thanks for joining me at Houston TechFest 2015 at the NRG Center in Houston, TX! Big congrats to Michael and his crew of dedicated volunteers who once again put forward a first-rate free Saturday training event. I hope everyone enjoyed my presentations, our Sparkhound lunch session, our collar toss game at the Sparkhound booth, and all the general networking, professional development and learning all day.

As promised, here's the download links for my two presentations in the SQL Server track, which capped off a long day that featured six presentations by Assafs: two on SQL server, three in the Career Dev track and one more lunch session where I Trebek'ed a game of Sparkhound Jeopardy! in the Sparkhound Top Dog Challenge.

In room 313 I presented: SQL Server Security Principals and Best Practices at 2:10pm and SQL Server Admin Best Practices with DMV's at 4:10pm.

Here's the Onedrive folder for the slidedecks and sample scripts, including the labs we didn't cover in the DMV session: http://1drv.ms/1UO0Te3

See you next year!




8/28/2015

SQL Saturday Baton Rouge 2015 Recap

SQL Saturday Baton Rouge 2015 was our 7th annual event, but we're still learning lessons. Here's my official Planning Committee Chair's recap of the event.


Here's one of the big new things we did this year:
  • We have a large, four-building facility for our event with multiple entry points. We have one registration desk, sure, but we're also onsite at a major university's campus, so we get a lot of walk-ups. So it's hard to use anything in the SpeedPASS to count our folks. So this year we got these inexpensive, thin tyvek wristbands, similar to ones they would put on your wrist at a bar, concert or event. We ordered from wristbandexpress.com.
    • To give folks incentive to get themselves a wristband (and therefore get counted), we told everyone we're going to use the five-digit number on the wristbands as a raffle ticket as well for the big end-of-day giveaway.
    • Unfortunately, the wristbands come in packs of 500, and the labeled numbers for each pack are NOT contiguous OR guaranteed to be unique. So we ordered four packs of wristbands in two colors as we expected around 600 people. If we get overlapping wristband numbers, we'd use different colors. If we somehow got contiguous numbers, we'd use those two packs.
    • We gave away the wristbands at the registration table, and had folks (including myself) grabbing a sheet of 10 wristbands and walking around to hunt for folks who might not be wearing one. We scoured the lunch line to make sure people got one, I think we did a good job. At the raffle at the end of the day, we asked if anyone in attendance didn't have a wristband, and maybe a handful didn't.
    • Turns out, we got unique numbers but not contiguous. Our plans was to give out the first 500, then start pulling from the second pack. Our count at the end of day was 586. We had given out all 500 from the first pack, 86 from the second range of numbers.
  • After years of meeting for lunch, we had most of our planning committee meetings this year online via Skype for Business. We had better attendance, more frequent meetings, and better note-taking.
  • We splurged on a vegetarian lunch option from Zoe's Kitchen, a vegetarian salad and roasted veggies, and it was a big hit even with omnivores.
  • We tried our best to use the new PASS volunteer portal, but it had limitations, so our gallant volunteer coordinator Adrian Aucoin had to revert to manual, excel-based scheduling of volunteers (for multiple volunteer timeslots/jobs) and email communication. Already working with PASS to get that promising website improved for 2016.
  • Mesh construction vests instead of volunteer tee shirts was a big hit. 
    • Easier for volunteers to throw on - without having to layer or change shirts.
    • Lightweight and very highly visible
    • Some might enjoy wearing them as night-time running gear!

Some of the new ideas that we took in from our Post-Mortem Meeting and an online document shared with all planning committee members and volunteers:
  • Leave badge holders out of the bags, hand them at registration. We noticed a lot of people digging in their bags for their badge holders, clogging up the registration area. The registration area must be a high-velocity traffic area for us since we have so many attendees.
  • We didn't realize this until the night of the bag packing event, but for the first time, not a single sponsor provided labeled pens or notepads for the conference bags. Wow! Next year, we need to make sure someone provides pens, and instead of notepads, we'll add a couple extra blank pages to the letter-paper-sized conference booklet. (We need to make sure the conference booklet is therefore NOT printed on glossy paper.)
  • Instead of plastic bags, perhaps we should encourage a sponsor to provide more durable, more re-usable, more eco-conscious fabric grocery store bags? We will work together to get the bags printed with both the SQLSat and sponsor logo.
  • During the raffle, invite any leaders of any user groups to come up and grab some free swag. Tell folks if they want these prizes, they gotta go to the meetings!
  • Assign volunteers to time slots of garbage duty. We got overwhelmed with garbage during lunch.
  • Assign volunteers to specifically refill the ice chests with drinks. We ran out during lunch.
  • After years of doing lunch sessions, last year we were told by our host LSU that no food/drink were allowed in the classrooms. Many of our attendees don't know this yet. We need more NO FOOD/DRINK SIGNS, probably in each classroom.
  • Put that message and more on an informational post-it poster in each classroom.
  • Assign specific volunteers to stay after the event to clean up, move tables, haul trash and pack our stuff. We need at least 20 people for this, 10 was not enough this year. 
  • Buy dry-erase markers and erasers and put them in each classroom. It’ll be a small donation to LSU since apparently they can’t afford dry erase markers (or the professors hoard them selfishly).
  • The volunteer coordinator should stay put and in an obvious place to greet volunteers and give out assignments. He/she should stay put in Volunteer HQ so that our 80+ volunteers are never in doubt as to how they can help.
  • Speakers should be given a slide to add to their slidedecks that includes instructions for how attendees can fill out the online speaker evaluations. The word didn't get out enough to give speakers feedback online.
  • Do a better job of reaching out to local university departments, community colleges, social media, and newspapers and event calendars.
  • A Monty Python-themed SQL Saturday would be AMAZING. But where would we find enough shrubberies?

8/23/2015

Actual Emails: Allow NUNS to Lead You to Good Clustered Index Design

Wrote this email exchange with a colleague who wanted to confirm that the client-proposed design for a table was... less than optimal.


Subject: clustered index
From: A. Developer

Hey William,
I believe the client created this Clustered Index... <horrifying screenshot of a clustered index with many large nvarchar columns as the key>

From: William 

The clustered index is ideally 
1)      Non-changing
2)      Unique
3)      Narrow
4)      Sequential 
“NUNS” 
Having those multiple nvarchar columns in the clustered index is probably not a good idea, as it violates #3 and probably #1 and #4 too.  
The most ideal clustered index is on an integer identity column. You can modify the design of a table to add one of those. 
Look at the data and suggest a new clustered index. That five-column clustered index might be a perfectly fine nonclustered index, but is an inefficient clustered key.

From: A. Developer
That makes sense. Thanks William!

8/20/2015

Your Devs' Questions Answered With sys.dm_server_services

It starts with "I'm a developer...

"... with no RDP access, how can I tell if the SQL Server Agent service is running?"
"... and I don't think CDC is working. Is the SQL Agent on?"
"... how can I tell how long has the SQL Server been running? Did somebody reboot it last night?"
"... so hey I need to know if the SQL Server service set to automatic startup."

Here's an easy script that anyone can use, it can be especially helpful for developers without RDP access to the Windows Server that is hosting the SQL Server service. It'll answer these developer questions and more, in addition to a lot of other utility for you as a DBA.
SELECT  servicename -- Ex: SQL Server (SQL2K8R2)
, startup_type_desc -- Manual, Automatic
,  status_desc -- Running, Stopped, etc.
,  process_id
,  last_startup_time -- datetime
,  service_account
,  filename
,  is_clustered -- Y/N
,  cluster_nodename
FROM   sys.dm_server_services

You don't need to be a member of the sysadmin server role to run this, but you do need VIEW SERVER STATE. Developers can be given this permission, even in production, as it gives them access to many key dynamic management views (DMVs), including DMV's for diagnostics and performance tuning.

MSDN reference: https://technet.microsoft.com/en-us/library/Hh204542(v=SQL.110).aspx

7/19/2015

See you at SQL Saturday Baton Rouge 2015!

SQL Saturday is a global event to bring Information Technology speakers and professionals together for a community-driven, community-attended free day of technical training. The Baton Rouge SQL Saturday 2015 event is produced by the Baton Rouge User Groups, and will be our sixth annual event on campus at LSU. We expect ~600 regional IT professionals and national speakers to join us. 

This free conference is open to the public and is perfect for students, database administrators, .NET developers, business intelligence developer, SharePoint admins and developers, IT managers, server admins, network admins, and job-seekers.



Folks with the following skillsets are drawn to SQL Saturday Baton Rouge because of the professional networking, free training, and giveaways:
  • SQL Server Administrators
  • Business Intelligence Developers
  • Data Analysts
  • ETL Developers
  • C#/VB.NET Developers
  • Mobile Developers
  • Windows Server Admins
  • SharePoint Architects
  • SharePoint Developers
  • Network Administrators
  • Quality Assurance Analysts
  • IT Managers
  • Project Managers
  • Hiring Managers
  • Jobseekers of all levels of experience
  • Students
  • CIO's
  • CEO's

We got some great feedback and testimonials in 2014, like these actual responses:
  • “Some very good talks with great content, large community of very smart, talented developers, great networking, and awesome SWAG/goodies”
  • “Great networking opportunity, good way to get to know the community. Some talks were really excellent presentations on state of the art database techniques.”
  • “Lots of companies with great networking opportunities; lots of free training and free stuff. High quality and free is very unique.”
  • “It was a fun way to network and learn about how everyone is using technology that we can bring back to our own organizations. It is a chance to learn something new, and meet new people.”
  • “My first sql saturday and certainly not my last. Had a very nice time.”
After our hugely successful 2014 event, we sent out an internet survey to all registered attendees. Check out the results of our last, most important question:



6/13/2015

SQL Server Permissions and Security Principals at Houston SQL Saturday 2015

Thanks to everyone who attended my presentation on SQL Server Permissions and Security Principals this morning at Houston SQL Saturday 2015!

What an awesome event! Please join me in sending a note of thanks and congratulations to Nancy Hidy-Wilson and the rest of the Houston Area SQL Saturday User Group for organizing this awesome event.

My session was be a ground-floor introduction to SQL Server permissions starting with the basics and moving into the security implications behinds stored procedures, views, database ownership, application connections, consolidated databases, application roles and more.

Download my slides and sample scripts here:
http://1drv.ms/1FSdJvh

 

6/11/2015

Error Upon First Processing Your SSAS Database With A Service Account

This blog post is to document the easy fix to a problem that has become a rite of passage for the good folks (including myself) on the data warehouse development team I've been working with recently.

Here's the error text you've probably received the first time you try to process your Analysis Services database using a job or, in this case, an SSIS package calling an Analysis Services Execute DDL Task:
Executing ExecutePackageTask: D:\whatever.dtsx
Error: 0xC1060000 at Analysis Services Processing Task, Analysis Services Execute DDL Task: OLE DB or ODBC error: Login failed for user 'NT SERVICE\MSOLAP$SQL2K14'.; 28000; Cannot open database "DataWhatever" requested by the login. The login failed.; 42000.
Error: 0xC1120064 at Analysis Services Processing Task, Analysis Services Execute DDL Task: A connection could not be made to the data source with the DataSourceID of '4c67b909-250d-4b14-95cc-451d1c95bc9f', Name of 'SqlServer .sql2k14bi DataWhatever'.
Error: 0xC11F0056 at Analysis Services Processing Task, Analysis Services Execute DDL Task: An error occurred while processing the partition 'DimDate_168cd22b-ae72-4730-a054-4fd0fa6acc2f' in table 'DimDate_168cd22b-ae72-4730-a054-4fd0fa6acc2f'.
Error: 0xC11C0006 at Analysis Services Processing Task, Analysis Services Execute DDL Task: The current operation was cancelled because another operation in the transaction failed.
Task failed: Analysis Services Processing Task
Warning: 0x80019002 at Main: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
The solution is quite simple.

The SSAS data cube might have been developed and processed just fine with your personal user account, but now that you've executed it with a service account for the first time, you need to grant it permissions. The SSAS Service account does not have rights to access your data source, in this case, your SQL Server 2014 instance.

Grant read-only permissions to the SSAS service account to the source database, for example, by creating a login and adding that login to the db_datareader role of the database, or granting SELECT permissions to the tables or schema needed to process the SSAS cube.

6/08/2015

Behavior of ORDER BY + UNION Can Be Confusing

This is an interesting example and it exposes a quirk with the concept of a UNION, which is, that the various subsets of a UNION cannot be guaranteed to be sorted by their subset character.

The difference is important to understand - it is the difference between a result set being determinant (in this case, predictably ordered) or not. It's also the difference between a syntax error and a properly ordered UNION'd result set.

Here's the key: after two results sets have been UNION’d, you cannot independently sort the rows in the first query, then the results in the second query, because the origin rowset is lost.

Here's a demonstration:
DECLARE @tbl TABLE ( id INT primary key, val VARCHAR(10) )
INSERT @tbl
( id, val )
VALUES 
( 3, 'Last' ),
( 1, 'First' ),
( 2, 'Beta' )
 
-- Works
SELECT *
FROM (
  SELECT TOP 1 *
FROM @tbl t
ORDER BY t.id DESC
  UNION ALL
  SELECT TOP 1 *
FROM @tbl t
ORDER BY t.id DESC
) x
id val
3 Last
3 Last
-- Same intent, but throws a syntax error
SELECT TOP 1 *
FROM @tbl t
ORDER BY t.id DESC
UNION ALL
SELECT TOP 1 *
FROM @tbl t
ORDER BY t.id DESC
Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'UNION'.

-- Note that the ORDER BY is sorting the post-union result set, but that our TOP 1 is non-determinant.
SELECT TOP 1 *
FROM @tbl t

UNION ALL
SELECT TOP 1 *
FROM @tbl t
ORDER BY t.id DESC

id val
1 First
1 First



Note that the last line in last query, is not sorting the bottom reference to @tbl, but rather is sorting the post-union result set.

Look at that values we're getting in the last subquery. Why the value of 1?

Note that I have a primary key on the table variable, which by default creates a clustered index. This is not the "default sort order" of an unsorted query return, but in this particular and highly simplistic case, it does provide us a sort when getting a TOP 1. Without the PRIMARY KEY in the DECLARE, both of these valid-syntax queries return 3 and 3, because it's now an unsorted heap.

Move the PRIMARY KEY clustered index to the val column, and the last query results in 2 and 2, because 'Beta' is now the first row returned, when sorted (ascending, by default).

The default sort for an query, without an ORDER BY in a query, will be non-determinant from query to query. In some queries, a nonclustered index or a joined table or other operation could provide the sort. In order words, sort order without an ORDER BY may be sometimes predictable, not reliable.

So, why are ORDER BY's allowed after each UNION statement in a subquery?  The ordering of the post-union result set can now be provided with an ORDER BY outside of the subquery, so I’m speculating that the syntax allows it.  Inside a subquery, we can assume that the ORDER BY for each query in a UNION is actually not referring to the post-UNION result set.

That’s weird, but wrapping the union statement in a subquery is not “hiding an error” as it may appear to be, it’s actually allowing you to now define three different ORDER BY’s if you want it – one for each pre-union result set and one for the post-union set.

Without parenthesis, there’s no syntax to support those three different ORDER BY’s, there is only one post-union result set ORDER BY.

So, why make this a blog post?

Because one of the developers I work with found the last query above in legacy code for an application they are supporting. A real developer made the very real mistake of using a TOP without an ORDER BY in each half of a UNION (of two far more complicated queries than the above examples), resulting in unpredictable and inconsistent results that also failed to enforce the business logic that was required.

Remember, NEVER use a TOP without an ORDER BY, and be aware of how a UNION can make this tricky!

6/05/2015

See You Next Weekend at SQL Saturday Houston!

Looking forward to speaking next weekend at SQL Saturday Houston 2015 on June 13!

If you haven't already registered for this great event, here's the link: https://www.sqlsaturday.com/408/registernow.aspx

This event will be held at San Jacinto College in Houston, and admittance to this event is free but there is a $10 lunch fee.

I'll be presenting on SQL Server Permissions and Security Principals at 9:45AM in room 1.115. My session, which I have done at other SQL Saturdays in the past, will be a ground-floor introduction to SQL Server permissions starting with the basics and moving into the security implications behinds stored procedures, views, database ownership, application connections, consolidated databases, application roles and more. This class is perfect for DBA's, developers and system admins.

5/11/2015

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).

5/05/2015

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
,Subscriptions.LastStatus
,Subscriptions.LastRunTime
,'Next Run Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END
, '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))
END,'NA')

,Subscriptions.Parameters
,ISNULL(
Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)')
,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="PATH"])[1]','nvarchar(150)')
) as [To]
,
ISNULL(
 Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(150)') 
, 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
,Subscriptions.LastStatus
,Subscriptions.LastRunTime
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



4/09/2015

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

AGENDA
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

3/22/2015

Announcing: SQL Saturday Baton Rouge 2015

SQL Saturday Baton Rouge 2015 is coming up on August 2 at LSU, register today!

Email or instant message your friends, post it on your blog, tweet it on Twitter with hashtag #SQLSatBR - free training is coming soon for those interested in SQL Server, .NET development, SharePoint, Windows Server, PowerShell and more!

Who Attends SQL Saturday Baton Rouge?
Folks with the following skillsets are drawn to SQL Saturday Baton Rouge because of the professional networking, free training, and giveaways:

  • SQL Server Administrators
  • Business Intelligence Developers
  • Data Analysts
  • ETL Developers
  • C#/VB.NET Developers
  • Mobile Developers
  • Windows Server Admins
  • SharePoint Architects
  • SharePoint Developers
  • Network Administrators
  • Quality Assurance Analysts
  • IT Managers
  • Project Managers
  • Students
After our 600+ person 2014 event, we received some great feedback and testimonials, like these actual responses:

  • “Some very good talks with great content, large community of very smart, talented developers, great networking, and awesome SWAG/goodies”
  • “Great networking opportunity, good way to get to know the community. Some talks were really excellent presentations on state of the art database techniques.”
  • "Lots of companies with great networking opportunities; lots of free training and free stuff. High quality and free is very unique.”
  • “It was a fun way to network and learn about how everyone is using technology that we can bring back to our own organizations. It is a chance to learn something new, and meet new people.”
  • “My first sql saturday and certainly not my last. Had a very nice time.”

Check out the results of our last, most important question on a post-event survey:



See you there!

Team SQL Saturday Baton Rouge
Register today!