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.

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.

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

http://sqldownunder.com/sdu-tools

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.

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

 

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

http://sqldownunder.com/sdu-tools

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.

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.

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

http://sqldownunder.com/sdu-tools