"How do I contribute to dbatools?" with Drew Furgiuele

Published Tue 06 February 2018 in SQL > dbatools

This weekend I caught up with Drew Furgiuele at SQL Saturday Cleveland and learned how to get involved with the open-source dbatools PowerShell module.

If you don't use dbatools yet, what are you waiting for? It's an amazing community project that will help you automate your SQL Server work with over 350 ready-to-use commands.

After you start using dbatools, you might think of new commands you want to add or other features you want to improve - and the best part is that you can!

Even if writing PowerShell commands isn't one of your strengths, there are many ways you can contribute to this excellent community project as Drew mentions in the video above.

The Fastest Way To Locate Errors In Your SQL Query

Published Tue 30 January 2018 in SQL > Development

n-120570-2

In about 60 seconds you will never debug error messages in SQL Server Management Studio the same way again.

Coming from a .NET background, I'm used to receiving relatively helpful error messages that point me to the precise location of the error in my code:

NET-Error-Message Pinpoint error finding

SQL Server Management Studio does a decent job too - **except when it doesn't. ** Different versions of SSMS provide error line numbers differently.

Watch this week's video on YouTube

In older versions (<= 2012), SSMS provides error location information relative to the batch starting point of the erroneous query. This means if you have a window full of T-SQL batch statements, your error message will be relative to the last "GO" in the editor before the syntax error:

Error-message-in-old-SSMS

In SSMS 2014, batch start locations aren't considered:

error-message-ssms-2014

Starting in SSMS 2016, the error messages revert back to counting from the current batch but also indicate the line number where the batch begins:

Error-message-in-newer-ssms

While all of these error messages point me in the right direction, I'm too lazy to try and remember how each version of SSMS refers to the error location. This becomes particularly problematic when jumping around environments and not being able to use a consistent version of SSMS.

There is one feature that works consistently though that makes navigating to the specific error lines easily.

You can double click on the error message to be taken directly to the line number referenced!

I know this sounds simple, but it's one of those small things I never thought of trying until I saw someone else do it. Little did I know what I was missing out on - especially in those giant hundreds/thousands of line long scripts. but now that I know about it it's the only way I locate my query errors.

This functionality also works if you have multiple errors listed in the Messages window.

But what about if your SQL query is all on one giant line (like if it was copied from somewhere or generated dynamically)? You can use a regular expression to format your query first and then click on the error message to quickly navigate to the correct lines.

I Have A SQL Login - Why Can't I Connect?

Published Tue 09 January 2018 in SQL > TSqlTuesday

tsqltuesdaylogo

This post is a response to this month's T-SQL Tuesday prompt created by Arun Sirpal. Adam Machanic created T-SQL Tuesday as a way for SQL users to share ideas about interesting topics. This month's topic is "Your Technical Challenges Conquered".

DBA Skills 101: SQL Logins

While writing last week's post about efficiently scripting database objects, I decided to make progress towards my 2018 learning goals by figuring out what database permissions were needed for running SQL Server Management Studio's "Generate Scripts" tool.

I thought it would be best to start with a clean slate so I created a new SQL login and database user so that I could definitively figure out which permissions are needed.

Normally I use Windows Authentication for my logins, but this time I thought "since I'm getting crazy learning new things, let me try creating a SQL Login instead."

After I created my login, I decided to test connecting to my server before digging into the permissions. Result?

Watch this week's video on YouTube

I can't connect!

That's right, I tried to connect and I got this very detailed error message :

"Great," I thought. "I should just switch to a Windows Authentication login, those always work for me."

"BUT NO, THEN I WON'T LEARN ANYTHING!"

On to troubleshooting

