My Favorite (SQL Server) Podcasts

I’ve been listening to podcasts for quite some time now, I think I started around two years ago. I used to listen to the radio while commuting, but I thought I could spend my time better by listening to podcasts.

I first started out with downloading SQL Server Radio on my laptop, put it in Dropbox, download it on my phone and play it on the radio in my car via Bluetooth. I knew this could be done better but didn’t have the need for right then. When I heard of .NET Rocks! and other SQL Server-related podcasts I knew I had to make a change to my overly complex process of listening to podcasts. That’s when I started using Podcast Addict for Android. It took me a while to get used to, but I started with a speed of 1.3, then to 1.5, 1.7 and now I play them at 2.0 speed.

For an overview, the podcasts I listen to are:

• .NET Rocks!
• Dear SQL DBA
• Developer On Fire
• Hanselminutes
• no dogma podcast
• NOS op 3 Tech podcast
• Office Hours Podcast: SQL Server Pain Relief
• RunAs Radio
• Simple Programmer Podcast
• SQL Down Under
• SQL Server Radio
• voiceofthedba’s podcast

---

.NET Rocks! by Carl Franklin (b | @carlfranklin) and Richard Campbell (@richcampbell)
.NET Rocks! is a weekly talk show for anyone interested in programming on the Microsoft .NET platform. The shows range from introductory information to hardcore geekiness.

This has got to be the longest running (SQL Server) podcast in the world.. It’s already at episode 1382 and started in 2002! The topics range from .NET (obviously) to Azure, mobile development to live Q&A sessions at conferences and JavaScript to coding with voice control. There’s something for everyone. So they’ve got a wide variety of topics and besides that the show is usually also a good laugh!
In addition to these topics, they also have these “Geek Outs” one in a while, with a wide variety of topics like DC Lighting, Arctic Ocean, Reusable Spacecraft and Supersonic Aircraft Geek Out.

---

Dear SQL DBA by Kendra Little (b | @Kendra_Little)
“Dear SQL DBA” is a free weekly podcast where I answer questions from (and about) SQL Server DBAs.

Kendra just delivered her 24th episode, she started out some 7 months ago, she has about 2-3 episodes a month ranging roughly from 15-30 minutes. An episode is centered around a question she gets from users. I only discovered this podcast a few weeks ago but I already like it very much because of the way she talks about the topic and explains things very clearly. There’s also a clear structure in every episode that makes it good to follow. If you’re not fan of podcasts you can also find the transcripts on her site, just read up on the questions listed on the site and pick the ones you’re interested in.
The topics range from How to Level Up Your DBA Career to Altering an INT Column to a BIGINT

---

Developer On Fire by Dave Rael (b | @raelyard)
Stories from inspiring people in and around software.

This podcast only started out in June 2015 with an introduction, but already has its 187th episode aired!
Dave started out with a good structure around his podcast: what does value mean to you, what are your greatest failure and success, a book recommendation (he keeps a whole list of all the recommendations) and 3 ways of delivering value.
He has a wide variety of topics and speakers like Troy Hunt, Jon Skeet and Linda Rising together with his daughter Kendall.

---

Hanselminutes by Scott Hanselmann (b | @shanselman)
Fresh Air for Developers

Currently at show #559, Scott delivers an episode once a week that normally is just over 30 minutes. Although the topics are not always to my interest, there’s something to learn every time, be it social skills, how to learn presenting or finding a tool for your todo-lists. His topics range from specific technologies (Hello Ruby, Web Animation Foundations), to teaching and learning and presenting.

---

no dogma podcast by Bryan Hogan (b | @bryanjhogan)
discussions on software development

Bryan delivers 1 to 2 shows a month and started in June 2014. His topics range from DevOps to Project management and privacy. I’m not a big fan of the long music outro, but that’s just my opinion and I can easily skip that part. Nonetheless a descent podcast with good releases and quality content!

---

NOS op 3 Tech podcast (Dutch) by NOS (Dutch Broadcasting Foundation)
The NOS 3 Tech Podcast updates you each week about the latest news from the world of technology and social media. Every Thursday at 16:30 a new episode!

