Welcome to Julian Kuiters Saturday, October 20 2018 @ 07:07 PM AEDT

Answer: How do you create a byte with the value of 00000001 in SQL Server?

SQL Server 2005

My team mate Pi asked me today: how do you create a byte with the value 00000001 in SQL Server?

The answer is to create an integer representation of the byte's value, and cast it to binary.


The output in Management Studio is: 0x01 (Management Studio outputs binary fields in Hex format). Hex 0x01 = Bits 0000001.

Hey Yo! SQLCMD may truncate your XML!

SQL Server 2005

While trying to use SQLCMD to produce an app.config file, I encountered problems with the output being truncated.

The script was similar to:

DECLARE @appconfig XML
SET @appconfig = '<configuration></configuration>'
... Insert configuration nodes
-- Output the config
SELECT @appconfig as ApplicationConfiguration

The default options for SQLCMD produced output like:


(the value was a little longer, but cut off at the end)

Clearly the xml was not valid. There's header text included, corrupted tags, and missing closing tags. So I hunted through books online and google for an answer.

Turns out I needed to use the -y 0 option, which will output xml columns up to 1MB in size.

Also the -h-1 (without any spaces) will remove the header from the output.

If you are planning to use SQLCMD to output XML, remember: Hey Yo! -h-1 -y 0!

sqlcmd -h-1 -y 0 ...

Read more on SQLCMD Utility Options

SQL Server 2005 - Service Pack 2a (SP2a) Update

SQL Server 2005

Downloaded and installed Service Pack 2 for SQL Server 2005? Well here's a reason to download and apply it again.

A few days after releasing Service Pack 2 for SQL Server 2005, Microsoft has re-released it with an additional fix for maintenance plans. If you have already applied SP2, you will need to apply this critical patch. If you are not sure if the updated service pack 2a has been applied to a server, this kb article explains how to check the affected files.

The new Service Pack (SP2a) includes all the fixes for SP2, as well as improvements to the engine and tools.

While you are at it, don't forget that Updated SQL Server 2005 Books Online (help) Available Now (Feb 2007).

SQL Truncation Attacks

SQL Server 2005

SQL injection attacks are nothing new and hopefully everyone is protecting themselves from it.

An interesting by-product of the standard method of protection from the basic sql injection attack (replace single quotes with two single quotes) is increasing the size of the original value. When building a command in a variable, its possible to truncate the original WHERE conditions allowing the attacker to affect much more data than the developer intended.

Bala Neerumalla has written a detailed article for MSDN Magazine with background on the attack methods, some excellent examples, and methods for detecting and preventing SQL Truncation attacks. Definately worth reading and implementing.

Shrinking Databases bit-by-bit to avoid long delays

SQL Server 2005 Michael Jones has a neat script for shrinking databases in small chunks to avoid having the server locked up for a long period.

declare @sql varchar(8000)
declare @name sysname
declare @sizeMB int
declare @UsedMB int
declare @FreeMB int
declare @ShrinkMB int
-- Desired free space in MB after shrink set @FreeMB = 1000
-- Increment to shrink file by in MB set @ShrinkMB = 50
-- Name of Database file to shrink set @name = 'MyDatabaseFileName'
-- Get current file size in MB select @sizeMB = size/128. from sysfiles where name = @name
-- Get current space used in MB select @UsedMB = fileproperty( @name,'SpaceUsed')/128.
select [StartFileSize] = @sizeMB, [StartUsedSpace] = @UsedMB, [File] = @name
-- Loop until file at desired size while  @sizeMB > @UsedMB+@FreeMB+@ShrinkMB   begin
  set @sql =   'dbcc shrinkfile ( ' + @name + ', '+convert(varchar(20),@sizeMB-@ShrinkMB)+' ) '
  print 'Start ' + @sql
  exec ( @sql )
  print 'Done ' + @sql
  -- Get current file size in MB   select @sizeMB = size/128. from sysfiles where name = @name     -- Get current space used in MB   select @UsedMB = fileproperty( @name,'SpaceUsed')/128.
