Azure-Sql-Db

In our free SDU Tools for developers and DBAs, we have added many tools that help to convert data between different types and formats when working with T-SQL.

In some recent consulting work, I needed to convert data from BizTalk messages into other formats. To help make this easy, I added a function that converts from the Base64 (used by Biztalk messages and other applications) into varbinary.

Not surprisingly, we called it Base64ToVarbinary. And for completeness, we also added the reverse function: VarbinaryToBase64.

2018-07-05

When I need to move small amounts of data between an on-premises SQL Server system and an Azure SQL Database, or run queries that involve both systems, the easiest option at present is to create a linked server to Azure SQL Database.

And the easiest way to do that (or if you can’t remember the commands) is with one of our free SDU Tools for developers and DBAs, called CreateLinkedServerToAzureSQLDatabase. You can read it to check out the code, or use it like this:

2018-06-27

Service Broker is one of my favorite tools in SQL Server. So many applications need to have a transactional queue, and many people try to build them using tables and other objects. But SQL Server has Service Broker and with it, you get the beauty of using the knowledge of someone who already knows about queues having thought about how they need to work.

But while Service Broker is quite forgiving, it’s common while developing Service Broker applications to make mistakes and end up with messages in queues that will never be delivered because you had some bug in your code. Service Broker doesn’t want to ever just throw your data away.

2018-06-20

I often need to convert values from one data type to another, particularly when working with strings. One function that I’m surprised wasn’t already built in to SQL Server was one that converts a character to its hexadecimal representation.

There is an ASCII function. You can see it here:

This converts a character to its ASCII value (see http://asciitable.com )

We’ve added a function to our free SDU Tools collection for developers and DBAs. It’s called CharToHexadecimal and you can see what it does in the main image above.

2018-06-13

While SQL Server includes an amazing array of built in functions and procedures, one that’s always puzzled me is the lack of a simple function to convert a database compatibility level to a SQL Server version ie: how do you know that 110 was SQL Server 2012?

And so, you don’t need to wait any longer.

One of the tools in our free SDU Tools collection for database developers and DBAs is SQLServerVersionForCompatibilityLevel.

2018-06-06

One of the things that I’ve always thought was missing in SQL Server and the T-SQL language was a built-in view that showed reserved words, and even better, future reserved words.

It’s one thing to say that you can create a policy to avoid people using reserved words for table, column, or object names, but how exactly do you know that the name you’ve chosen is one of those?

2018-05-30

When you are trying to debug code in SQL Server T-SQL, or trying to monitor where code is up to, it’s common to add PRINT statements into the code.

These are fine when they are ad-hoc statements, but if you include PRINT statements in stored procedure code, and the procedure runs for any length of time, you’ll know that you normally don’t get to see the PRINT output in the Messages tab until the procedure ends.

2018-05-23

One of the most common tasks that “data people” perform is moving data around, and that can include exporting existing database data, and importing data from other places.

While there are many other standards for how data is stored in files, CSV (comma-separated-value) files are still (by far) the most common. Another common variation are TSV (tab-separated-value) files, where tabs are used to separate values instead of commas. This is usually a good idea as commas occur frequently within the data.

2018-05-18

Sometimes you need to remove all characters from a string, except for a certain type of character. This often happens when processing incoming data from an external source, before you use it within your systems.

In our free DBA and developer SDU Tools, we added some functions to help with this.

AlphabeticOnly limits the output string to just alphabetic characters, based on the English language.

AlphanumericOnly limits the output string to just alphabetic characters (again based on the English language) or the digits from 0 to 9.

2018-05-09

If you aren’t aware of the SQL Server data type called sql_variant, don’t feel bad; you’re not alone. Many people who’ve worked with SQL Server for a very long time haven’t used it.

sql_variant is a very special data type. It’s the data type that you use when you need to store other data but you’re not sure what type you will need to store. Once it is stored though, it has the appropriate data type.

2018-05-02