On occasions, I need to work with XML data in SQL Server T-SQL and one problem is that I need to be able to encode and decode strings, using the XML encoding standards. For example, my strings might include % characters or < or > characters, etc.
So we added a pair of tools (XMLEncodeString and XMLDecodeString) our free SDU Tools for developers and DBAs.
You can see them in action in the main image above, and in this video:
2019-02-27
In a previous SDU Tools post, I described how to calculate the columns required for a date dimension. Another related tool that we have in our free SDU Tools for developers and DBAs, is a function that creates the columns needed for a time period dimension. It’s called TimePeriodDimensionColumns.
You supply the starting time, and the number of minutes per time period, and it supplies the output columns.
You can see it in action in the main image above, and in this video:
2019-02-20
Calculating the columns required for a date dimension using T-SQL is a pretty common request from SQL Server developers. So we decided to make it really easy to do. In our free SDU Tools for developers and DBAs, there is a table-valued function called DateDimensionColumns, for just this purpose.
You supply a date, and the month that your financial year starts in, and it supplies the output columns.
You can also easily combine it with our DatesBetween function to get dimension columns for a range of dates:
2019-02-13
To celebrate Chinese New Year this week, I thought I should write about some options that we recently added to our free SDU Tools for developers and DBAs, for working with Chinese calendar concepts.
Let’s start with the basic one: when is Chinese New Year? We added a function called DateOfChineseNewYear. You just supply our year number (Gregorian calendar), and it will tell you when Chinese New Year is.
2019-02-08
In our free SDU Tools for developers and DBAs, we’ve added a lot of scripting tools. To script out Windows authenticated logins (as opposed to SQL logins), we have ScriptWindowsLogins.
You can see how to use it in the main image above. It’s a function, that takes a list of the logins to script. You can pass the value ALL or a comma-delimited list.
One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.
2019-02-06
In our free SDU Tools for developers and DBAs, we’ve added a lot of scripting tools. The tool that I’m describing today is one of the most sophisticated tools in our scripting options. It’s ScriptTable.
It’s very flexible. For example, it can change the name of the table, or the schema that it’s in. It can force ANSI_NULLS and ANSI_PADDING on or off. It can change user-defined data types to their base types, change compression strategies, and more.
2019-01-30
In our free SDU Tools for developers and DBAs, we’ve added a lot of scripting tools. To script out SQL authenticated logins (as opposed to Windows logins), we have ScriptSQLLogins.
You can see how to use it in the main image above. It’s a function, that takes a list of the logins to script. You can pass the value ALL or a comma-delimited list.
Importantly, as well as the login and the hashed password, it also scripts other details:
2019-01-23
In our free SDU Tools for developers and DBAs, there’s another useful scripting tool. It scripts out the logins that are members of server roles. It’s called ScriptServerRoleMembers.
One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.
But if you just want to create a script, that works too.
By default, it lists permissions for all logins, but you can also provide a list of the logins that you’re interested in, as a comma-delimited list, to the @LoginsToScript parameter.
2019-01-16
In our free SDU Tools for developers and DBAs, there’s another useful scripting tool. It scripts out the server permissions that have been granted to logins. It’s called ScriptServerPermissions.
One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.
But if you just want to create a script, that works too.
By default, it lists permissions for all logins, but you can also provide a list of the logins that you’re interested in, as a comma-delimited list, to the @LoginsToScript parameter.
2019-01-09
In our free SDU Tools for developers and DBAs, there’s a tool that makes it easy to create a script for creating SQL Server database users. No surprise, it’s called ScriptDatabaseUsers.
One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.
But if you just want to create a script, that works too.
2019-01-02