This podcast started in August of this year and has exactly 1 episode a week that takes around 30 minutes. It’s a really fun and light-weight podcast to listen to that has several changing experts around 3-4 subjects every week. The topics range from VR and Playstation Now, to going to Mars with Elon Musk and Silicon Valley and Trump.

---

Office Hours Podcast: SQL Server Pain Relief by (the team at) Brent Ozar Unlimited (b | @BrentOzarULTD)
Every Wednesday, we get together to answer your SQL Server questions. Every now and then, we also throw in special topical episodes where we present one of our training class sessions for free, too.

They started just over a year ago and have a webcast (almost) every Wednesday, where every episode takes around 30 minutes. They answer live questions while they are coming in, they have to be short though, otherwise they’ll send you to SO or SE. Almost every episode a question comes around where they refer to the post by Erland Sommarskog (b): Slow in the App, Fast in SSMS, which he recently revised for changes in SQL 2016. For me it’s a must-hear every week: fun, questions from the audience and packed with knowledge.

---

RunAs Radio by Richard Campbell (@richcampbell)
RunAs Radio is a weekly podcast for IT Professionals working with Microsoft products. Each 30-minute episode covers a specific topic in the field of IT from a Microsoft-centric viewpoint.

Launched in April 2007 it just had its 500th episode two months ago! This podcast smells knowledge and enthousiasm.
The topics range from SQL Q&A’s at SQLIntersection (2013, 2015, 2016) to Data Analytics with Jen Stirrup and The DevOps Handbook with Gene Kim.

---

Simple Programmer Podcast by John Sonmez (b | @jsonmez)
The Simple Programmer Podcast is a short podcast that is a mix of career advice, philosophy and soft skills.

Each episode is around 5-10 minutes long, except for interviews that usually last around 40 minutes and he has a strict release schedule of at least 3 episodes a week. As John says it, his topics range from career development, entrepreneurship, fitness, finance, productivity, personal development and more. Although 3 a week seems like a lot, they’re really short and good-to-listen-to podcasts, you can even skip the funny music and intro/ads if you like to take it down 2 minutes.
He even launched the 2016 Developer Podcast Award ending on the 31st of 2016, so if you still want to cast a vote you’d better hurry.

---

SQL Down Under by Greg Low (b | @greglow)
SQL Down Under is a podcast (audio show) for SQL Server professionals.

Greg started in 2005 and last month episode 70 was released, with each episode taking around 60 minutes. His release schedule is not that regular the last year, but he did release 2 episodes in the last 3 months. I hope he’s getting back to a more regular release because I really like his podcasts and he has some quite well known guests from the SQL Server world, ranging from Itzik Ben-Gan back in 2005, to Paul Randal in 2007, Jen Underwood in 2015 and Kasper De Jonge earlier this year on SSAS 2016. Up until 2014 Greg also kept the show transcripts up-to-date if you’re more into reading.

---

SQL Server Radio by Madeira Data Solutions (b | @Madeira_Data)
SQL Server Radio is a podcast (an internet radio show) for SQL Server DBAs, database developers, architects, system administrators, and anyone else who is interested in the SQL Server platform.

Guy Glantser (@guy_glantser) and Matan Yungman (@MatanYungman) started this podcast in August 2014 and release an episode every 2 weeks, which takes around 30-50 minutes. They also have a Hebrew edition which they started earlier. The podcast is always good and fun to listen to, sometimes they bring a guest, but usually it’s Guy and Matan talking SQL Server, conferences or recent blog posts or a mistake they made. Every now and then they have a quiz about a random term out the SQL Server Glossary, on which they have to tell an interesting(!) story. Usually it boils down to just a story.

---

voiceofthedba’s podcast by Steve Jones (b | @way0utwest)
A series of episodes that look at databases and the world from a data profesional’s viewpoint.

I believe this podcast started out in April 2014, though I can’t really find a website around it. These are really short (usually under 5 minutes) talks by Steve on very different topics. Although the quality seems a bit low to me, he usually got some nice views and points to consider.