select [EndFileSize] = @sizeMB, [EndUsedSpace] = @UsedMB, [File] = @name

Troubleshooting Delegation in SQL Server

SQL Server 2005

I just came across a good article by Keith Brown on a common trap when setting up SQL Server with kerberos delegation. As he noted, the port number for the Sql Server Service should be specified when setting the service principal name (SPN).

Delegation is a powerful feature of Active Directory but can be difficult to setup. It can be used to allow SQL Server to impersonate the user when connecting to other services: such as BULK LOAD connecting to a network file server. Or for middle tier applications to impersonate users on other servers. A detailed explanation of delegation can be found here.

Microsoft's whitepaper on troubleshooting kerberos delegation has more detail on setting up SQL Server, including linking servers with delegation.

Cannot BACKUP or RESTORE a database snapshot

SQL Server 2005

I've been beta testing SQL Backup 5 and came across an old problem. If you iterate through all the databases on a server and issue a BACKUP DATABASE for Database Snapshot you will receive an error message similar to:

Msg 3002, Level 16, State 1, Line 1
Cannot BACKUP or RESTORE a database snapshot.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

You may also see in your server logs:

Msg 3041, Severity 16, State 1.
BACKUP failed to complete the command BACKUP DATABASE databasename. Check the backup application log for detailed message.

Snapshot databases should not need backing up, as they are read-only representations of another database. Amongst the limitations of Database Snapshots are that you cannot backup or restore a snapshot database. Instead if you need, you can revert the original database back to the Database Snapshot version. Make sure you backup the original database first!

How do you know if a database is a snapshot?

Snapshot databases will reference their original database using the source_database_id column in sys.databases. The following SQL displays which databases are real and which are snapshots:

-- real databases on the server
SELECT name FROM sys.databases WHERE source_database_id IS NULL

-- snapshot databases on the server
SELECT name FROM sys.databases WHERE source_database_id IS NOT NULL

Unsigned Integer Datatype in SQL Server 2005

SQL Server 2005

There's been some interesting questions by users on the SQL Server Engine Tips blog. While almost all of them seem to be off topic, there are some interesting ideas. One of the fun ones was from Dave asking "Can we have some unsigned int data types?".

As a basic background for those of us who spend most of their time in T-SQL; an unsigned integer does not hold negative values. The value range is from 0 to the maximum of the datatype.

So assuming Dave is wanting unsigned integers for this reason, here is how you would create unsigned integer user datatypes in SQL Server 2005:

/* Create the unsigned integer data types */
CREATE TYPE dbo.unsignedTinyint FROM tinyint NULL
CREATE TYPE dbo.unsignedSmallint FROM smallint NULL
CREATE TYPE dbo.unsignedInt FROM int NULL
CREATE TYPE dbo.unsignedBigint FROM bigint NULL

/* unsigned integer (positive values only) rule */
CREATE RULE dbo.unsignedSmallIntRule AS @value >= 0;
CREATE RULE dbo.unsignedIntRule AS @value >= 0;
CREATE RULE dbo.unsignedBigintRule AS @value >= 0;

/* Bind the unsigned integer rules to their datatypes */
EXEC sp_bindrule 'dbo.unsignedSmallIntRule', 'dbo.unsignedSmallint'
EXEC sp_bindrule 'dbo.unsignedIntRule', 'dbo.unsignedInt'
EXEC sp_bindrule 'dbo.unsignedBigintRule', 'dbo.unsignedBigint'

You can download the sample script here which includes an example table with the new unsigned integer data types.

If you attempt to insert an negative value into an unsigned column, you will receive an error message like:

Msg 612, Level 16, State 0, Line 1
A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated.


Msg 220, Level 16, State 2, Line 1
Aritmetic overflow error for data type tinyint, value =-1.