THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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

  • 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.

    http://sqlmag.com/sql-server/data-tales-12-case-code-refused-execute-1

    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:

    image

    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:

    image

    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).

    image

    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: http://sqldownunder.azurewebsites.net/Resources/SDUTools

    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: http://sqlmag.com/sql-server/data-tales-11-case-ballooning-tables

    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: http://www.sqldownunder.com/Podcasts

    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):

    image

    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):

    image

     

    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?

    clip_image001

    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:

    image

    Choosing “Scroll Bar Options” then leads to this:

    image

    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:

     

    image

    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: http://www.sqldownunder.com/podcasts

    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 http://sqlserverperformance.wordpress.com/, his SQLskills blog is at http://www.SQLskills.com/blogs/glenn, and he can be reached by email at glenn@SQLskills.com, 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 sqlmag.com.

    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: http://sqlmag.com/sql-server/data-tales-10-it-s-matter-existence

    Enjoy !

  • Power Query Webinar this week

    Hi Folks,

    That Power Query webinar that I’m doing for the global Power BI team is on this week.

    Love to see you attend.

    Registration is here: https://info.microsoft.com/CO-PowerBI-WBNR-FY17-08Aug-25-PowerQuery-PowerBI-Registration.html

  • Database on a diet–final section (part 4)

    Hi Folks,

    I’ve been writing articles for SQL Server Magazine as part of my Data Tales series.

    Over the last couple of months, I’ve been writing a min-series about a database on a diet.

    Earlier parts described the need for size reduction, how ROW and PAGE compression come into play, how to choose between them, etc.

    Part 4 (the final part) is now online here:

    http://sqlmag.com/sql-server/data-tales-9-case-database-diet-part-4

    It covers GZIP compression of large string fields, PDF size reduction in BLOBS, and more.

    Enjoy !

  • Power Query Webinar

    Hi Folks,

    Want to get your head around Power Query in both Excel and Power BI Desktop?

    I've got a webinar happening for the Power BI team later this month: https://info.microsoft.com/CO-PowerBI-WBNR-FY17-08Aug-25-PowerQuery-PowerBI-Registration.html

  • SQL Server index updates vs PostgreSQL index updates

    The link below is to quite a fascinating article that on the surface is about why Uber moved to MySQL from PostgreSQL.

    What it really is about is a severe limitation in how PostgreSQL indexes are updated.

    With SQL Server, if you update a few columns in a row, only the indexes that have that column are updated. In PostgreSQL, all indexes still need to be updated. I understand why they've done that but it's not a good outcome.

    SQL Server shines in this regard.

    http://use-the-index-luke.com/blog/2016-07-29/on-ubers-choice-of-databases

  • Happy Birthday Power BI from all of us

    It’s hard to believe that it’s only a year, given how far Power BI has come.

    Many of us in the community got together under the lead of Paul Turley and Adam Saxton and created a short video for Power BI’s birthday.

    Looks good:

     

    image

    https://youtu.be/An_8wf4RwBw

    So to James Phillips, and all the Power BI team, happy birthday from Down Under, Power BI !

     

    (Good to see some locals in there too Smile)

  • SQL Server 2016 Polybase Services stuck in Change Pending state

    I’ve been using the Polybase services in SQL Server 2016 all throughout the pre-release period without any issue.

    When I tried using them in the GA (Generally Availability) release, I could never get the two Polybase services to start. They were always stuck in a “Change Pending” state. Trying to start them in SQL Server Configuration Manager basically made SSCM look like it was hung.

    Trying to use the Polybase services to create an external data source returned:

    TCP Provider: No connection could be made because the target machine actively refused it.

    clip_image001

    Fellow MVP John Paul Cook came to the rescue today. He previously had an issue with TCP Chimney support and noted that it looked similar.

    True enough, the problem was that TCP wasn’t an enabled protocol on my SQL Server install on my machine. Enabling TCP and restarting made all good.

    Thanks John !

More Posts Next page »

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

Privacy Statement