---

 

I hope you found this list useful, if you have any additions, podcasts I should be listening to or remarks, please let me know via this blog or via Twitter!

SSAS Deployment Error: The Following System Error Occurred

Before I made the switch to Pulse I never had that many experience with Analysis Services cubes (neither Multidimensional nor Tabular). After making the switch from integration (with Microsoft BizTalk) I did start out with Power Pivot at ADA, on which I also wrote a few blog posts, about CALCULATE, which is The Queen of all DAX functions according to Marco and Alberto.  But for the other projects we did at ADA it just wasn't a direction we took, maybe also due to the lack of experience with SSAS.

The last months I got involved more in Multidimensional as well as Tabular and therefore also ran into other issues lately, the latest I will describe here.

Problem

During deployment of an SSAS multidimensional cube in Visual Studio I received a very descriptive error message saying (in Dutch): "The following system error occurred:". And that was it. No description of any kind. The output window of Visual Studio did mention some long error number, but that turned out to be not very useful (read: useless).

Also, when a key user wanted to change the rights of a cube he also received the same error. He was trying to give a specific person (UserA) rights to the Purchase role. Although UserA could be added perfectly fine to the Administrator role for example, adding UserA to this role would fail with this error.

Problem solving

As you can see in the screenshot of my Role Properties window below there is a SID showing instead of a user name, but in the Properties window of the key user the user name was correctly displayed. After seeing this SID I immediately thought of a user that was no longer valid/active in AD, but let's see what steps I took to come this far in the first place.

The first thing I tried was ignoring the error in VS and continue the deployment. That worked but I was unsure what gave me the error and if the deployment itself was properly done. I also noted that every cube gave this error in deployment, so it wasn't a cube-specific thing.

Then my colleague Ron pointed me to the possibility of deploying the .asdatabase-file, which is located in the bin directory of the solution.

This .asdatabase-file is like the ispac-file for SSIS, it's the output of the project when built in SSDT. You can either start the Analysis Services Deployment Wizard or double click the .asdatabase-file. The Microsoft.AnalysisServices.Deployment.exe that is used to start the wizard can be found in the following location, where 120 stands for my SQL version (2014).

After going through the first steps of the wizard brings me at the Options for Partitions and Roles:

The important part here is the bottom selection: "Retain roles and members". This means existing roles and role members in the destination database are retained and no new roles are deployed. After having selected this option the deployment succeeded without any errors.

TL;DR: The Solution

Delete the SID from the Role Properties. Also make sure to delete it, or check that it doesn't exist, from the roles Membership tab in Visual Studio.

After the cleanup I was able to add UserA to the Role again and also the deployment of the project in Visual Studio was flawless.

My Takeaways For SQLSaturday 551: #SQLSatHolland

Finishing this post was on my list for quite some time now, but I gave my post last week about the Technical Preview for Power BI in SSRS priority because of the "hotness" of the topic. So without further ado, here's my SQLSatHolland write-up.

It's that time of year again: it was SQLSaturday in the Netherlands, a.k.a. SQLSatHolland!

It's always nice to meet up with SQLFamily, (Twitter) friends/old colleagues and new friends.

This year I also volunteered for the first time by being a timekeeper for the speakers and making sure the session evaluation forms were distributed before the sessions started and collecting them at the end of the session. It was a good and fun experience and I will try to keep giving back more to the community where I can.

I wrote down some notes of the following sessions:

If a machine can learn, why can't YOU learn Azure Machine Learning? by André Melancia (t)

This was an introduction into AML by André where he started out with some theory and explained what ML is not and the difference with AI. We learned how to create a free Azure subscription, set up a ML workspace and start using ML Studio, which is very much alike the Integration Services graphics of connecting processes through intermediate steps using arrows. He then walked us through the demo of creating our first model and experiment in ML.

Using PowerShell for SSIS by Joost van Rossum (b|t)

Joost used the first few minutes to explain what PowerShell (PS) is and goes right into the demo's after that. He even ignores the best practice of not typing in a demo, but all goes well fortunately :-) He showed us some neat Star Wars "song" of PS-beeps and also explains what you can actually use PS for: a.o. deploying IsPacs and environments, download packages from the catalog, search within multiple packages/projects and setting authorization.

