The Bit Bucket

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.

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.

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 !

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.

You can find out more about our free SDU Tools here:

http://sqldownunder.com/sdu-tools

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.

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.

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.

You can find out more about our free SDU Tools here:

http://sqldownunder.com/sdu-tools

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.

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.

 

 

You can find out more about our free SDU Tools here:

http://sqldownunder.com/sdu-tools

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.

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.

You can find out more about our free SDU Tools here:

http://sqldownunder.com/sdu-tools

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

We hope you continue to use SDU Tools.

You can find out more about our free SDU Tools here:

http://sdutools.sqldownunder.com

 

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 !