First things first, I tried retyping my login and password (I know typing in the password of "password" is really tricky but I've made mistakes doing much simpler things).

No luck. Maybe when I created the login I fat-fingered the password?

I recreated the login, making sure I precisely typed the password. Try to connect again...nope.

Ok, ok. I'm missing something obvious. I have this error message though - maybe the internet will know!

I find the exact error message in a blog post by Aaron Bertrand - he's a credible guy, I bet I'll find the solution there!

Nope.

(Side note: the answer is there, just buried in the comments. In my eager "this will be an easy solution" I didn't bother scrolling down that far).

Ok... how about books online? Even though I created the login through the SSMS GUI, I know the T-SQL command to do the same is CREATE LOGIN. Maybe I'll find the solution in the documentation?

No luck.

(Side note again: in hindsight you can get to the solution from the above link, but it's buried two further links deep. While troubleshooting I was in the mindset of "ain't nobody got time for that" - I wanted a solution given to me immediately without having to do any further research!)

I kept searching online, reading through Stack Overflow answers, not finding what I needed.

(Side note (last one, I promise): anyone else having a harder time searching for relevant Stack Overflow answers? It feels like more and more I find questions/answers that are for older versions and no longer relevant)

At this point I was really frustrated. "CREATING A LOGIN SHOULD BE LIKE THE FIRST THING A DBA LEARNS!! WHY IS THIS SO HARD?!?!?!?!?!?!"

At that point I was tired and disappointed that I had spent more time trying to solve this login problem rather than actually figuring out the permissions that I wanted to include in my blog post.

Sleep

I decided to take a break for the night and revisit the problem the next morning.

As expected, I searched the internet for the answer again and somehow my keyword selection hit the jackpot - I found the Stack Overflow answer telling me I needed to set the server to mixed authentication mode:

mixed-authentication-mode

Wow, that was easy.

Takeaway

This wasn't a complex problem. At least, it shouldn't have been a complex problem.

All in all I spent probably 30 minutes trying to figure it out - not the longest amount of time I've sunk into a problem that ended up having a really simple solution.

However, this stuff happens. It's amazing what a fresh (rested) set of eyes can do for solving a problem.

Lesson learned: next time I'm getting frustrated by a problem that I think should be easy to solve, I need to step away from the computer and come back once I have a clearer mindset :).

One Last Technical Challenge (BONUS)

I figured I'd add one more technical challenge to this post: submit a pull request to the sql-docs GitHub.

My rationale was that I couldn't be the only person to have ever been stumped by authentication modes. Maybe I could be helpful to the next person who visits the CREATE LOGIN books online page and give them a hint as to why they can't connect.

Contributing to open source isn't something I've done through Github before, but luckily I had Steve Jones's excellent write up to guide me.

There were no real challenges here since I was just making a simple edit, and low and behold a few days later my PR got merged and is now live in BOL - cool!

A Better Way To Script Database Objects

Published Tue 02 January 2018 in SQL > SSMS

Happy New Year! My New Year's resolution for 2018 is to help you become a better SQL developer.

I want to start off with that today by showing you a much better way to generate database object change scripts.

Watch this week's video on YouTube

If you are like I used to be for YEARS, anytime you want to copy a table, index, etc... you probably right click on that object in SQL Server Management Studio and click "Script as" > "CREATE To":

old-create-to An inefficient way of generating change scripts

This is a pretty easy way to quickly script database objects, however it's incomplete.

For starters, I'm forced to generate the scripts for tables one at a time. Not fun.

Additionally, if I want to script associated objects for that table, like indexes, I have to go to each index and then right-click and select "Script Index as". Ugh.

There Is A Better Way

Instead of using "Script Table as", you can right click on your database and choose "Tasks" > "Generate Scripts...":

generate-scripts

This option brings up a GUI that will allow you to script multiple objects at the same time:

multiple-objects-at-once Look ma, multiple objects at once!

Additionally if you click the "Advanced" button in the final page of the dialog, you will receive many more options for how your objects will get scripted, including the ability to script out the indexes!

script-indexes Ooooooo, ahhhh - everything scripted in one fell swoop!

Using the Generate Scripts Task it's easy to generate table, index, stored procedure, etc... change scripts all in one step - nice!

NOTE: If your SQL User receives an error when trying to generate the scripts, make sure they have the following access:

USE [MyDatabase]
GO

GRANT VIEW DEFINITION TO [MyUser]
GO

GRANT SELECT ON sys.sql_expression_dependencies TO [MyUser]
GO

sql_expression_dependceis

SQL and NOT SQL: Best of 2017

Published Tue 26 December 2017 in Meta

Picture1-e1513781747981

With only a few days left in 2017, I thought it would be fun to do a year in review post. Below you'll find some of my top 5 favorites in a variety of SQL and non-SQL related categories. Hope you enjoy and I'll see you with new content in 2018!

Watch this week's video on YouTube

Top 5 Vlogs

You guys watched over 500 hours of my videos this year, thank you!

Watch this week's video on YouTube

I think the content of this last video is good, but I don't actually like anything else about it. Why did I bother including it then?

It's the first SQL Server vlog I made this year. I'm a little embarrassed by how bad it is, but I keep it up as motivation for myself to see how much I've improved since I started filming videos.

If you ever think you want to start doing something - just start doing it. Keeping track of progress and watching how you evolve is extremely rewarding.

Top 5 Tweets/Instagrams

I'm not a huge social media guy to begin with, but I do like sharing photos.

https://www.instagram.com/p/BTynN1gjTyj/

https://twitter.com/bertwagner/status/903712788538949633

https://twitter.com/bertwagner/status/894373268857212929

https://www.instagram.com/p/BP08U99j3Wh/

https://twitter.com/bertwagner/status/892717494166802432

Top 5 Catch-All

These are some of the random things that helped me get through the year.

Red Bird Coffee. This is premium coffee at affordable prices. The Ethiopian Aricha is the best coffee I've ever had - tastes like red wine and chocolate.
Vulfpeck's The Beautiful Game - I listened to this album more than any other to get into a working groove. So funky.
Pinpoint: How GPS Is Changing Technology, Culture, and Our Minds - This was probably my favorite book of the year. If you ever wonder how GPS works, or the implications of a more connected world, this book was absolutely fascinating.
LED Lighting Strips - I put these behind my computer monitors to create some nice lighting for filming, but I've found myself leaving them on all the time because they add a nice contrasting back light to my screens.
BONUS! Mechanical Keyboard - This thing is inexpensive, but amazing. I didn't realize what I was missing out until I started typing on it. The sound of the clacking keys brought on an immediate flashback to the 1990s when I last had a mechanical keyboard. I don't know if it allows me to type faster like many users claim, but I am definitely happier typing on it. CLACK! CLACK! CLACK!

"How do I contribute to dbatools?" with Drew Furgiuele

The Fastest Way To Locate Errors In Your SQL Query

I Have A SQL Login - Why Can't I Connect?

DBA Skills 101: SQL Logins

I can't connect!

On to troubleshooting

Sleep

Takeaway

One Last Technical Challenge (BONUS)

A Better Way To Script Database Objects

There Is A Better Way

SQL and NOT SQL: Best of 2017

Top 5 Blog Posts

Top 5 Vlogs

Top 5 Posts That Never Got Written

Top 5 Tweets/Instagrams

Top 5 Catch-All