Azure SQL Data Warehouse by James Rowland-Jones (t)

James starts with explaining what SQL DW is and is not. Analytics, aggregates and large data volumes are the key words when you want to use SQL DW. When NOT to use it: RBAR-processing, OLTP, incompatible formats like JSON and XML. You pay for the data you store and the compute you provision. You can choose between Hash and Round Robin (default) distribution, where you have to take into account that the distribution key is read only.

Continuous Integration and the Data Warehouse by John Tunicliff (b|t)

You can watch (a shorter version of) this session recorded at an earlier conference on YouTube. John talks about the problems why CI is still not a basic part of database development, the main point being the data in the database. TeamCity Build Server, PowerShell and psake is mentioned also for implementing CI, all with different use cases. Another key point to CI is a test framework, e.g. NUnit BI which is an open source framework to test BI solutions. A best practice when using SSDT for database design is not to upgrade projects, but to start over with a clean import and transfer over the pre- and post-deploy scripts.

Q&A BI with Joost van Rossum, Jan Pieter Posthuma (b|t) en Remko de Boer

These Q&A sessions were a try-out to see if we liked them and they could be a succes. Our BI session was mostly questions to the speakers but sometimes turned out to be a discussion between several attendees and speakers. It was really an open hour, and I really enjoyed it. Questions ranged from performance of Azure, Power BI, the new MS Certification paths (which I wrote about here) and the Master Data Services session from Remko, which was a very good session I heared from several people.

Introducing the SQL Server 2016 Query Store by Enrico van de Laar (b|t)

I didn't actually attend this session, but rumors say it was an exciting one so I have to share it with you. Let's start with some pictures, they say more…

It all started when he was about to begin with his session:

That time when Windows 10 decides to install updates automatically... pic.twitter.com/GZT5ew3Gvf

— Klaus Aschenbrenner (@Aschenbrenner) 15 oktober 2016

He even did some Windows updates that morning "to be sure". Well, that clearly didn't help…

The view for 50 minutes of my Query Store session. thankfully it still turned out good thanks to a very interactive audience! #SQLSatHolland pic.twitter.com/SLlSaPAzNV

— Enrico van de Laar (@evdlaar) 15 oktober 2016

Poor man's Query Store by @evdlaar :-) pic.twitter.com/QNGMcPGPMn

— Klaus Aschenbrenner (@Aschenbrenner) 15 oktober 2016

What I heard from a lot of people (and also from Enrico himself), is that the session still was great and he turned it into a Q&A. He even turned to the whiteboard to get the picture across! Well done for the professional approach and for thinking in solutions when this happened.

My presentation and demo's from #SQLSatHolland are online now https://t.co/lBKFD3LZBj. Now you can "see" what I talked about :-)

— Enrico van de Laar (@evdlaar) 15 oktober 2016

If you want to know more about one of the sessions listed above or are just interested in any of the other sessions of SQLSatHolland, the session materials can be found at the SQL Saturday website, although at the time of writing not all materials have been added to the site.

I hope to also see you next year at SQLSatHolland!

In Action: Technical Preview of Power BI Reports in SSRS

Yesterday the SSRS team made the announcement that we can now consume Power BI reports inside Reporting Services. You can upload your Power BI files (.pbix) to the Report Manager, or deploy them to the Report Server from Power BI Desktop if you like.
To start off, I really like the features and the things you can do, however I also found some issues I will be talking about later.

How to get started

The SSRS team did a pretty good job describing How to get started with the Technical Preview, so I won't repeat that here, just read their blog.
A few caveats:

Seriously Microsoft #Azure, this error message is not helping. pic.twitter.com/wCFsSmZW2h

— Koen Verbeeck (@Ko_Ver) October 27, 2016

• Your virtual machine name in Azure needs to be all lowercase letters, in Chrome the error message displays correctly, but in IE the error message you receive is not very helpful, according to Koen.
• I used West Europe as my Azure Region, so I had to login to the RDP as WESTEUROPE\nvvroenhoven

