SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Richie Lee's SQL Stuff

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

PoshSSDTBuildDeploy Update – Now With Dynamic SqlCmdVar Updates

One of the big challenges in the whole “build once/deploy many” ideology is the need for environment configuration. And this is true with SSDT-based database deployments. Happily the variables are stored in the publish.xml file.

And so today’s release of PoshSSDTBuildDeploy added a switch on the Publish-DatabaseDeployment function. When included,… Read more

0 comments, 104 reads

Posted in Richie Lee's SQL Stuff on 28 November 2017

PoshSSDTBuildDeploy Update – Now With LocalDB!


Late on Friday I published a PowerShell Module that gives working examples of using DataToolsMSBuild. All very interesting, go and have a read of my previous post.

One of the things I really want the module to do is to set up all software required for building/deploying. And… Read more

0 comments, 179 reads

Posted in Richie Lee's SQL Stuff on 28 November 2017

Kill a Connection, Session, or Command in Analysis Services

One of the useful features of the SSAS Activity Monitor is the ability to cancel sessions and queries. Though there is no obvious way to do this through SSMS you can in fact write an xmla query to kill either the session, connection or command:

View the code on Gist Read more

0 comments, 172 reads

Posted in Richie Lee's SQL Stuff on 10 October 2017

Failed SSIS Build – DtsRuntimeException


I was helping a team debug a failed build today. Despite the fact that the build compiled on the devs box – something that’s become almost cliché to hear these days – it would fail using devenv.com via cmdline. Annoyingly, there was no errors outputted. So i cloned the… Read more

0 comments, 177 reads

Posted in Richie Lee's SQL Stuff on 6 October 2017

Automate Parameterising SSIS Connection Managers


Back when SQL Server 2012 was known as Denali, one of the new and exciting features released was that all properties of a connection manager in a SSIS project were parameterised. This even happened automatically when they got deployed to a SSIS Server. Great! The feature was also written… Read more

1 comments, 1,476 reads

Posted in Richie Lee's SQL Stuff on 6 October 2017

What Do You Get if You Unpack an Ispac?

I’ve spent what feels like a lifetime working on automating SSIS deployments, more specifically SSIS packages that have been created in Visual Studio that use the project deployment model. If your SSIS Project is using this model you’ll know that the output of a build is an .ispac file. But…

Read more

0 comments, 192 reads

Posted in Richie Lee's SQL Stuff on 4 October 2017

Deploy SSAS Cubes With PowerShell


Recently someone got in touch with me about one of my earliest posts on my old blog. I realised I had made a mistake on that post and had updated. But I also realised that the script itself is actually part of a PowerShell module. So wanting to make… Read more

0 comments, 142 reads

Posted in Richie Lee's SQL Stuff on 3 October 2017

Deploy Dacpacs With PowerShell

Addendum – There is a repo on GitHub which I wrote and recommend using for build/deployment of DACPACs. Read up on my last post about PoshSSDTBuildDeploy. But for a quick and dirty overview, read on…

When it comes to managing Dacpacs with automation (eg either by deploying or extracting… Read more

0 comments, 171 reads

Posted in Richie Lee's SQL Stuff on 26 September 2017

SSIS Package Execution in Azure is Out Now

It’s been a long time coming, but now SQL Server Integration Services is in Public Preview on Azure!

I’ve written about it elsewhere in greater depth, but here are the headlines:

Read more

1 comments, 225 reads

Posted in Richie Lee's SQL Stuff on 25 September 2017

Restore BACPAC to SQL Azure


2 posts in 1 day? When you’re hot, you’re hot!

I’m actually writing this one whilst I’m waiting for a BACPAC to restore to Azure. It’s actually incredibly easy: use SQLPackage! Setthe /SourceFile to your bacpac, and use the connection string published on Azure, and you’re done!

View the… Read more

0 comments, 182 reads

Posted in Richie Lee's SQL Stuff on 22 September 2017

How To Update Visual Studio 2017

(AKA how I lost a morning)


This morning I needed to update my version of SSDT, but before I could do that I needed to update Visual Studio 2017:

Awkward wording of an error message aside, let’s update Visual Studio 2017.

But how?

I just started VS 2017, and… Read more

0 comments, 343 reads

Posted in Richie Lee's SQL Stuff on 22 September 2017

Truncate vs Delete in SQL Server: What’s the Difference?

I’ve been using TRUNCATE TABLE to clear out some temporary tables in a database. It’s a very simple statement to run, but I never really knew why it was so much quicker than a delete statement. So let’s look at some facts:

  1. The TRUNCATE TABLE statement is a DDL operation,…

Read more

1 comments, 61 reads

Posted in Richie Lee's SQL Stuff on 4 September 2017

New Baby Musings (AKA Things I’ve Thought Upon On Sleepless Nights)


One month ago I became a dad for the 3rd time. And so my spare time, which has always been a premium, has shrunk even further. But here’s some thoughts on some stuff:

SQL Stuff

  • SSDT Supports Visual Studio 2017. And not only that, SSDT supports SQL Server Database,…

Read more

0 comments, 24 reads

Posted in Richie Lee's SQL Stuff on 2 September 2017

Sample Databases For SQL Server

As a blogger, one thing that I appreciate greatly is examples of technical features. This is mainly because

  • technical features can be complicated to explain succinctly
  • I myself learn best by doing, not reading how to do something
  • It proves that I’ve made an effort into understanding how something works

Read more

0 comments, 37 reads

Posted in Richie Lee's SQL Stuff on 1 September 2017

My Evening With SQL Tool Service


I’ve been writing a PowerShell Module that will handle deploying SQL Server Agent Jobs by using the Agent namespace in SMO. But outside of work I’ve been very much into playing around with Linux: I’m slowly making my way through Linux From Scratch; I’ve got Pi-hole setup… Read more

1 comments, 30 reads

Posted in Richie Lee's SQL Stuff on 13 July 2017

LocalDB DSC Script


So, there are many different product SKUs of SQL Server available, and generally when developing it’s probably best to use SQL Server Development Edition, even when it used to cost you money to get a product key. The disadvantage of using it though is that it is a large… Read more

0 comments, 40 reads

Posted in Richie Lee's SQL Stuff on 12 July 2017

Do Not Trust My GitHub Projects


Recently I’ve revisited one of my GitHub projects, TurboLogShip. I’ve been wanted to write a presentation around what it does and why it’s actually really important and super useful for people using log shipping in SQL Server. Essentially it speeds up log ship restoring to a readable secondary to… Read more

0 comments, 49 reads

Posted in Richie Lee's SQL Stuff on 23 April 2017

Dropping Foreign Keys The Lazy Way


So, on another blog a long time ago, I wrote a couple of posts about foreign keys and what step can be taken to suspend them when we are trying to load/delete data into them. There’s the elegant way and the brute force way.

Hang On…

“But aren’t they… Read more

0 comments, 29 reads

Posted in Richie Lee's SQL Stuff on 18 April 2017


This is hardly breaking news, but it’s important to know: when using NOLOCK table hints in SQL Server, it can cause blocking. Yep, you read that right. Given the fact that the table hint is called NOLOCK it’s counter intuitive to what you’d instinctively think, but it’s fact and by… Read more

0 comments, 33 reads

Posted in Richie Lee's SQL Stuff on 17 March 2017