Towering Queries

I think SQL Extended Properties are awesome. In a nutshell, they allow you to attach arbitrary key-value pairs to SQL objects of almost any kind. I've used them for things like simple documentation, to enabling special functionality for maintenance procs, or marking all tables serving a particular function in a system. They're just cool. The biggest pain in the butt IMHO is just managing them; creating them, updating them, and to a lesser extent, viewing them. The biggest problem is scripting them. SQL gives three procs for managing extended properties: sp_addextendedproperty sp_updateextendedproperty sp_dropextendedproperty Each of those does exactly what it says on the tin. The pain is that they will fail if you start in the wrong state. I.e.  Add will fail if the property already exists Update will fail if the property does not already exist Drop will fail if the property does not already exist Which means if you want to add XP management to any re-runnable script, you have

 I had an SSIS package which, as its first step, needed to pull a list of files to process. I did that with a C# Script Task that did that with Directory.EnumerateFiles on a specific fileshare directory. When I executed it locally (either through the Visual Studio project, or through DTEXEC on my local machine) it worked just fine. However when I deployed it to an SSIS Catalog, it would throw an error saying  <Script Task name>: Error: Access to path "..." is denied Same result if I executed it through an agent job on the server. Turns out, I was able to execute it just fine if I were connected to the actual server the SQL Instance the SSIS Catalog runs on. Why does this happen? Hell if I know. But that's the way it is.  For the agent job, it just need to be created as the SA (or an account with a lot of permissions), which my DBA could do. So, if you can get permissions to temporarily log into your SQL box, you can at least verify it will work. Then, if you have yo

Working with SSDT Database Projects can be a pain when you're working with other databases. After creating a recent multi-database solution with 4 Database Projects in it, I spent about a day trying to get rid of a bunch of errors that said something like "Procedure: [someproc] has an unresolved reference to object [someobject]" (there are also sometimes another set of errors which talk about ambiguity between objects, even when you've clearly properly aliased your objects. What follow are some tips for helping resolve some of these errors, because, as with many error messages with Microsoft data products, they can be super misleading. All the examples here are going to assume any databases you want to reference live on the same server. You can reference databases on different servers, but that's a bit weirder, and tbh I haven't found a use case for it yet. Just know, that functionality exists, but you'll have to figure that out on your own. This still m

 I'm a scripting junkie, so I got tired of having to download or copy/paste a gitignore file into my git repos all the time. To that end, I created the script below. The involved function is gitig which downloads a boilerplate .gitignore file from github (i have it defaulting to downloading the VisualStudio .gitignore, but you can change that, or provide your own at call time). The other function is gitinit which calls gitig followed by git init . That way you can initialize a new git repo with a boiler plate .gitignore script with a single powershell command. To install these,  Open Powershell Type "notepad $profile" This will open notepad with your powershell profile Paste the code below at the bottom of that file Save and close the profile file Restart powershell Now you can type gitinit from powershell to create a repository with a boilerplate .gitignore anywhere you want. function gitig( [String]$Name = "VisualStudio", [switch]$Overwrite = $