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

  • Failed to update the database because the database is read-only

    Had a client today asking about this situation. They were working away on a machine and suddenly they got the message “Failed to update the database because the database is read-only”.

    • The user hadn’t changed anything that they were aware of.
    • Based on the user’s permissions (ie: what they could see), everything in SSMS looked normal.
    • When they checked the sys.databases view, the database showed MULTI_USER.
    • There was enough disk space.
    • Folder permissions had not changed.
    • The user was puzzled.

    The issue was caused by the database being part of an availability group, and the AG had failed over. So suddenly, the user was connected to the replica database, not the primary. This is why the database said it was read-only.

    What the user should have done was to have connected to the AG listener in the first place, not to the server name. Then when failover occurs, the listener would follow the primary server.

    I think this error message is confusing. I’d really prefer that the system tell you that you are now not connected to the primary replica.

  • What’s in a name? How should SQL Server 2017 Graph Edge tables be named?

    Dennes Torres recently wrote a really excellent article introducing Graph Objects in SQL Server 2017. You’ll find it here: https://www.simple-talk.com/sql/t-sql-programming/sql-graph-objects-sql-server-2017-good-bad/

    I’ve also recently published a SQL Down Under podcast with Shreya Verma from the SQL Server team, where we discussed Graph extensions to SQL Server. (It’s part of a joint interview. We also discussed Adaptive Query Plans with Joe Sack). You’ll find that here: http://sqldownunder.com/Podcasts

    I loved Dennes’ article but one aspect that I want to talk a little more about is the naming of Edge tables. As I read the article, I was a little troubled about the edge naming. (Let me stress that it’s a very, very minor item in a very good article and just my opinion).

    And then I got thinking about other aspects of the naming. I think it’s an interesting area because we haven’t had these tables as formal parts of SQL Server before. I’m probably a bit anal about naming but I really think it matters. Here’s the issue:

    Dennes had Node tables for ForumMembers, and ForumPosts. All agreed there. And I like them both being plural. I think that’s appropriate here. Generally I like tables to be plural as sets of data, apart from when the table can only ever contain a single row. I don’t see the Node tables as any exception to this.

    Depending upon the other tables, I might have renamed them as Forum.Members and Forum.Posts (rather than dbo.ForumMembers and dbo.ForumPosts) but that would require knowledge of what other tables there are.

    But then the edges are defined like this:

    Written_By:

    $from_id will be the post

    $to_id will be the member

    Likes:

    $from_id will be who likes

    $to_id will be who/what is liked

    Reply_To:

    $from_id will be the main post

    $to_id will be the reply to the main post

    Implied Direction

    The first aspect to consider is the implied direction of each of these. Based on these definitions, I think it’s useful to write them as sentences to see that the flow works like this:

    ($from_id) the post Written_By the member ($to_id) -- > agreed

    ($from_id) who Likes who/what ($to_id) -- > agreed

    ($from_id) the main post Reply_To the reply to the main post ($to_id) -- > doesn’t work for me

    Note that Reply_To doesn’t work in the direction from $from_id to $to_id. I think that should have been the other way around.

    Positive (Forward) Direction

    I’d also like to see the tables use a forward direction naming rather than reverse (like “Written By”). So perhaps:

    ($from_id) the member Wrote the post ($to_id)

    ($from_id) who Likes who/what ($to_id)

    ($from_id) the reply to the main post RepliesTo the main post ($to_id)

    Tense

    At this point, notice that the tense is now different. Wrote is past tense, Likes is present tense, as is RepliesTo. I started wondering about whether this is ok because the article was written in the past, but I started wondering about Likes. Does the like still apply? We actually don’t know. We know that he/she did like it at the time but we have no current knowledge. They may have changed their mind. So, aligning the tense with our knowledge, perhaps we should have:

    ($from_id) the member Wrote the post ($to_id)

    ($from_id) who likes Liked who/what is liked ($to_id)

    ($from_id) the reply to the main post RepliedTo the main post ($to_id)

    Composite Names

    Finally, I’m ok with composite names like RepliedTo when needed but I was left wondering if there was a simpler, more direct way to say the same thing ie: a single word as a verb. The only one that I can think of that might work is this:

    ($from_id) the member Wrote the post ($to_id)

    ($from_id) who likes Liked who/what is liked ($to_id)

    ($from_id) the reply to the main post Answered the main post ($to_id)

    But I’m not sure about this one, as Answered tends to imply a solution, where RepliedTo doesn’t.

    I’d love to hear your thoughts.

  • SQL Down Under Show 71–Joe Sack and Shreya Verma on Adaptive Query Plans and Graph API

    Hi Folks,

    I had the pleasure recently to record a podcast with an old friend Joe Sack who is working with Adaptive Query Plans in SQL Server 2017.

    Joining us was Shreya Verma (also from the product team), discussing the Graph API (also in SQL Server 2017).

    You’ll find it here: http://www.sqldownunder.com/podcasts

    Enjoy !

  • 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: http://www.sqldownunder.com/Resources/SDUTools

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

    image

    If we call this procedure, it works as expected:

    image

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

    image

    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:

    image

    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:

    image

    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:

    image

    They want an output that looks like this:

    image

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

    Let’s look at the output from the function:

    image

    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:

    image

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

    http://www.sqldownunder.com/resources/sdutools

  • 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: http://www.sqldownunder.com/resources/sdutools

    Let’s look at the output from the function:

    image

    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.

     

    image

     

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

    http://www.sqldownunder.com/resources/sdutools

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

     

    image

     

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

    Let’s look at the output from the function:

    image

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

    image

     

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

    image

    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:

    image

     

    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:

    http://www.sqldownunder.com/resources/sdutools

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

    image

    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: https://youtu.be/V-jCAT-TCXM

    QuoteString - puts quotes around a string and doubles-up any embedded quotes. You can see it here: https://youtu.be/uIj-hTIhIZo

    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: https://youtu.be/P-r1zmX1MpY

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

    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: https://youtu.be/wc8ZS_XPKZs

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

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

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

More Posts Next page »

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

Privacy Statement