The PRINT statement in SQL Server’s T-SQL language is useful but one of the biggest restrictions with it is the size of the strings that it can print. Where this becomes a big issue is if you are needing to create dynamic SQL statements (which you obviously need to be careful of in the first place) or scripting database objects, and the statements need to be either executed or printed.
2018-08-08
You can execute SQL Server Agent jobs from within T-SQL code. The procedure sp_start_job can do that.
That’s all well and good but notice that it doesn’t say “execute job”; it just says “start job”. The command starts the execution of a job but has no interest in when it completes.
Sometimes, you need to be able to start a job and wait for it to complete before taking a following action.
2018-08-01
I regularly find myself writing repetitive code in T-SQL. Some things are best done by just creating code snippets but we’ve also added several others to our free SDU Tools for developers and DBAs.
One straightforward one is DropTemporaryTableIfExists.
This just wraps all that’s needed to remove a temporary table if it exists. The nice thing with this procedure is that you can call it before creating a temporary table, and call it again after you finish using the temporary table, as shown in the main image above.
2018-07-25
In our free SDU Tools for developers and DBAs, we have added many tools that help to manipulate dates.
When creating a date dimension (as part of dimensional modeling), you need to be able to get a list of all the dates between a start date and an end date. There are many other reasons why you might need to do this as well.
So we’ve added a table-valued function called DatesBetween to do just this. It takes a start date and an end date as parameters and returns all dates between. As well as the date values, it also numbers each of the dates.
2018-07-18
I spend a lot of time reviewing other people’s databases. Apart from looking at table designs, data types, keys, etc. I’m often interested in what the typical data in the table looks like.
In our free SDU Tools for developers and DBAs, we have added a special tool called AnalyzeTableColumns that makes this type of review easy. You point it at database, schema, and table, and it tells you about the table.
2018-07-11
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 https://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