After having gone through the setup of your Azure VM you'll have to wait 10-20 minutes for your VM to be provisioned and you're good to go.

What does it look like?

After logging in to your VM you can open the report manager via the SSRS Preview shortcut on the desktop and you end up with an overview of the available KPI's, Mobile reports and Power BI Desktop Reports available on the server:

Open a Power BI Desktop report

Clicking on one of the PBI Desktop Reports gives you the Power BI report (which was created with 1 page) in the report manager interface:

Edit a Power BI Report

You can edit the reports in Power BI Desktop by clicking on the 3 dots in the upper right corner of the report tile in the web portal and clicking "Edit in PowerBI Desktop". By the way, I believe there's a space missing between Power and BI :-).

When you open the report in PBI Desktop you also get a pop-up saying they're busy opening your report, the links in that pop-up are still on the ToDo list I guess because they are pointing to https://go.microsoft.com/TODO :-)

SSAS Live connection

As stated in an earlier announcement by the SSRS team: "This preview supports Power BI reports that connect "live" to Analysis Services models – both Tabular and Multidimensional (cubes). We plan to extend support to additional data sources in a future preview." You can see this by looking at the Data Source settings and in the lower right corner of the report in PBI Desktop. As a consequence of connecting live to SSAS you are not able to edit the data model or adjust any queries.

Saving a PBI Desktop file to SSRS

When you save the file you have the option of saving it to disk or directly to SSRS.

When you choose the option of SSRS make sure to type in http://localhost/reports instead of https for this VM.

If you've already saved it once to SSRS and click save (or CTRL+S) afterwards in PBI Desktop, it keeps this setting and saves it automatically to SSRS.

Manage a Power BI Report

You can also manage the report item with the following properties:

Manage Data sources

For now, only live connections to SSAS are supported, but that will certainly change in the future. You can also use credentials to connect to the SSAS instance, but for now I'll use the default settings. 

At my first attempt to test the connection I got an error message, the next attempts did work correctly, no idea why that happened.

Manage Security

You can also customize the security for a specific report, before doing so you get a confirmation dialog box to confirm your settings differing from the parent folder:

After I clicked Customize security, but didn't alter anything, I started to run into some issues I'll describe here.

Issues with the Technical Preview (released October 27th)

Test connection

I encountered this error when I tried to test the connection to the SSAS Tabular cube. The second attempt did work correctly. I made no changes to the settings, I only clicked around a bit.

Customize security

After clicking the button "Customize security" I tried opening the report (I renamed the report to Sample Sales Report 2) again but the Power BI logo kept displaying.

Opening the report in PBI Desktop also didn't work and resulted in the following error:

Also when I open the .pbix-file from explorer and try to save it to SSRS I get the following error:

