THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

  • More free SQL Server developer and DBA tools: May SDU Tools Release

    Hi Folks,

    We've just created the May 2017 update for SDU Tools. We're pretty excited about what we're delivering this month.

    The first part that we’ve added is a set of tools to make it really, really easy to capture a performance query trace, to load it, and to perform a basic analysis of it. There are three tools involved in this:

    • CapturePerformanceTuningTrace – Just give the trace a name, say where it should be stored, which databases to filter on (if any), and how long you want the trace to run. We do the rest.
    • LoadPerformanceTuningTrace – This tool loads the completed trace into a table.
      AnalyzePerformanceTuningTrace - And then we find the things in the trace that are of interest – automatically

    Next, we’ve added a useful tool to help with your database consistency. I often run into databases where columns that hold the same information are defined differently across the database.

    • ListMismatchedDataTypes - check if columns with the same name have been declared the same way across your database.

    Every month, we also add to our list of standard functions:

    • CalculateAge - Ever looked for code to calculate how old someone or something is?
    • AsciiOnly – Can be used to strip non-ASCII characters from a string. Optionally can also strip control characters.

    We’ve also made it easier to work with SQL Server Agent jobs. sp_start_job is great but it returns as soon as the job starts.

    • ExecuteJobAndWaitForCompletion – Executes a SQL Server Agent job synchronously (ie: wait for it to complete before returning). It has configurable timeouts for starting the job and for waiting for it to complete.

    Our list of scripting functions has also been enhanced:

    • FormatDataTypeName - Takes the name of a data type, the maximum length, precision, and scale and outputs the data type the way it appears in a table definition
    • PGObjectName - Considering migrating to PostgreSQL ? We hope not but if you are needing to work with other systems, this tool for creating PostgreSQL compatible object names from SQL Server ones might help.

    Finally, we've also posted new videos for each new tool and updated the demo script files.

    If you have registered with us, you should have already received an email with a download link. If not, you'll find more info here:

  • Avoiding invalid object name errors with temporary tables for BizTalk, Reporting Services and apps using SET FMTONLY

    When applications need to call stored procedures, they try to work out what the returned data will look like ie: which columns come back, what data types are they, etc.

    The old way of doing this was to call SET FMTONLY ON. Unfortunately, many applications like Biztalk and versions of Reporting Services still did this, and/or still do this. The same issue happens with LINQ to SQL and other tools that use SQL Metal.

    Instead of finding out what’s needed, they fail with an “Invalid object name” error when temporary objects are present. Let’s look at an example. We’ll start with a stored procedure that just grabs some trivial data and returns it, but puts in into a temporary table first:


    If we call this procedure, it works as expected:


    If, however, we try to use SET FMTONLY ON, it fails miserably:


    The problem is that SET FMTONLY causes SQL Server to just return an empty rowset for each statement like a SELECT that it encounters (without executing it), and it has no idea what #SomeComments is until it’s actually executed.

    This is a common problem with configuring Biztalk to work with SQL Server T-SQL stored procedures. There are other ways of manually configuring Biztalk to avoid this but everyone using it just seems to complain that it doesn’t work with stored procedures, or at least not with those that contain temporary tables. One solution is to use table variables instead. SET FMTONLY is happy with those.

    Another option is to try to fool Biztalk (or other application). Consider this procedure instead:


    We declare some code that will only run at run time, and manually manipulate the SET FMTONLY state around the creation of the temp table. Then the outcome is as expected:


    Hopefully that will keep Biztalk and other applications happy.

  • Right-aligning numbers in T-SQL

    When you output a series of numbers in T-SQL, people often want to right-align the numbers. So instead of numbers that look like this:


    They want an output that looks like this:


    Now the first thing to understand is that this is generally a client-tool or program issue, not a T-SQL issue. I ran these queries in SQL Server Management Studio and it could just as easily have decided to show me numbers right-aligned.

    The only way that T-SQL can send you a right-aligned value is to send you a string value instead.

    In the March 2017 update to SDU_Tools, we added a LeftPad function that can do this. SDU Tools is a free library for SQL Server developers and DBAs. You can easily just add it to your database, then call the functions as needed. The functions get added in a separate schema and are easy to remove later if you ever wish to. (We hope you won’t want to). They’re also easy to upgrade. You’ll find info here:

    Let’s look at the output from the function:


    The parameters to the function are the value to be padded (which doesn’t have to be a number), the total number of characters, and the character to use for the padding.

    Similarly, there is a RightPad function that can be used like this:


    We hope you’ll find these functions useful. More details are here:

  • PascalCase and camelCase strings in T-SQL

    Yesterday, I discussed changing the case of T-SQL strings to ProperCase, TitleCase, SnakeCase, and KebabCase.

    But there are other case options that can be needed. For example, often when I’m programmatically generating code, I want to create identifiers for objects. While many people will use SnakeCase for that, in Microsoft-related land (particularly around .NET), it’s common to use both PascalCased and camelCased strings.

    In the April2017 update to SDU_Tools, we added functions to do all of the above. SDU Tools is a free library for SQL Server developers and DBAs. You can easily just add it to your database, then call the functions as needed. The functions get added in a separate schema and are easy to remove later if you ever wish to. (We hope you won’t want to). They’re also easy to upgrade. You’ll find info here:

    Let’s look at the output from the function:


    With PascalCase, whitespace is removed, and all words are capitalized.

    In .NET and some other languages, camelCase is also common. This is the same as PascalCase but the first word isn’t capitalized. It’s often used for local variables within methods, etc.




    We hope you’ll find these functions useful. More details are here:

  • Converting T-SQL strings to Proper Case, Camel Case, Snake Case, or Kebab Case

    Often when I’m moving data around (usually with SSIS but also with just T-SQL), I need to take text that is all capitalized or simply has messed-up capitalization, and convert it to something that looks better (and more human readable) in the target system.

    The most common option for me is conversion to Proper Case. Here’s an example. Let’s start with the following text:




    On a webpage or report, that’s going to look downright ugly. In the March 2017 update to SDU_Tools, we added functions to do all of the above. SDU Tools is a free library for SQL Server developers and DBAs. You can easily just add it to your database, then call the functions as needed. The functions get added in a separate schema and are easy to remove later if you ever wish to. (We hope you won’t want to). They’re also easy to upgrade. You’ll find info here:

    Let’s look at the output from the function:


    The function has been built to allow for common capitalization rules in names and addresses. Here’s another example:



    While Proper Case is useful, it’s not always what’s needed. Let’s look at the difference with a book title:


    The key difference with Title Case is that minor words like “for”, “and”, “to”, etc. aren’t capitalized.

    While we were adding these functions, we also decided to add a few other common ones. Snake case and Kebab Case look like this:



    Note that in Snake Case, the words are all lower-cased and any whitespace between the words is replaced by a single underscore.

    In Kebab Case, every word is capitalized and dashes (hyphens) are inserted between the words.

    We hope you’ll find these functions useful. More details are here:

  • March 2017 update for SDU Tools–More free tools for SQL Server developers and DBAs

    Hi Folks,

    As promised, tonight we’re releasing the monthly update to SDU Tools, our free toolset for SQL Server developers and DBAs. A download link will be sent to all registered subscribers.


    First, the new tools:

    AnalyzeTableColumns - allows for quick profiling of a table. Shows the column metadata, and shows a sample of distinct values from each column. We suspect this will be the favorite this month. You can see it here:

    QuoteString - puts quotes around a string and doubles-up any embedded quotes. You can see it here:

    LeftPad and RightPad - pads a string to the left (or right) with a specified filler character. Useful for formatting strings or right-aligning numeric values. You can see them here:

    SeparateByCase - takes a pascal-cased or camel-cased string and separates out the words by inserting a space. You can see it here:

    StartOfFinancialYear and EndOfFinancialYear - calculates the start (or end) of the financial year for any given date, and allows you to specify the month when the financial year starts. You can see them here:

    PrintMessage - annoyed at waiting for PRINT messages to come back in your stored procedures? Wait no more. You can see it here:

    And the new options:

    We’ve added two other scripts to the download zip.

    • A script that can be used to completely remove the tools
    • A script that contains the sample code that we’ve used in the videos for this month

    And the fixes:

    We've also corrected a couple of issues that we had with collations (don't you just love collations). And while we’re going to be super-careful about backward compatibility, we did rename one option. FindSubsetIndexes is now ListSubsetIndexes so it matches the other commands in that group. (The developer responsible has been dealt with Smile)

    We hope you continue to use SDU Tools. More information is here:

  • Data Tales 12: The Case of the Code that Refused to Execute

    Hi folks,

    I’ve written another Data Tales article for SQL Server Magazine. This time, it’s SSIS (Integration Services) related.

    Enjoy !

  • SSMS: Removing all tabs in your query window with spaces

    This is just a short post that I’ve been meaning to write for a while.

    I know that there is an eternal tabs vs spaces debate that goes on in development teams. Currently I’m in the spaces team for SQL queries.

    So, it’s a pain in the neck when I receive a script file from someone and it’s full of tabs. Even worse when there are a bunch of tabs at weird tab positions. And I start to edit it, and things jump around, and I think @#$@$!@#$@#$ tabs !

    But nowadays, SSMS can help yet very few people seem to realize it.

    If you hit Control-H to bring up the Quick Replace dialog (or do it manually when Control-H doesn’t work –> still investigating that), you can set an option to use Regular Expressions:


    Click to turn that option on, it will have a background color (default is beige-ish). Enter \t for the from text, and 4 spaces (or whatever your favorite number is) for the “to” text and click the replace all option:


    And you get to say “farewell you pesky tabs”.

    I also often use this with \r etc. to replace multiple double-lines, etc. etc. (One day we might even get a macro recorder but this helps for many situations)

    Hope that helps someone.

  • Introducing SDU Tools: Free T-SQL Tools for DBAs and Developers

    I’ve worked with T-SQL for a very long time, and over the years I’ve lost count of how many tools I’ve found the need to create to help me in my work.

    They have been in a variety of script folders, etc. and whenever I go to use them now, I often have to decide which is the best version of a particular tool, as they’ve also been refined over time.

    So I decided to get them into a clean clear shape and SDU Tools was born. I’ve grabbed a whole bunch of tools for a start, made sure they are pretty consistent in the way they do things, and published them within a single script. I figured I might as well also share them with others. They are free (I get you to register so I know what usage they are getting).


    For each of the tools, I’ve also created a YouTube video that shows how to use the tool. I’m also planning to create blog posts for each tool so I have the opportunity to show things that won’t fit in a short video and ways of using them that might be less obvious.

    I’ve got a big backlog of tools that I’d like to add so I’m intending to add in whichever ones I get reworked during each month. Likely areas in the upcoming months are further functions and procedures related to scripting objects, and also to code generation.

    The tools ship as a single T-SQL script, don’t require SQLCLR or anything to clever, and are targeted at SQL Server 2008 and above. They are shipped as a single schema that you can place in whichever database (or databases) suits you best.

    Of course there’s the usual rider that you use them at your own risk. If they don’t do what they should, I’ll be sad and then fix it, but that’s all Smile

    I hope you find them useful. You’ll find more info here:

    Enjoy !

  • Data Tales #11: The Case of the Ballooning Table

    Hi folks,

    I’ve written another “Data Tales” case for SQL Server Magazine.

    It’s posted now and you’ll find it here:

    Enjoy !

  • SQL Down Under show 70–Aaron Bertrand–SQL Server 2016 SP1, SQL Server on Linux, SentryOne Plan Explorer

    Hi Folks,

    I got to record another podcast last week and it’s published now:

    The guest this time was Data Platform MVP Aaron Bertrand. We initially planned to mostly just discuss SentryOne Plan Explorer because all the Pro features were now available in the free edition, which is an awesome situation. But the release of SQL Server 2016 SP1 came the week before, and as both Aaron and I were deeply invested in the changes that occurred there, we spent time discussing it, and also SQL Server on Linux because the public preview for that was also announced.

    Enjoy !

  • SQL Server Management Studio and Usability

    SQL Server Management Studio has moved into the Visual Studio 2015 shell. In general, that’s a really good thing. There are many built-in benefits that come from using that shell. I’ve been showing people many of these. One that surprises many people is the ability to change the font in Object Explorer. No longer do you need to squint at the tiny writing in Object Explorer. You too can change the text in Object Explorer to a readable size (particularly on high-resolution monitors):


    In Tools/Options, you can set the font for “Environment” and it now applies to that text (as well as a number of other places):



    Unfortunately, there are one or two things that are a little harder at first for people who want to use SSMS to write T-SQL. One that was driving me crazy was the scroll bar. Visual Studio tries to give so much information on that bar, about what’s changed, where the insertion carat is, etc. The problem with this is that I often now can’t even find the handle when I want to scroll the window. For example, how do you grab the handle with your mouse and slide the window when it looks like this?


    I was starting to get resigned to this when I asked in the MVP email list. Erik Jenson pointed out that the scroll bar itself had properties. I should have thought of that. If you right-click the scroll bar, you get these options:


    Choosing “Scroll Bar Options” then leads to this:


    The ones that I’ve highlighted are the real offenders. However, note the warning at the bottom. You really don’t want to remove these for all languages. Some might be helpful to you if you use other languages. So instead, click on the option further down the list, to set them for T-SQL only:



    I hope that helps you make SQL Server Management Studio a bit more useful.

  • SQL Down Under Show 69: with guest Data Platform MVP Glenn Berry

    Hi Folks,

    The next SQL Down Under show is now online. In it, Glenn Berry discusses hardware and hardware-related performance issues for SQL Server.

    You’ll find the show here:

    Enjoy !


    More on Glenn:

    Glenn Berry is a Principal Consultant with SQLskills. He has worked as a SQL Server professional for many years in a variety of roles, most recently as Database Architect for Avalara in Parker, CO.

    Glenn has been a SQL Server MVP since 2007, and has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS, which proves he likes to take tests. His expertise includes DMVs, high availability, hardware selection and configuration, and performance tuning. He is also an Adjunct Faculty member at University College – University of Denver, where has been teaching since 2000. He has completed the Master Teacher Program at University College.

    Glenn is heavily involved in the SQL Server community, and is a frequent speaker at user groups, SQL Saturdays, and the PASS Community Summit. He is the author of the book SQL Server Hardware, and he wrote chapters for SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2 books.

    Glenn’s personal blog is at, his SQLskills blog is at, and he can be reached by email at, and on Twitter as GlennAlanBerry.

  • FIX: The unattended execution account cannot be set at this time

    I ran into this one today and it’s the in the category of things that I’m likely to run into again one day so it’s worth recording it here.

    I was trying to configure an unattended execution account for SQL Server Reporting Services 2016 and was getting the above error.

    The problem was that even though the machine is just in a workgroup, SQL Server Reporting Services Configuration Manager needs a domain or machine specified for the user name.

    You can’t just set a name like SSRSUnattendedExecution, it does have to be MYMACHINENAME\SSRSUnattendedExecution. That’s different to the other SQL Server services that happily let you configure it that way when setting them up.

    The message is misleading as it suggests that you can’t do it right now. You can’t do it ever like that.

  • Data Tales #10: A Matter of Existence

    Hi Folks,

    I’ve been continuing with my Data Tales series up at

    This time I’ve written about a pet hate: seeing code that counts rows when all it needs to do is to check for existence. However, all is not as bad as it seems because the optimizer does a pretty good job of protecting us from ourselves. (At least the SQL Server optimizer does Smile)

    It’s here:

    Enjoy !

More Posts Next page »

This Blog



No tags have been created or used yet.


Privacy Statement