SQL Tact

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


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:


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:



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.