Any of the other reports (where I didn't alter the security settings) opens and saves perfectly fine. So in my opinion these errors all relate to the same issue with the security settings.

After undoing the security changes, so clicking on "Use same security as parent folder" everything was working fine again.

Conclusion

All in all some great new features have been added! I bumped into some minor issues that I'm sure will be resolved in later versions.

Suggested reading: MDX Date Picker, MS Certification Paths, Dynamics 365

I found some posts, news and came across some issues you might be interested in.

---

I'm creating some reports for a client and am working with SSAS Tabular. I therefore have to deal with MDX based parameters and I wanted to have my date parameter to resemble a Date Picker as you normally would have when using SQL Server as a source:

• Calendar Date Picker for MDX based SSRS Reports on MSSQLTips.com

---

Microsoft has changed their MCSA and MCSE exams by aligning to industry-recognized areas of competence. There's no need anymore to recertify every (three) year, but you can re-earn you certification each year. Due to the fact that the technology changes faster than a few years ago, (SQL Server had changes in the way updates were rolled out earlier this year, Azure changes almost every week) they decided to switch to a more aligned model for certification.

Certification paths

The Data Management & Analytics Path is most appropriate for me and in detail looks like this:

What this means is you will have to earn one of the MCSA's listed here and then take one elective exam to earn the MCSE. You can then re-earn the MCSE each year by taking a new elective exam and in this way adding knowledge every year.
The Microsoft Learning team is already developing a solution where some exams will be eligible for a retake every (calendar) year with rapidly changing technologies, for example Azure.
You can find more information here:

• Microsoft Streamlines Technical Certifications by Larry Kaye on Born To Learn
• Top 12 Questions About the New Streamlined Certification Paths by Larry Kaye on Born To Learn
• Microsoft Certification Changes by James Serra on his blog

---

Since I made the switch to Pulse I started working with Dynamics AX, Microsoft's ERP system. This Tuesday Microsoft had a First Look event at the AXUG Summit 2016 on (the new) Dynamics 365 for Operations, which is the new name for Dynamics AX. Dynamics NAV is also renamed and is now called Dynamics 365 for Financials.

I came across a very good blog by Fredrik Sætre (b | @AxFredrik) which summarizes the First Look event very well. He speaks about the Common Data Model that will be replacing the Entity Story introduced earlier in AX 2012 R2 and will be the core of Dynamics 365, for AX, NAV and CRM.

Remote Session Was Disconnected Because Client Access License Stored on this Computer Has Been Modified

The last day I was working at a client and tried to login via a remote desktop session. Although this was working earlier, I now suddenly got this error:

The remote session was disconnected because the remote desktop client access license stored on this computer has been modified

My colleague could still log in so the connection to the desktop was not an issue. After searching a little bit I stumbled upon this article.

The steps you have to follow are:

• Edit the registry (if you have no clue how, please stop reading and ask someone from IT to do it for you)
• Just to be sure, backup the registry or create a restore point
• Delete the key located at:

HKEY_LOCAL_MACHINE\Software\Microsoft\MSLicensing
 

Now the next time the client connects to the server, it will obtain another license.

Windows 10 Black Lock Screen

For the solution, scroll down, for the story, read on.

After returning from holiday last week I had some Windows Updates to install and afterwards my lock screen was changed. Whenever I locked my computer it would show up like below.

Black Lock screen without background

 And when I then want to log in and entered the sign-in screen, the Windows spotlight background appears, as I expected in the previous screen.

Sign-in screen with background

After some searching I couldn't find this specific issue so I started looking at the Lock screen options myself.

You can open the Lock screen options by typing "lock" after pushing the start button and clicking Lock screen settings, or opening the Action Center by clicking on the notification icon and clicking All settings > Personalization > Lock screen.

Action center

Either way you should end up in this screen:

Lock screen settings

I found out that the slider on the bottom, the one that reads "Show lock screen background picture on the sign-in screen", is the culprit. Apparently this also makes the background go away at the lock screen, they could've mentioned that IMO…

TL;DR

By switching the button in the Lock screen settings to Off, the background will appear again in the lock screen.

HTH

My Reading Goal Journey

I created a page over here to keep track of all the books I read.

Before I start off this post I have to mention Adam Saxton (b | t), also known as Guy in a Cube, because this post, and the idea of setting a goal for reading was inspired by his post and video a few weeks back. If you don't know who Adam is, check out his blog on guyinacube.com or one of his video's on his Youtube channel.
In his post, Adam talks about his reading journey, and that he wants to get some regular reading done.

As I have followed business leaders and entrepreneurs, a common theme has come up. They all read regularly, and a lot!

Fast(er) reading
As Adam states in his post that he's a slow reader, I'm also not one of the fastest readers myself. I did start out with some (fast) reading techniques on lifehacking.nl earlier this year which already helped me in reading a little bit faster. The site is in Dutch, but you can probably find similar sources in English or translate it.
I'm currently at the stage of reading at 90 bpm and 2-3 stops per line. At that speed I can notice I stop the subvocalization (inner speech), which is a big part of slowing readers down. Sometimes it's hard to keep following the rules of speed reading, I'm having a hard time with regression (looking back in the text). So this reading goal is a good place to keep practicing those things and progressing even further.

My goal
For my goal to be SMART, I'm going to start out small and set my goal to 25 pages a day for 100 days, that will take me to October 11th, because I already started last Monday.




My Reading Journey
As time passes, I will keep you posted on how my journey progresses and update this post with new books I complete.

Updated on February 3rd, 2017
I created a new page called My Reading Journey where I'm keeping track of my progress.

SSIS: The Buffer Manager Failed a Memory Allocation Call

I recently made the switch to Pulse (you can read about it here), so I am working with lots of new clients lately. One of those clients was having issues with refreshing their data warehouse and SSAS cubes. The process usually takes around 30 minutes to complete, but the last week it would easily take between 5 and 12 hours. Not really the way you want it to go, right?

Problem
One of the packages in SSIS was taking ages to process so I used sp_WhoIsActive by Adam Machanic (b | t) and came to the conclusion the process had a wait type of PREEMPTIVE_OS_WAITFORSINGLEOBJECT, which can be found in sys.dm_os_wait_stats.

Jonathan Kehayias (b | t) mentions on MSDN:

For a thorough understanding of (Non-)Preemptive waits in SQL Server, Pinal Dave (b | t) has written an excellent blog post here. A little excerpt:

PREEMPTIVE: Simply put, this wait means non-cooperative. While SQL Server is executing a task, the OS interrupts it. This leads to SQL Server to involuntarily give up the execution for other higher priority tasks. This is not good for SQL Server as it is a particular external process which makes SQL Server to yield. This kind of wait can reduce the performance drastically and needs to be investigated properly.

You see the word drastically there? That's kinda what happened :)

Continuing with my investigation, I executed the source query from the package manually in SSMS and it took around 20 seconds. Executing the package in SSDT succeeded but resulted in the following two informational messages:

The buffer manager has allocated # bytes even though the memory pressure has been detected and repeated attempts to swap buffers have failed.

The buffer manager failed a memory allocation call for 65536 bytes, but was unable to swap out any buffers to relieve memory pressure. # buffers were considered and # were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.

As the second message states there is not enough memory available, Well there should be, because I remembered there recently had been a memory increase from 16 to 32 GB on the server. But about that RAM, it is shared across the server... and between different applications... That started me thinking about the max server memory for SQL Server and the memory left for the rest of the server (OS, SSRS and SSIS!), because they're all running on the same server.

Solution
The maximum server memory can be set by editing the Server Properties (in SSMS):

or by using plain T-SQL. The beneath example sets the maximum memory to 4 GB:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

When the memory increased to 32 GB recently, the max server memory was also set from 13 to 26 GB, so the memory available to processes other then SQL Server didn't increase much. I changed the memory limit back to 20 GB to release an extra 6 GB to the rest of the apps running on the server. This way the (DTExec-)process for SSIS had enough memory to perform the tasks in-memory and the nightly process was running as normal again.

HTH

Saying Goodbye to ADA and Hello to Pulse!

As you may have noticed, I'm starting tomorrow, June 1st, at Pulse (t | web) in Venlo, that's near the border with Germany. I had a couple of days off so my last day at ADA was last week. I already said goodbye to my colleagues, had some time to clear my head and spend some extra time with the kids, which is always great!

ADA
I have to say I enjoyed my time at ADA (t | web). I really liked my colleagues and the laughs I had with them. That doesn't mean I was always doing what I really wanted or liked most. I understand that the best jobs and the nicest customers aren't always available (for me). But in the long run you have to have fun and find a challenge in what you do!

Pulse
So I decided to take a leap of faith and that's how I found Pulse, or actually they found me (ok, actually a recruiter found me). I'll be fulfilling an MS BI Consultant role there.
Pulse is an MS Gold Partner with Dynamics AX (ERP) on which they also built a BI framework. And now with the content packs of PowerBI the availability and quality of the solutions will only get better I believe.

Hopefully I will get (yes I know, I have to make time) a bit more time to blog and I'll let you know how my  choice turns out!

Nicky

How To Insert a TAB Character in OneNote

As you might know, clicking the TAB button on your keyboard results in a table in OneNote. What if you really want to insert a TAB character? Well, you need a trick for this.

• Create an Auto-correct rule that turns your chosen character sequence (e.g. "\t") into a regular TAB.
• Open Notepad++ (or the text editor of your choice) and type one TAB. Select it and copy it.
• Go to OneNote and click File | Options | Proofing | AutoCorrect Options.

• In the dialog, put your desired sequence of characters (e.g. \t into the "Replace:" field.
• Paste the TAB that you copied from Notepad++ into the "With:" field and click Add.
• Click OK 2 times to close the dialogs and you're done!

Now, any \t followed by a SPACE will be replaced by a regular TAB.

HTH

I found this answer on the MSFT community site.

The Best of PASS Summit 2015

If you haven’t been to the PASS Summit in Seattle last year (like me :(), you might want to check out the recordings of Summit 2015, as they are now available on PASStv.

Furthermore, PASS has selected the top 10 recordings (I believe based on reviews of the sessions) for you to look at over at Youtube. The links to the video’s can be found at the PASS website, go check them out!


My personal favourite is from Itzik Ben-Gan:

Have fun!

SSRS: Go Back Button in an Expression

ProblemLast week I was making a drillthrough report in SSRS for one of my clients and I had the need to make a (browser-like) Back button, to go back to the parent report for example. I know I did this before and it had something to do with history and (-1) but I couldn’t recall the syntax. So I thought I’d finally capture the solution in a blog post!

Solution
The key to the solution is the <Action> property of the <Text Box Properties>, which you’ll have to set to <Go to URL> and edit the expression with the following code:

=”javascript: history.go(-1)”

Do mind the casing here, because it’s case sensitive!

SSIS: An Item With The Same Key Has Already Been Added

Problem
Recently I was working on a new project and was trying to deploy my SSIS project with several packages to the catalog for the first time. I received the error when trying to build the project:


Explanation
This occurs when e.g. you copied a (package) connection manager to another package and later promoted one of the 2 to a project connection.
Let’s say you have package A and B, both with the same package connection manager ConMgr. When you promote ConMgrof package A it will become (project)ConMgr, but in package B it will still be ConMgr, the package connection, and the new project connection will not be shown.

Solution
(I found my solution @ SO.) By deleting the package connection ConMgr (in package B) the project connection will become visible in the package and (in my project) succesfully replaced the connections in all data flows. It could be that you have to go through all components that referenced the old package connection to redirect them to the (project)ConMgr instead.

Hope that helps!

SSRS: Sorting in Particular Order in a Report

This week I had the need to order values in a matrix in SSRS in a particular order. The names need to be ordered in alphabetical order, except for one name that always needs to come first. I’ll show you how I did it with a sorting expression in the Group.
I’ve taken a (light-weight) version of AdventureWorks for the examples.

Let’s take the Sales.ProductCategory and Sales.Product tables as an example. I’ve counted the number of Products of the 4 main (parentless) ProductCategories:


The sorting in the Row Group Name is automatically done from A-Z. If you want it in any other order you’ll have to set it explicitly. You can either pick a(ny) column and set it to sort A-Z/Z-A or you can enter an expression. We’re now going to implement the latter.
When you click on the arrow of the Row Group Name in Row Groups box at the bottom of the screen and click on Group Properties… you’ll end up with the following screen:


We want to leave the existing sorting expression as it is, because we can use it later. Add another row and click on the fx button of the new row to open up the expression dialog box:


By entering the formula you see in the picture above, you’re saying to the sorting engine: Whenever the value of the field Name is equal to “Clothing”, give it a sort order of 0, otherwise give it a sort order of 1. When ordering from A-Z 0 comes before 1 so “Clothing” always comes before the rest. You can adjust this to your needs ofcourse to make a specific item come last, or sort all the items in particular order by nesting IIF statements.
When you click OK and click on the up arrow to sort on this expression first you end up with the following sorting options:


Sorting on the Group is first done on the expression, so “Clothing” comes before anything else, second alpabetically on the Name, so the rest of the values are placed after ”Clothing” from A-Z. This gives me the same values as before, but now sorted differently: