Here I’m talking about 1NF, not only in the context of a table, but also in the context of a DB or even an enterprise.
Category: Development
SSIS: EventHandlers
This is a quick intro to event handlers in SSIS. Continue reading “SSIS: EventHandlers”
T-SQL: Switching data with a Single Partition
This is the fastest way to move data to a new table. Of course, you have to move all the data, but it’s quite useful. Tables are partitioned in SQL by default so this is a great technique.
Microsoft SQL Server – T-SQL: Switching data with a Single Partition
This is the fastest way to move data to a Continue reading “Microsoft SQL Server – T-SQL: Switching data with a Single Partition”
Powershell Classes: Part 5 – Inheritance con’t
Here we finish up the discussion on inheritance and we Continue reading “Powershell Classes: Part 5 – Inheritance con’t”
Powershell Classes: Part 3
This is part 3 of our series on how to
create classes in powershell.
We start by going over homework and then get into hidden classes and creating a fire hazard in our code.
Watch Part 1
Watch Part 2
Watch Part 4
Watch Part 5
XP_CmdShell Default Proxy
Most DBAs don’t think of it, but xp_cmdshell has a
default proxy account. In fact, this proxy is responsible for its permissions, and actually follows Microsoft’s current external script security strategy. So xp_cmdshell isn’t unique and it doesn’t do anything special security-wise.
Here you’ll see how to test which credentials xp_cmdshell is using. It’s actually pretty easy.
Redirect command line output in SSIS script task
Here I’m going to show you how to use an Continue reading “Redirect command line output in SSIS script task”
Master Stored Procedure Workshop: Part 2
This is part 2 of the previous session from SQLSaturday Continue reading “Master Stored Procedure Workshop: Part 2”
Master Stored Procedure Workshop: Part 1
This is from SQLSaturday Richmond, VA on 3/30/2019.
I’m teaching stored Continue reading “Master Stored Procedure Workshop: Part 1”
REGEX for DBAs
This is the version of my session that works with Continue reading “REGEX for DBAs”
Database Unit Testing with SSDT
DB unit testing has been a cloud for a long Continue reading “Database Unit Testing with SSDT”
Regex Word Boundaries
Here I’m going to show you how to use regex word boundaries. Word boundaries allow you to match entire words, or even words that Continue reading “Regex Word Boundaries”
Regex Lookarounds
In regex you can perform lookarounds to check for matches that don’t get consumed. These are zero-length assertions that merely report the match. Here Continue reading “Regex Lookarounds”
Find double words in regex
Here I show you how to use a backreference in regex to be able to find double words in regex. What you’ll learn how to do Continue reading “Find double words in regex”
What is a Stored Procedure
Here Jen explains what a stored procedure is, and how to create a basic one with no parameters. She also compares it with other Continue reading “What is a Stored Procedure”
Insert Into vs. Select Into
Knowing how and when to use Insert Into or Select Into can quite often make or break your load process. Here I show you Continue reading “Insert Into vs. Select Into”
Best Habit for Coders
There’s a step you can make in your code to improve it many times over. It’s an easy step to take, but it can Continue reading “Best Habit for Coders”
Data-driven Subscriptions in SSRS
Here I get into an elegant method for giving users a report that has a dynamic distribution list. As DBAs we don’t want to Continue reading “Data-driven Subscriptions in SSRS”
Querying Dev Metadata
Sometimes you need to get info on objects you’ve created. Here you can see how you can query for this info, and even learn Continue reading “Querying Dev Metadata”
Deploying SSRS Reports
One of the most common questions I get is how to deploy reports in SSRS. Here I show you pretty much everything you need Continue reading “Deploying SSRS Reports”
Dealing with Sql_variant in SSIS
Every now and then you come across something you don’t want to deal with that you’re forced to handle in your SSIS packages. Here Continue reading “Dealing with Sql_variant in SSIS”
Intro to SSIS
Intro to SSIS.
UNION and UNION ALL
Here I talk about UNION and UNION ALL, and the different rules about using ORDER BY, GROUP BY, SELECT INTO, and INSERT INTO with a Continue reading “UNION and UNION ALL”
Developing Dynamic SSIS
Dynamic SSIS.
Designing Efficient Functions
Here I get on my cert soapbox again. I was going through a practice cert exam and came across an answer to a question Continue reading “Designing Efficient Functions”
Check if File Exists
Inspired by a user question I decided to make a quick vid on how to use a script task to check that a file exists. Continue reading “Check if File Exists”
Ground Zero SSIS: Class 1
This is the 1st class I’m teaching at the NTSSUG for ground zero SSIS. I hope to have 6 in all, but it may Continue reading “Ground Zero SSIS: Class 1”
OR is bad, IF is good
Here I tell you about a common mistake – using OR instead of IF – and show you that IF is better.
Clean Package Design
This is a nice little primer on designing clean packages that are easier to support. Get rid of the sea of arrows that afflict Continue reading “Clean Package Design”
HTML: Table Basics
Continuing with basic HTML, I talk about table basics here. I’m not kidding; this is basic. So if you already know anything about Continue reading “HTML: Table Basics”
Ground Zero SSIS: Class 2
In this 2nd class we discuss how to decide whether you should architect your table loads in single or multiple packages. We also start Continue reading “Ground Zero SSIS: Class 2”
Data Mining 101
SPEAKER: Andrew Minkin. Drew stopped by to teach all of us mortals about data mining. There’s so much more to cover in this Continue reading “Data Mining 101”
Ground Zero SSIS: Class 3
Here in another live presentation to NTSSUG we discuss where to hold your query inside your packages, as well as get into flatfile error handling.
T-SQL Code Sins
SPEAKER: Jen McCown. Do you know how to set yourself for good coding practices? Well Jen does. Come see the advice she Continue reading “T-SQL Code Sins”
T-SQL Codig Sins: SQL Saturday #41 – Atlanta
Come see Jen teach T-SQL Code Sins at SQL Saturday #41 in Atlanta. You see these huge mistakes in many shops so come take Continue reading “T-SQL Codig Sins: SQL Saturday #41 – Atlanta”
Fix SSIS Pkg Import Errors
This is a live troubleshooting video. I had an error importing an SSIS pkg so I turned on the camera and worked the issue Continue reading “Fix SSIS Pkg Import Errors”
Intro to ASP.NET
Intro to ASP.NET This is a decent intro to ASP.NET. Personally I don’t think it’s the best one you’ll ever see, but it’s ok. You’ll Continue reading “Intro to ASP.NET”
Source Control in SSMS
Here I show you how to setup source control in SSMS and who should and shouldn’t use it.
Dirty Tricks: Text Manipulation
Dirty tricks are the little tricks we use that aren’t official, may not be “proper”, but are insanely useful. Today we look at script building Continue reading “Dirty Tricks: Text Manipulation”
Deploy Packages
Jen and I sit up very late one night and discuss how do deploy SSIS packages. If you don’t know anything at all about Continue reading “Deploy Packages”
Dirty Tricks 2: Get List From Command Line
Here’s one of my dirty tricks: getting a list of folders or files from a command prompt, and formatting it for use in code using Continue reading “Dirty Tricks 2: Get List From Command Line”
TSQL: Grouping Basics
Here’s a ground level intro to grouping, with a lovely 80s arcade theme. This is a reshoot of the original Grouping Basics video (now Continue reading “TSQL: Grouping Basics”
T-SQL: CASE Statement
Here’s an intro to case statements, in the form of a conversation with Sean. I cover simple case and searched case, and the special Continue reading “T-SQL: CASE Statement”
Using TFS in SSMS
Here I show you how to set SSMS up to work with Team Foundation Server
T-SQL: Find Last Day of Month
Follow Jen as she steps you through 3 progressively elegant solutions for finding the last day of the month.
Grouping Sets
Here Jen and I go over grouping sets the only way we can. I hope you guys enjoy us late at night.
T-SQL: Over() and Partition By
Show how to use OVER and PARTITION BY to get groups of data with aggregation.
Relational Division
Here we discuss relational division, the counterpart to relational multiplication (cross joins). We’ll hit a couple of examples, using subqueries to implement two different Continue reading “Relational Division”
Insert Non-duplicate Rows
Here we work our way through a coding scenario—inserting rows—that should illustrate the process of solving a problem from the simplest case to your intended Continue reading “Insert Non-duplicate Rows”
Relational Multiplication
Here’s a quick talk on relational multiplication that gets us to take a closer look at what our joins are really doing.
Sending HTML SQL Emails
I’m actually excited to be able to bring you this video because this is a topic that comes up quite often in DBA circles. Continue reading “Sending HTML SQL Emails”
SSIS Scripting Languages
This covers how to change the scripting language in SSIS for Katmai.
Commenting Tribal Knowledge in SPs
There is a lot of tribal knowledge that accumulates in any organization. Here I discuss some ways to document your SPs so you can Continue reading “Commenting Tribal Knowledge in SPs”
Execute SQL Task with Parameters
This video discusses how to pass parameters to queries and SPs inside the Execute SQL Task. It doesn’t teach the basics of the task. Continue reading “Execute SQL Task with Parameters”
Write Good Dynamic SQL
Here I show you a couple methods for getting around really hard to read dynamic SQL.
Row Count Transform
Here I discuss how to use the row count transform in SSIS and give you some good advice for naming and working with the variable Continue reading “Row Count Transform”
Red-Gate’s Data Generator
A walkthrough of Red-Gate’s new data generator tool.
SSIS Using Variables in Script Tasks
Here I talk about how to setup the script task to work with variables, and how to access them through code once the script can Continue reading “SSIS Using Variables in Script Tasks”
Introduction to ForEach Loops
Here I show you the basics of how to work with ForEach loops. Good stuff.
Creating Files with ForEach Loop
Here I take the intro one step further and show you how to create multiple txt files based off of a looping variable in the Continue reading “Creating Files with ForEach Loop”
Introduction to Sequence Containers
This video takes a look at the basics of using the Sequence Container in SSIS. There’s not a lot to it, but it’s a Continue reading “Introduction to Sequence Containers”
Commenting Tribal Knowledge in SSIS Packages
There is a lot of tribal knowledge that accumulates in any organization. Here I discuss some ways to document your SSIS packages so you Continue reading “Commenting Tribal Knowledge in SSIS Packages”
SSIS Basics
This is a live presentation I gave recently. Here is a bare bones bottom of the barrel discussion on SSIS. This is for Continue reading “SSIS Basics”
SSIS Basics 2: Flatfile Olympics
Here we’re just going to jump into SSIS and do some real practical work. We’re going to learn all about taking things to/from multiple Continue reading “SSIS Basics 2: Flatfile Olympics”
MultiFlatFile Connection Manager
Here I talk about how to use the multiflatfile connection manager in SSIS to loop through flatfiles.
Basic HTML for DBAs
After my last video I got some emails saying that not all DBAs already know HTML. So I decided to teach some basic HTML Continue reading “Basic HTML for DBAs”
Derived Columns
Here I show you how to work with derived columns in SSIS and use it to not only marry columns, but also troubleshoot data flows. Continue reading “Derived Columns”
HTML Styles: Part 1
Here I continue with the HTML basics video by showing you a better way to do styles. I hinted about this in the basics Continue reading “HTML Styles: Part 1”
Connection strings in Package Config Files
Here I show you how to put your data source connection string inside a package configuration XML file. This was inspired by a forum post Continue reading “Connection strings in Package Config Files”
SSIS Variable Scope
Learn how SSIS scopes variables. It’s not a complicated topic, but worth covering.
Forcing Package Failures
This one was by user request to show how you can compare the counts of 2 tables and use it to force the package to Continue reading “Forcing Package Failures”
Simple Backup Procedures
This is a tutorial not only on how to turn your backup commands into SPs, but also the basics of stored procedures are discussed.
Select Into with Identity()
This video shows you how to use the Identity() function to create an identity column on the fly during a select into statement. You don’t Continue reading “Select Into with Identity()”
sp_HelpIndex Shortcut
There’s a nifty keyboard shortcut to help you with things like sp_helpindex. It works with many SPs so have fun.
Building Subreports
I got a user question through email and rather write-out how to do this, I just shot him a quick video. So the quality Continue reading “Building Subreports”
SSIS Data Cleansing
Fix data errors with derived cols and conditional split transformations.