Moved over to foxtricks.posterous.com

I move over the blog to http://foxtricks.posterous.com Most posts from this blog can be found over there. You will be redirected in a few seconds.

Going beyond sp_helpindex

Every SQL Server developer and DBA has to use the sp_helpindex from time to time to get some information on the indexes on a table. I have 2 major problems with this stored procedure:

• All information is concatenated in 1 string
• Not much info on partitioned indexes

If I run the following statement:

exec sp_helpindex 'Customer'

The following information will be returned:

index_name	index_description	index_keys
pk_entity	clustered, unique, primary key located on PRIMARY	entity

CREATE FUNCTION dbo.fn_helpindex(@table_name sysname)
RETURNS @result table(table_name            sysname, 
                      index_name            sysname, 
                      is_unique             bit, 
                      ignore_dup_key        bit, 
                      is_primary_key        bit, 
                      is_unique_constraint  bit, 
                      fill_factor           tinyint, 
                      is_padded             bit, 
                      is_disabled           bit, 
                      is_hypothetical       bit, 
                      allow_row_locks       bit, 
                      allow_page_locks      bit, 
                      data_space_type       nvarchar(2),
                      data_space            nvarchar(1024))
AS
  BEGIN
    
    WITH partitionfields ([object_id], [index_id], [name]) AS 
    (SELECT ic.[object_id] ,ic.index_id, c.name 
       FROM sys.index_columns ic
       JOIN sys.columns c 
         ON  ic.[object_id] = c.[object_id] 
        AND ic.column_id = c.column_id 
      WHERE ic.partition_ordinal = 1)
    INSERT INTO @result
    SELECT table_name = object_name(i.[object_id])
         , i.name

         , i.is_unique
         , i.ignore_dup_key
         , i.is_primary_key
         , i.is_unique_constraint
         , i.fill_factor
         , i.is_padded
         , i.is_disabled
         , i.is_hypothetical
         , i.allow_row_locks
         , i.allow_page_locks
         , data_space_type = ds.type
         , data_space = coalesce(ds.name + N'(' + pf.name + N')', ds.name)
    FROM sys.indexes i 
      INNER JOIN sys.data_spaces ds
      ON  i.data_space_id = ds.data_space_id
      LEFT JOIN partitionfields pf
      ON i.[object_id] = pf.[object_id] AND
         i.index_id = pf.index_id
    WHERE i.name IS NOT NULL 
      AND object_name(i.[object_id]) = @table_name

    -- Return the result of the function
    RETURN 
  END

If I run:

select * from dbo.fn_helpindex('Customer')

The following information will be returned:

table_name	index_name	is_clustered	is_unique	ignore_dup_key	is_primary_key	is_unique_constraint	fill_factor	is_padded	is_disabled	is_hypothetical	allow_row_locks	allow_page_locks	data_space_type	data_space
Customer	pk_entity	1	1	0	1	0	0	0	0	0	1	1	FG	PRIMARY

If we use the function on a table with a partitioned index (like the script below) the function will return more information about the data space as well.

CREATE PARTITION FUNCTION pfn_maturity_bucket (int)
AS RANGE RIGHT FOR VALUES (3, 6, 12);

CREATE PARTITION SCHEME psc_maturity_bucket
AS PARTITION pfn_maturity_bucket
ALL TO ([PRIMARY])

CREATE TABLE LoanDetails(code nvarchar(50), amount numeric(23,3), maturity_bucket int)
CREATE INDEX ixLoanDetails 
          ON LoanDetails(maturity_bucket) 
          ON psc_maturity_bucket(maturity_bucket)

So, let's run the following statement on the partitioned table:

select * from dbo.fn_helpindex('LoanDetails')

The output will be:

table_name	index_name	is_clustered	is_unique	ignore_dup_key	is_primary_key	is_unique_constraint	fill_factor	is_padded	is_disabled	is_hypothetical	allow_row_locks	allow_page_locks	data_space_type	data_space
LoanDetails	ixLoanDetails	0	0	0	0	0	0	0	0	0	1	1	PS	psc_maturity_bucket(maturity_bucket)

How to determine the default database path in all SQL Server Versions

When you create a database in SQL Server using the "CREATE DATABASE" statement without any options, the database files will be created in some "default" locations. Many people think this default location is the same as the location of the master database, but this is incorrect, it is possible to alter the location in server properties after installation of the server. (in Management Studio right click on the server and select "Server Properties", then go to the "Database Settings" tab and near the bottom you will see a section with "Database Default locations") For example, suppose I execute the following statement:

CREATE DATABASE DefaultLocationDB

On my machine this means 2 files will be created:

• C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DefaultLocationDB.mdf
• C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DefaultLocationDB.ldf

This is nice, you don't have to remember a path to pass, but when you want to restore a database from another server (where a different path is used) it can be a pain. To restore a database with a specific location for the files we use the following syntax:

RESTORE DATABASE DefaultLocationDB 
FROM  DISK = N'c:\backups\DemoDB.bak' WITH  FILE = 1,  
MOVE N'demo_data_device' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoDb.ldf',  
MOVE N'demo_log_device' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoDb.ldf',  
NOUNLOAD, REPLACE

To make it easy to restore a database using a script I designed a function to figure out the location for the default Data path (and another one for the default log path). The locations of these paths I got from http://msdn.microsoft.com/en-us/library/ms143547.aspx The output can be stored in a 260 character variable (260 as specified MAX_PATH in http://msdn.microsoft.com/en-us/library/aa365247.aspx).

Edit: Rudi Larno posted a very nice refactored version of the code I posted. (especially note the nice recursion in it.)

View my old code

IF object_id('dbo.fn_get_default_data_path') is not null
    DROP FUNCTION dbo.fn_get_default_data_path
GO

CREATE FUNCTION dbo.fn_get_default_data_path()
returns nvarchar(260)
AS
BEGIN
    DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @data_path nvarchar(260), @log_path nvarchar(260);
 
    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER')
    
    -- sql 2005/2008 with instance
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';
 
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;

    IF @data_path is null -- sql 2005/2008 default instance
      BEGIN
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'SQLDataRoot', @data_path output;
        SET @data_path = @data_path + '\Data';
      END
 
    if @data_path is null -- sql 2000 with instance
      begin
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;
      end
      
    if @data_path is null -- sql 2000 default instance
      begin
        SET @registry_key = N'Software\Microsoft\MSSQLServer\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;
      end
      
      return @data_path
END
GO

As you can see it gets very ugly very fast. To extract the log path use this function:

IF object_id('dbo.fn_get_default_log_path') is not null
    DROP FUNCTION dbo.fn_get_default_log_path
GO

CREATE FUNCTION dbo.fn_get_default_log_path()
RETURNS nvarchar(260)
AS
BEGIN
    DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @log_path nvarchar(260);
 
    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER');
    
    -- sql 2005/2008 instance
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';
     
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultLog', @log_path output;
 
    if @log_path is null -- sql 2000 with instance
      begin
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultLog', @log_path output;
      end
      
    if @log_path is null -- sql 2000 default instance
      begin
        SET @registry_key = N'Software\Microsoft\MSSQLServer\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultLog', @log_path output;
      end    
    
    IF @log_path is null
      BEGIN
        SELECT @log_path = dbo.fn_get_default_data_path()
      END
      
      return @log_path
END
GO

IF object_id('dbo.fn_get_default_path') is not null
    DROP FUNCTION dbo.fn_get_default_path
GO

CREATE FUNCTION dbo.fn_get_default_path(@log bit)
RETURNS nvarchar(260)
AS
BEGIN
    DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @path nvarchar(260), @value_name nvarchar(20);

    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER');

    -- sql 2005/2008 with instance
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';

    SET @value_name = N'DefaultData'
    IF @log = 1
      BEGIN
        SET @value_name = N'DefaultLog';
      END

    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;

    IF @log = 0 AND @path is null -- sql 2005/2008 default instance
      BEGIN
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'SQLDataRoot', @path output;
        SET @path = @path + '\Data';
      END

    IF @path IS NULL -- sql 2000 with instance
      BEGIN
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;
      END

    IF @path IS NULL -- sql 2000 default instance
      BEGIN
        SET @registry_key = N'Software\Microsoft\MSSQLServer\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;
      END

    IF @log = 1 AND @path is null -- fetch the default data path instead.
      BEGIN
        SELECT @path = dbo.fn_get_default_path(0)
      END

    RETURN @path;
END
GO

The functions can then be used as follows (or you can just copy paste the output):

declare @sql nvarchar(2000), 
        @data varchar(260), 
        @log varchar(260); 

select @data = dbo.fn_get_default_data_path(),
       @log = dbo.fn_get_default_log_path()

select @sql= 'RESTORE DATABASE DefaultLocationDB 
FROM  DISK = N''c:\backups\DemoDB.bak'' WITH  FILE = 1,  
MOVE N''demo_data_device'' TO N''' + @data + '\DemoDb.ldf'',  
MOVE N''demo_log_device'' TO N''' +  @log + '\DemoDb.ldf'',  
NOUNLOAD, REPLACE'

exec (@sql)

edit: I updated the script for dbo.fn_get_default_log_path() to avoid calling dbo.fn_get_default_data_path() when not needed.

How to create SQL Server temp tables without collation problems

This message has moved to http://foxtricks.posterous.com

How to strip trailing zeros

A client came to me with another string formatting problem. I would never recommend formatting strings in T-SQL, but rather to do formatting on the client side. The question was to strip trailing zeros from a numeric value stored in a string but retain the decimals when they are not zero.

1.0501	needs to show	1.0501
1.0500	needs to show	1.05
1.0000	needs to show	1

This is easy to do by replacing all zeroes with a space and then using RTRIM() to remove the right spaces. Finally replace the spaces again with zeroes and you have the correct result. Except when all decimals were zero, in that case the decimal point needs to be suppressed as well. I poured the code in a function that is easier to use.

CREATE FUNCTION dbo.fn_strip_zeros(@number numeric(38,10))
RETURNS varchar(38)
WITH ENCRYPTION
AS
  BEGIN
    DECLARE @result varchar(38)
    DECLARE @decimal varchar(3)

    SET @decimal = substring(convert(varchar(38), convert(numeric(38,10),1)/5 ), 2,1)

    set @result = rtrim(replace(replace(rtrim(replace(@number,'0',' ')),' ','0') + ' ', @decimal + ' ', ''))

    RETURN @result
  END

GO

How to make a thread safe insert/update procedure on SQL Server

When developing an application that does a lot of inserts and updates, it is sometimes easier to wrap both statements in one stored procedure. This results in a simple procedure that will check if a certain record exists. When no record is found a new one is inserted, when a record is found the values are updated.

We will use the following table script as example to explain the problem:

create table updatetest 
  (
   id     int primary key, 
   name   varchar(20), 
   amount numeric
  )


insert into updatetest values (1, 'first', 100)
insert into updatetest values (2, 'second', 500)
insert into updatetest values (3, 'third', 30)
insert into updatetest values (4, 'fourth', 200)

When making an insert/update procedure most people write something like this:

CREATE PROCEDURE updatevalues(@id     int, 
                              @name   varchar(20), 
                              @amount numeric)
AS

IF EXISTS(SELECT * FROM updatetest WHERE id = @id)
  BEGIN
    UPDATE updatetest 
    SET name = @name, 
        amount= @amount
    WHERE id = @id
  END
ELSE
  BEGIN
    INSERT INTO updatetest VALUES (@id, @name, @amount)
  END

This looks like the perfect solution to our problem except for one detail. When this procedure is executed from several threads at the same time to update the same record, it will crash.

Let's alter our procedure script a bit to make it easier to reproduce the problem.

CREATE PROCEDURE updatevalues(@id     int, 
                              @name   varchar(20), 
                              @amount numeric)
AS

IF EXISTS(SELECT * FROM updatetest WHERE id = @id)
  BEGIN
    waitfor delay '00:00:10'
    UPDATE updatetest 
    SET name = @name, 
        amount= @amount
    WHERE id = @id
  END
ELSE
  BEGIN
    waitfor delay '00:00:10'
    INSERT INTO updatetest VALUES (@id, @name, @amount)
  END

Compile this script and then run the following statements (at the same time) in 2 query windows.

exec updatevalues 7, 'Seven', 777

Surprise surprise, look at the error we get in the second query window...

"Msg 2627, Level 14, State 1, Procedure updatevalues, Line 17
Violation of PRIMARY KEY constraint 'PK__updatetest__29AC2CE0'. Cannot insert duplicate key in object 'updatetest'.
The statement has been terminated.

(0 row(s) affected)

Why does this happen? Simple, the first execution checks if the record exists and decides to insert a new record because the if exists returns false. The second thread is executing the same query at the same time and of course the existance check returns false as well. They both try to insert and a primary key error happens.

Some people think that this will never happen, but they are wrong, from the moment you are working with large amounts of data and users, this conflict will happen very often.

Luckily there is an easy way to solve this (very annoying) problem by using Lock hints in our advantage.

We will update our test procedure to include a transaction and an Exclusive Lock:

CREATE PROCEDURE updatevalues(@id     int, 
                              @name   varchar(20), 
                              @amount numeric)
AS

BEGIN TRANSACTION
IF EXISTS(SELECT * FROM updatetest (XLOCK, SERIALIZABLE) WHERE id = @id)
  BEGIN
    waitfor delay '00:00:10'
    UPDATE updatetest 
    SET name = @name, 
        amount= @amount
    WHERE id = @id
  END
ELSE
  BEGIN
    waitfor delay '00:00:10'
    INSERT INTO updatetest VALUES (@id, @name, @amount)
  END
COMMIT TRANSACTION
RETURN(0)

The updated code has as effect that the second thread will wait until the first thread has completed it's operation. It is no longer possible that another operation on the same record is "sneaking" in between the check and the insert or update.

Since I simplified the procedure script for the example you can download a complete version of the code.

Building a small Virtual PC with windows xp and SQL Server 2005 Developer edition

I love Virtual PC 2007, but I hate how every VPC image I used so far is at least 5GB and easily grows to 10GB. It is hard to keep multiple images active on your machine. So I started looking for a way to reduce the size of an image.

About the only usefull resource I found was the site of http://www.bold-fortune.com. This website contains the log of a guy that deletes windows files one by one and tests his system for stability.

My goal was to end up with the smallest possible Windows XP installation with the SQL Server 2005 service installed (and running).

Here is how I got there:

• Created a Virtual PC with Virtual PC 2007 with 128Mb memory enabled.
• Installed Windows XP SP2 and switched off as many of the optional stuff as I could.
• Set the swap file size to a fixed 128MB
• Installed virtual pc additions.
• Disabled the Windows Security center
• Disabled windows file protection (following the method described on the bold-fortune site)
• Installed the SQL Server service with the following components: Database Server, Shared components, Replication support.
• In The surface configuration wizard enable remote connections over tcp/ip.
• Disable all protocols except tcp/ip in the server configuration manager.
• Followed the deletion steps as described by bold-fortune except "WMI" and "Logical Disk manager". (also left the file drvmain.sdb)
  Between every delete I recycled SQL Server to see if I could still connect remotely and run queries afterwards.

When a in my view important service or component was deleted I restarted the vpc to see if everything was still in working order. (I check in event viewer for errors.)

Another small fix I did was running the following in a cmd.exe window:

net config server /autodisconnect:-1

This fixes most of the "MRxSmb the redirector failed to determine the connection type on the loopback connection"

When all this was done I switched on compression on the complete c-drive of the vpc (make sure sql server is running because it does not when the system databases are compressed). Then used the Whitney Defrag utility to defragment the drive. This defragmenter is extremely nice on virtual pc's because it has a tiny installation footprint and it moves all the clusters to the start of the disk.

When all is ready it is time to start compacting the Virtual PC. Start this process by capturing the ISO file "C:\Program Files\Microsoft Virtual PC\Virtual Machine Additions\Virtual Disk Precompactor.iso". This will automatically start zeroing out all the free space of the virtual hard disk.

Once the virtual hard disk is pre-compacted, shut down your Virtual Machine and compact the virtual hard disk by using the "Virtual Disk Wizard".

This (lenghty) process delivered me a Virtual PC of 826 MB and with compression on a footprint of 550 MB on disk which enables me to keep several virtual machines around. I do realise that a vpc with so little memory is slow and not usable for any serious testing, but it enables me to comfortably experiment with clustering and replication.

You can download the batch files i created to shrink my vpc images but be carefull. If you run these files on your normal pc, they WILL delete a LOT of files and certain software will NOT WORK anymore. So please be carefull. And I do not guarantee that these batch files will work perfectly for you. For me they do exactly what I need and leave me with a good usable VPC image for testing.

So If you want to try the same thing I did, you can download the batch files and start deleting windows.

What to do when you want to drop a FileGroup

I was working on a system to partition tables on SQL Server 2005 which means I was creating database filegroups and database files and I needed to drop my newly created objects regularly to retest my code. But sometimes it was not possible to drop the filegroups with complaints that the filegroup was not empty. After a lot of googling I found that it is possible that statistics are defined on the filegroup (most likely automatic stats). To see this you can run the following query.

select object_name(id) AS TableName, * 
from dbo.sysindexes 
where groupid = object_id('<yourfilegroup>')

After dropping the statistics I could drop the filegroup without problems.

A small update to the sp_help_partition procedure.

One of the flaws in the sp_help_partition function I posted earlier is the lack of information if the partition function is a Range RIGHT or a Range LEFT function. To make that clearer I added a check on the boundary_value_on_right field from the system tables. Also added an order by to make the presentation nicer.

SELECT
          object_name(i.[object_id]) as [Table],
          ps.name                    as PartitionScheme,
          pf.name                    as PartitionFunction,
          dds.destination_id         as PartitionNumber,
          fg.Name                    as FileGroupName,
          case when boundary_value_on_right = 1 then 'RIGHT' else 'LEFT' end [Range], 
          prv.value                  as RangeValue
    FROM sys.indexes i
      INNER JOIN sys.partition_schemes ps on ps.data_space_id = i.data_space_id
      INNER JOIN sys.partition_functions pf on ps.function_id = pf.function_id
      INNER JOIN sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id
      INNER JOIN sys.filegroups fg on fg.data_space_id = dds.data_space_id
      LEFT  JOIN sys.partition_range_values prv on prv.boundary_id = dds.destination_id
    WHERE i.type = 1 

I hope this version works better for you. Download the complete script.

Zero Padding a numeric value in Transact SQL

A quick one before I go home (and just because I used it 10 minutes ago)... I zero pad a numeric value like this:

DECLARE @mynumber INT,
        @padding  INT
SELECT @mynumber = 123, 
       @padding = 8
SELECT REPLACE(STR(@mynumber, @padding), ' ', '0')

The result of this example will be

00000123

I admit, it is not very elegant, but is string manipulation ever elegant in TSQL...

High Performance writing to XML Log files.

A characteristic of logfiles is the "append" nature. We append new content at the back of the file. When writing to a normal text file this is very easy and fast.

2007-07-01 13:56:04.313 start process
2007-07-01 13:56:25.837 do something
2007-07-01 13:56:25.853 the end

But when we want to use XML files to write log information things become a little bit more complicated because the xml needs to be valid, and we need a "closing" tag for the file.

<?xml version="1.0" encoding="UTF-8"?>
<log>
<logline date="2007-07-01 13:56:04.313" text="start process" />
<logline date="2007-07-01 13:56:25.837" text="do something" />
<logline date="2007-07-01 13:56:25.853" text="the end" />
</log>

This structure means we can no longer just "append" lines to the file. But we need to "seek" the closing tag and write before that. This is an extremely slow process especially when the logfiles become big. (I have seen 1Gb+ logfiles before.) Or even worse, we need to open the logfile as a DOM document. (RAM explosion anyone...) To solve this problem I use a little xml trick.

NOTE: This code will not be parsed correctly by every XML parser on the planet. But all the parsers I have used do it correctly.

The little logfile you saw in the previous example can be rewritten to be stored in 2 separate files. One with the opening and closing tags, and one with the log lines. Then the loglines get included in the "header" file which keeps it valid xml. And we can append lines in the "loglines" file. First the "header" file which contains an include section for the "loglines.xml" file.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log [
&lt;!ENTITY loglines SYSTEM "loglines.xml">
]>
<log>
&loglines;
</log>

Then the loglines.xml file itself. (Note that this file does not validate.)

<logline date="2007-07-01 13:56:04.313" text="start process" />
<logline date="2007-07-01 13:56:25.837" text="do something" />
<logline date="2007-07-01 13:56:25.853" text="the end" />

Now when you parse the header.xml file it returns the complete log file as valid xml and you can append new log lines to the loglines.xml file without having to parse the complete file. As a little bonus... To format the log file as a nice html page you can apply the following xsl stylesheet.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
  <html>
  <body>
    <h2>Log Lines</h2>
    <table border="1">
    <tr bgcolor="#9acd32">
      <th align="left">Date</th>
      <th align="left">Message</th>
    </tr>
    <xsl:apply-templates select="log/logline" >
    <xsl:sort select="@date"/>
    </xsl:apply-templates>
    </table>
  </body>
  </html>
  </xsl:template>
  
  <xsl:template match="logline">
    <tr>
      <td><xsl:value-of select="@date"/></td>
      <td><xsl:value-of select="@text"/></td>
    </tr>
  </xsl:template>
  
</xsl:stylesheet>

You can download the files used in the examples by right clicking on the links and selecting "Save As...". logfile.xml, loglines.xml and logfile.xsl.

Recognising the SQL Server Version

Something I often need to do is recognise which SQL Server version I am working on. For example to separate SQL 2000 compatible code from SQL Server 2005 compatible code. I'll show you some of the options to get the version number... Starting from the ugliest to the most elegant... The first option is calling the extended procedure xp_msver

exec xp_msver 'ProductVersion'

Which give you this result:

Index	Name	Internal_Value	Character_Value
2	ProductVersion	524288	8.00.2039

xp_msver can give you quite a large amount of version information about your system, but it is not very pretty to use in your code. There is also no option to get a simple version number. Another option is to call @@version

select @@version

Which gives you something that looks like:

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

This is of course not very practical when you are programming. So another option is fetching the version number from the server properties.

select serverproperty('productversion')

And the result looks a bit like this:

9.00.3054.00

This is already more practical. But still difficult to use in your programs. My favorite option is using the @@microsoftversion function. This function returns a

select @@microsoftversion / 0x01000000 

And the result of this query is a very simple:

9

The following query shows you the possible values for the recent SQL Server versions (I don't have a SQL 6 - 7 test environment anymore, so I don't know if it works on those.)

SELECT CASE @@microsoftversion/ 0x01000000 
         WHEN 8 THEN 'sql 2000'
         WHEN 9 THEN 'sql 2005'
         WHEN 10 THEN 'sql 2008'
       END

Although this was a simple topic, I hope you found it interesting.

Using SET IDENTITY_INSERT as a normal database user in SQL Server 2005.

In earlier versions of Microsoft SQL Server it was impossible to grant "SET IDENTITY_INSERT" rights to a normal user. To have permission to do an identity_insert (even as part of a stored procedure) you needed to grant the user ddl admin rights which is quite a heavy permission to grant to a normal user.

Luckily this problem can be solved in SQL Server 2005 by using Certificates and code signing.

Let's set up a demo environment.

create database identity_insert_test
use identity_insert_test
exec sp_addlogin 'SimpleLogin', 'PasswordForSimpleLogin'
GO
exec sp_grantdbaccess 'SimpleLogin', 'SimpleLogin'
GO

if exists(select * from sys.objects where object_id = object_id('dbo.a')) 
   drop table dbo.a
create table dbo.a (keyfield int identity, somevalue varchar(10))
GO

if exists(select * from sys.objects where object_id = object_id('p_a')) 
  drop procedure p_a
go
create procedure p_a
as
set identity_insert dbo.a on
insert into dbo.a (keyfield, somevalue) values (2,'nice')
set identity_insert dbo.a off

return (0)
go

grant exec on p_a to SimpleLogin
go

When we run the stored procedure with the simple "no rights" login we quickly see the following error

Msg 1088, Level 16, State 11, Procedure p_a, Line 3
Cannot find the object "dbo.a" because it does not exist or you do not have permissions.

(Make sure you run the "revert;" part of the statement to revert to your login after the error.)

Execute as user = 'SimpleLogin'
exec p_a
revert;
GO

To fix this annoying problem we need to create a few more objects in the database. First of all we need a master key which will be used to encrypt the certificate. Then we create a certificate, a login based on the certificate and we grant the "ALTER ANY SCHEMA" permission to the login. And finally we will sign the stored procedure with the schema login.

CREATE MASTER KEY ENCRYPTION BY password = '**somethingspecial**';
CREATE CERTIFICATE SchemaCert WITH SUBJECT = 'Schema user Certificate'
CREATE USER SchemaCertUser FROM CERTIFICATE SchemaCert
GRANT ALTER ANY SCHEMA TO SchemaCertUser
ADD SIGNATURE TO p_a BY CERTIFICATE SchemaCert
GO

Now when we rerun the stored procedure as our simple login we see that everything works as expected.

Execute as user = 'SimpleLogin'
exec p_a
revert;
GO

The ability to sign stored procedures gives you the power to build a much more secure SQL Server Database system. For example you can sign stored procedures that use dynamic sql and remove the need to grant select rights on the underlying tables.

I have not (yet) benchmarked the performance impact of signing stored procedures, but I suspect the overhead will not be huge.

You can download the complete test script and try it out.

a Daylight savings time aware version of a sqlclr xp_getfiledetails procedure.

One of the undocumented extended stored procedures that no longer exists in SQL Server 2005 is xp_getfiledetails.

Of course I am aware that I am not the first one to implement this procedure as a SQLCLR procedure.

I found implementations on the following locations:

• SSIS Junkie Blog by Jamie Thomson
• Simple Talk by Greg Larsen

But a bug I noticed in both the versions of the procedure is the fact that it is not "Daylight Savings Time" aware.

This means that if you request the file details of a file that is created on a day when Winter Time was in place and currently Summer Time is in use that the timestamps are off 1 hour.

To correct this problem this I wrapped the datetime checks in the following function:

private static TimeSpan ExplorerBias(DateTime timeStamp)
{
    TimeSpan bias = new TimeSpan(0);
    if (TimeZone.CurrentTimeZone.IsDaylightSavingTime(DateTime.Now) != TimeZone.CurrentTimeZone.IsDaylightSavingTime(timeStamp))
    {
        bias = TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now) - TimeZone.CurrentTimeZone.GetUtcOffset(timeStamp);
    }
    return bias;
}

You can download the Complete xp_getfiledetails sqlclr Script and see the effect of the function.

To test the script, create a new Visual Studio 2005 Database project and paste the code from the script (or just include the file.)

Using a schema collection to validate output of FOR XML RAW

Schema collections are used in SQL Server 2005 to create an xml datatype field that is validated with an XML Schema. Unfortunately there is an xsd feature that is not implemented very nicely is the processContents="lax" attribute. The attribute processContents is implemented, but where normal xsd supports the values strict, lax and skip. The sql server schema collection parser only supports strict and skip. And unfortunately using lax does not degrade, it just gives a compilation error. So reusing prebuild xsd files is going to create some issues that need to be resolved before you can compile them in the database. I will show an example where using the processContents="lax" technique would be usefull. The following schema can be used to create a datatype to store a result of a "for xml raw" query. When you set processContents="lax" you will get a compilation error. Although it would be usefull, we have to be content with processContents="skip".

IF EXISTS(SELECT * FROM sys.schemas WHERE name ='xml_raw_schema')
    DROP XML SCHEMA COLLECTION  dbo.xml_raw_schema
GO
CREATE XML SCHEMA COLLECTION xml_raw_schema AS
 N'<?xml version="1.0"?>
 <xsd:schema xmlns:targetNamespace="http://schemas.mycompany.com/myproduct"
            elementFormDefault="qualified"
            attributeFormDefault="unqualified"
            xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <xsd:element name="table">
     <xsd:complexType>
        <xsd:sequence>
           <xsd:element name="row" minOccurs="0" maxOccurs="unbounded">
              <xsd:complexType>
                 <xsd:anyAttribute processContents="skip" />
              </xsd:complexType>
           </xsd:element>
        </xsd:sequence>
     </xsd:complexType>
   </xsd:element>
</xsd:schema>'

You can use the schema collection like this:

declare @xml xml(xml_raw_schema)
SET @xml = (select * from sys.tables FOR XML RAW, TYPE, ROOT('table'))
select @xml

Which shows (in my test case) the following output:

<table>
  <row name="Orders" object_id="2073058421" schema_id="1" parent_object_id="0" type="U " type_desc="USER_TABLE" create_date="2007-04-12T17:30:08.150" modify_date="2007-04-12T17:46:17.043" is_ms_shipped="0" is_published="0" is_schema_published="0" lob_data_space_id="0" max_column_id_used="3" lock_on_bulk_load="0" uses_ansi_nulls="1" is_replicated="0" has_replication_filter="0" is_merge_published="0" is_sync_tran_subscribed="0" has_unchecked_assembly_data="0" text_in_row_limit="0" large_value_types_out_of_row="0" />
</table>

Although we have to be carefull that it does not slow down processing too much I find it a very valuable technique because you can achieve a very basic level of type safety.

Wouldn't it be usefull if sp_who showed the query the user is running.

I can't remember the number of times I have run sp_who2 and then dbcc inputbuffer (##) to quickly check out blocking problems. Wouldn't it be usefull if this information was included in sp_who. Well, with SQL Server 2005 we can finally do this thanks to the power of the function sys.dm_exec_sql_text(). All I really had to change to sp_who2 was adding "OUTER apply sys.dm_exec_sql_text(s.sql_handle)" and then use the stmt_start and stmt_end fields to extract the substring from the query.

Install the procedure below and run:

exec sp_whoo

Executing the procedure will give you the following results:

SPID	Status	Login	HostName	BlkBy	DBName	Command	CPUTime	DiskIO	LastBatch	ProgramName	SPID	REQUESTID	SqlStatement
52	sleeping	sa	MyLaptop	.	test_db	AWAITING COMMAND	110	43	05/24 10:54:40	Microsoft SQL Server Management Studio - Query	52	0	select * from sys.tables

Changes versus the standard sp_who2 in SQL Server 2005:

• Removed all spids before 50 (system threads)
• Removed your own spid.

Limitations:

• Queries running through the service broker do not always return a sql handle. So no data can be shown.
• When accessing encrypted objects, no information can be shown either

You can download the script sp_whoo.sql or read the code below:

USE master
GO
IF (EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_whoo' AND OBJECTPROPERTY(id, 'IsProcedure') = 1))
      DROP PROCEDURE dbo.sp_whoo
GO
CREATE PROCEDURE dbo.sp_whoo(@loginame sysname = NULL)
AS
 
SET NOCOUNT ON
 
declare @retcode         int
 
declare @sidlow         varbinary(85)
       ,@sidhigh        varbinary(85)
       ,@sid1           varbinary(85)
       ,@spidlow         int
       ,@spidhigh        int
       ,@spidself        int
 
declare @charMaxLenLoginName      varchar(6)
       ,@charMaxLenDBName         varchar(6)
       ,@charMaxLenCPUTime        varchar(10)
       ,@charMaxLenDiskIO         varchar(10)
       ,@charMaxLenHostName       varchar(10)
       ,@charMaxLenProgramName    varchar(10)
       ,@charMaxLenLastBatch      varchar(10)
       ,@charMaxLenCommand        varchar(10)
 
declare @charsidlow              varchar(85)
       ,@charsidhigh             varchar(85)
       ,@charspidlow              varchar(11)
       ,@charspidhigh             varchar(11)
 
SELECT @retcode = 0      -- 0=good ,1=bad.
      ,@sidlow  = convert(varbinary(85), (replicate(char(0), 85)))
      ,@sidhigh = convert(varbinary(85), (replicate(char(1), 85)))
 
SELECT @spidlow         = 50
      ,@spidhigh        = 32767
      ,@spidself        = @@spid
 
--------------------------------------------------------------
IF (@loginame IS     NULL)  --Simple default to all LoginNames.
      GOTO LABEL_17PARM1EDITED
 
-- select @sid1 = suser_sid(@loginame)
SELECT @sid1 = NULL
IF EXISTS(SELECT * FROM sys.syslogins WHERE loginname = @loginame)
    SELECT @sid1 = sid FROM sys.syslogins WHERE loginname = @loginame
 
IF (@sid1 IS NOT NULL)  --Parm is a recognized login name.
   begin
   SELECT @sidlow  = suser_sid(@loginame)
         ,@sidhigh = suser_sid(@loginame)
   GOTO LABEL_17PARM1EDITED
   end
 
--------
 
IF (lower(@loginame collate Latin1_General_CI_AS) IN ('active'))  --Special action, not sleeping.
   begin
   SELECT @loginame = lower(@loginame collate Latin1_General_CI_AS)
   GOTO LABEL_17PARM1EDITED
   end
 
--------
 
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0)  --Is a number.
   begin
   SELECT
             @spidlow   = convert(int, @loginame)
            ,@spidhigh  = convert(int, @loginame)
   GOTO LABEL_17PARM1EDITED
   end
 
--------
 
raiserror(15007,-1,-1,@loginame)
SELECT @retcode = 1
GOTO LABEL_86RETURN
 
 
LABEL_17PARM1EDITED:
 
 
--------------------  Capture consistent sysprocesses.  -------------------
 
SELECT s.spid , s.status ,s.sid ,s.hostname ,s.program_name ,s.cmd ,s.cpu ,s.physical_io ,s.blocked ,s.dbid
 , convert(sysname, rtrim(s.loginame)) AS loginname
 , s.spid AS 'spid_sort'
 , substring( convert(varchar, s.last_batch, 111) ,6  ,5 ) + ' ' + substring(convert(varchar, s.last_batch, 113), 13, 8 ) AS 'last_batch_char'
 , s.request_id
 , case when s.stmt_start=0 AND s.stmt_end=0 then qt.text
        else substring(qt.text, (s.stmt_start/2)+1,
                (case when s.stmt_end = -1
                      then len(convert(nvarchar(max), qt.text))*2
                      else s.stmt_end 
                 end - s.stmt_start) / 2) 
   end AS 'sql_statement'
INTO #tb1_sysprocesses
FROM master.dbo.sysprocesses s WITH (nolock)
  OUTER apply sys.dm_exec_sql_text(s.sql_handle) AS qt
WHERE ((s.spid > @spidlow AND s.spid < @spidhigh) OR (s.spid = @spidlow AND s.spid = @spidhigh))
  AND s.spid <> @spidself
  AND (s.sid = @sidlow OR @sidlow <> @sidhigh)
 
IF @@error <> 0
    begin
        SELECT @retcode = @@error
        GOTO LABEL_86RETURN
    end
 
--------Screen out any rows?
 
IF (@loginame IN ('active'))
  BEGIN
    DELETE #tb1_sysprocesses
    WHERE lower(status) = 'sleeping'
      AND upper(cmd)    IN ('AWAITING COMMAND','LAZY WRITER','CHECKPOINT SLEEP')
      AND blocked       = 0
  END
 
 
--------Prepare to dynamically optimize column widths.
SELECT
    @charsidlow     = convert(varchar(85),@sidlow)
   ,@charsidhigh    = convert(varchar(85),@sidhigh)
   ,@charspidlow    = convert(varchar, @spidlow)
   ,@charspidhigh   = convert(varchar, @spidhigh)
 
SELECT
     @charMaxLenLoginName   = convert( varchar, isnull( max( datalength(loginname)) ,5))
    ,@charMaxLenDBName      = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6))
    ,@charMaxLenCPUTime     = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7))
    ,@charMaxLenDiskIO      = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6))
    ,@charMaxLenCommand     = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7))
    ,@charMaxLenHostName    = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8))
    ,@charMaxLenProgramName = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11))
    ,@charMaxLenLastBatch   = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9))
FROM #tb1_sysprocesses
 
--------Output the report.
 
EXEC('
SET nocount off
SELECT
             SPID          = convert(char(5),spid)
            ,Status        =
                  CASE lower(status)
                     When ''sleeping'' Then lower(status)
                     Else                   upper(status)
                  END
            ,Login         = substring(loginname,1,' + @charMaxLenLoginName + ')
            ,HostName      =
                  CASE hostname
                     When Null  Then ''  .''
                     When '' '' Then ''  .''
                     Else    substring(hostname,1,' + @charMaxLenHostName + ')
                  END
 
            ,BlkBy         =
                  CASE               isnull(convert(char(5),blocked),''0'')
                     When ''0'' Then ''  .''
                     Else            isnull(convert(char(5),blocked),''0'')
                  END
            ,DBName        = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
            ,Command       = substring(cmd,1,' + @charMaxLenCommand + ')
            ,CPUTime       = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
            ,DiskIO        = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
            ,LastBatch     = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')
            ,ProgramName   = substring(program_name,1,' + @charMaxLenProgramName + ')
            ,SPID          = convert(char(5),spid)  --Handy extra for right-scrolling users.
            ,REQUESTID     = convert(char(5),request_id)
            ,SqlStatement  = rtrim(ltrim(replace(sql_statement, char(13)+char(10), '' '')))
      from
             #tb1_sysprocesses  --Usually DB qualification is needed in exec().
      -- (Seems always auto sorted.)   order by spid_sort
')
 
LABEL_86RETURN:
IF (object_id('tempdb..#tb1_sysprocesses') IS NOT NULL)
            DROP TABLE #tb1_sysprocesses
 
RETURN @retcode -- sp_whoo
GO

Fix SQL Server management studio Ctrl+Tab annoyance

In SQL Server 2000 Query analyzer hitting Ctrl+Tab would advance you one query window in the list of open query windows. Unfortunately In Sql Server Management Studio this will display the default Visual Studio Tab overview window. This window has one huge flaw and that is that it only displays the first 25 characters of the description of the open window. Which results in the Case of SQL Server Management Studio in a list with 10 times the same item.

To revert the Ctrl+Tab and the Ctrl+Shift+Tab behaviour back to the way it was in previous versions of the SQL Server client tools you can do the following hack. Close all instances of SQL Server Management Studio and open the following xml file in a text editor:

C:\Documents and Settings\<your user name>\My Documents\SQL Server Management Studio\Settings\CurrentSettings-####-##-##.vssettings

Note that the ####-##-## characters form a date (most likely the installation/service pack installation date.) and you sometimes can have multiple versions of this file. Updating the file with the latest date should work. (I have no pc's with multiple versions of this file.) In the file look for the following string. (The file will have a few very long lines of text in it.)

<UserShortcuts />

Once you found it, replace it with the xml code below and when you restart Management studio using Ctrl+Tab and Ctrl+Shift+Tab will once again switch you to the following or previous code window without showing the useless dialog.

<UserShortcuts>
  <RemoveShortcut Command="Window.NextDocumentWindowNav" Scope="Global">Ctrl+Tab</RemoveShortcut>
  <Shortcut Command="Window.NextDocumentWindow" Scope="Global">Ctrl+Tab</Shortcut> 
  <Shortcut Command="Window.NextDocumentWindow" Scope="Query Designer">Ctrl+Tab</Shortcut>
  <RemoveShortcut Command="Window.PreviousDocumentWindowNav" Scope="Global">Ctrl+Shift+Tab</RemoveShortcut>
  <Shortcut Command="Window.PreviousDocumentWindow" Scope="Global">Ctrl+Shift+Tab</Shortcut>
  <Shortcut Command="Window.PreviousDocumentWindow" Scope="Query Designer">Ctrl+Shift+Tab</Shortcut>
</UserShortcuts>

Credit: I found this tip burried in a comment on this page of the Microsoft connect website. So thanks to ayeltsov for suggesting the solution to an annoying problem.

Why is viewing a partitioned table layout so difficult

For some reason the Microsoft SQL Server developers decided not to provide any tools to see how a partitioned table is built. Since it is quite important information I wrote a little sp_help_partition TSQL stored procedure to give an overview. There is an optional object parameter which allows you to filter on a specific partitioned table, or shows you all the partitions in the system.

The procedure is installed on the master database, but because the name starts with sp_... you can run it in any database.

usage:

   1:  exec sp_help_partition 'Orders'

The result of this statement could be:

Table	Partition Scheme	Partition Function	PartitionNumber	FileGroupName	RangeValue
Orders	monthly_scheme	monthly_bucket	1	History	2006-06-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	2	Partition_02	2006-07-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	3	Partition_03	2006-08-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	4	Partition_04	2006-09-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	5	Partition_05	2006-10-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	6	Partition_06	2006-11-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	7	Partition_07	2006-12-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	8	Partition_08	2007-01-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	9	Partition_09	2007-02-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	10	Partition_10	2007-03-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	11	Partition_11	2007-04-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	12	Partition_12	2007-05-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	13	Partition_01	2999-01-01 00:00:00.000
Orders	monthly_scheme	monthly_bucket	14	Partition_99	NULL

You can download the script sp_help_partition.sql or read the code below:

   1:  USE master

   2:  IF EXISTS(SELECT * FROM sys.objects WHERE name = 'sp_help_partition')

   3:    BEGIN

   4:      PRINT 'Dropping procedure sp_help_partition...'

   5:      DROP PROCEDURE sp_help_partition 

   6:    END

   7:  print 'Creating procedure sp_help_partition...'

   8:  GO 

   9:  CREATE PROCEDURE sp_help_partition (@object_name sysname = NULL)

  10:  AS

  11:   

  12:  declare @db_name sysname

  13:  select @db_name = db_name()

  14:   

  15:  if @object_name IS NULL

  16:   begin

  17:      exec ('use ' + @db_name + '

  18:      select 

  19:            object_name(i.[object_id]) as [Table],

  20:            ps.name                    as PartitionScheme,

  21:            pf.name                    as PartitionFunction,

  22:            dds.destination_id         as PartitionNumber,

  23:            fg.Name                    as FileGroupName,

  24:            prv.value                  as RangeValue

  25:      from .sys.indexes i 

  26:      inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id

  27:      inner join sys.partition_functions pf on ps.function_id = pf.function_id

  28:      inner join sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id

  29:      inner join sys.filegroups fg on fg.data_space_id = dds.data_space_id

  30:      left  join sys.partition_range_values prv on prv.boundary_id = dds.destination_id

  31:      where i.type = 1 ')

  32:    end

  33:  else

  34:    begin

  35:      exec ('use ' + @db_name + '

  36:      select 

  37:            object_name(i.[object_id]) as [Table],

  38:            ps.name                    as PartitionScheme,

  39:            pf.name                    as PartitionFunction,

  40:            dds.destination_id         as PartitionNumber,

  41:            fg.Name                    as FileGroupName,

  42:            prv.value                  as RangeValue

  43:      from sys.indexes i 

  44:      inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id

  45:      inner join sys.partition_functions pf on ps.function_id = pf.function_id

  46:      inner join sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id

  47:      inner join sys.filegroups fg on fg.data_space_id = dds.data_space_id

  48:      left  join sys.partition_range_values prv on prv.boundary_id = dds.destination_id

  49:      where i.type = 1 and 

  50:            i.[object_id] = object_id(''' + @object_name + ''')')

  51:    end

  52:  RETURN(0)

  53:  GO

  54:  IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'sp_help_partition' AND type = 'P')

  55:    BEGIN

  56:      PRINT 'PROCEDURE sp_help_partition has been created...'

  57:    END

  58:  ELSE

  59:    BEGIN

  60:      PRINT 'PROCEDURE sp_help_partition has NOT been created due to errors...'

  61:    END

  62:  GO

For more information about table partitioning in Microsoft SQL Server 2005 read the excellent whitepaper by Kimberley Tripp

Fix the user/login sid mismatch when restoring a SQL Server Database on a different server.

When you take a backup of a SQL Server database one of the tables that is saved in the backup is called "sysusers". This table is used to link a server login to a database user. To do this, SQL Server links the "sid" field of the sysusers table to the "sid" field of the syslogins table in the master database.

select name, sid from sysusers

select name, sid from syslogins

When you restore the database backup on another SQL Server, the "sid" fields are out of sync and SQL Server does not fix this automatically. This is a big problem because your database users are pointing to logins that don't exist or worse, pointing to the wrong login. To fix this in a simple way you can run the following script:

   1:  DECLARE @username sysname

   2:   

   3:  SELECT top 1 @username = name 

   4:  FROM sysusers 

   5:  WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null 

   6:   

   7:   

   8:  WHILE @username is not null

   9:    BEGIN

  10:      IF @username='dbo'

  11:        BEGIN

  12:          PRINT N'mapped sa to the dbo user...'

  13:          EXEC sp_changedbowner 'sa' 

  14:        END

  15:      ELSE

  16:        BEGIN

  17:          PRINT N'mapped ' + @username + ' to the dbo user...'

  18:          EXEC sp_change_users_login 'update_one', @username, @username

  19:        END

  20:      SELECT @username = null

  21:      

  22:      SELECT TOP 1 @username = name 

  23:      FROM sysusers 

  24:      WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null 

  25:    END

The script simply tries to link the users to the logins based on the name, and it tries to make sa the default database user. Whew, problems fixed you would think... Not quite. You still have a bunch of users left in your database that are not linked to any logins. To remove this we can use the following script.

   1:  DECLARE @username sysname

   2:   

   3:  SELECT top 1 @username = su.name 

   4:  FROM sysusers su

   5:    left join master..syslogins sl

   6:    on su.name = sl.name

   7:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 

   8:    and suser_sname(su.sid) is null  

   9:    and sl.name is null

  10:   

  11:  WHILE @username is not null

  12:    BEGIN

  13:      print N'Dropping user ' + @username + '...'

  14:      exec sp_dropuser @username

  15:   

  16:      SELECT @username = null

  17:      SELECT top 1 @username = su.name 

  18:      FROM sysusers su

  19:        left join master..syslogins sl

  20:        on su.name = sl.name

  21:      WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 

  22:        and suser_sname(su.sid) is null  

  23:        and sl.name is null

  24:    END

To make using this code more practical I wrapped it in a stored procedure which you can install on the master database. You can download the script sp_fix_user_login_mismatch.sql or read the code below:

   1:  IF object_id('dbo.sp_fix_user_login_mismatch') IS NOT NULL

   2:    BEGIN

   3:      DROP PROCEDURE dbo.sp_fix_user_login_mismatch

   4:    END

   5:  GO

   6:  CREATE PROCEDURE dbo.sp_fix_user_login_mismatch

   7:  AS 

   8:   

   9:  DECLARE @username sysname

  10:   

  11:  -- Map all the database users to the logins that have the same name.

  12:  SELECT top 1 @username = su.name 

  13:  FROM sysusers su

  14:    inner join master..syslogins sl

  15:    on su.name = sl.name

  16:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 

  17:    and suser_sname(su.sid) is null 

  18:   

  19:  WHILE @username is not null

  20:    BEGIN

  21:      IF @username='dbo'

  22:        BEGIN

  23:          PRINT N'mapped sa to the dbo user...'

  24:          EXEC sp_changedbowner 'sa' 

  25:        END

  26:      ELSE

  27:        BEGIN

  28:          PRINT N'mapped the login "' + @username + '" to the database user "' + @username + '"...'

  29:          EXEC sp_change_users_login 'update_one', @username, @username

  30:        END

  31:      SELECT @username = null

  32:      

  33:     SELECT top 1 @username = su.name 

  34:      FROM sysusers su

  35:        inner join master..syslogins sl

  36:        on su.name = sl.name

  37:      WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 

  38:        and suser_sname(su.sid) is null 

  39:    END

  40:   

  41:  -- Remove all the database users that do not have a matching login.

  42:  SELECT top 1 @username = su.name 

  43:  FROM sysusers su

  44:    left join master..syslogins sl

  45:    on su.name = sl.name

  46:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 

  47:    and suser_sname(su.sid) is null  

  48:    and sl.name is null

  49:   

  50:  WHILE @username is not null

  51:    BEGIN

  52:      print N'Dropping user ' + @username + '...'

  53:      exec sp_dropuser @username

  54:   

  55:      SELECT @username = null

  56:      SELECT top 1 @username = su.name 

  57:      FROM sysusers su

  58:        left join master..syslogins sl

  59:        on su.name = sl.name

  60:      WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 

  61:        and suser_sname(su.sid) is null  

  62:        and sl.name is null

  63:    END

  64:  RETURN(0)

  65:  GO

I'm not a fan of giving you a procedure and leaving it to you to figure out if it is good code or not. So to unit test it I have used the following code

   1:   

   2:   

   3:  -- Test harness:

   4:  -- 

   5:  -- Create 3 logins to use as basis for our database users

   6:  -- Create a test database 

   7:  -- map the dbo owner to the fakesa login

   8:  -- add 2 users to the test database based on our 2 logins

   9:  -- backup the test database and drop it

  10:  -- drop fakesa and orphanedlogintest

  11:  -- restore the database and execute our fix procedure.

  12:  -- the user logintest should be remapped

  13:  -- the dbo user should be pointing to sa

  14:  -- the users fakesa and orphanedlogintest should be gone

  15:   

  16:   

  17:  set nocount on

  18:  create login fakesa WITH password = 'a D1ff1cult passw0rd'

  19:  create login logintest WITH password = 'a D1ff1cult passw0rd'

  20:  create login orphanedlogintest WITH password = 'a D1ff1cult passw0rd'

  21:  create database testloginproblems

  22:  GO

  23:  use testloginproblems

  24:  EXEC sp_changedbowner 'fakesa' 

  25:  exec sp_adduser 'logintest', 'logintest', 'db_datareader'

  26:  exec sp_adduser 'orphanedlogintest', 'orphanedlogintest', 'db_datareader'

  27:  use master

  28:  EXEC sp_addumpdevice 'disk', 'TestLoginProblemsData', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\TestLoginProblemsData.bak'

  29:  backup database testloginproblems TO TestLoginProblemsData

  30:  drop database testloginproblems

  31:  drop login fakesa

  32:  drop login logintest

  33:  drop login orphanedlogintest

  34:  create login logintest WITH password = 'a D1ff1cult passw0rd'

  35:  RESTORE DATABASE testloginproblems FROM TestLoginProblemsData

  36:   

  37:  use testloginproblems

  38:  print N''

  39:  SELECT su.name as 'list of problematic users'

  40:  FROM sysusers su

  41:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 

  42:    and suser_sname(su.sid) is null 

  43:   

  44:  exec sp_fix_user_login_mismatch

  45:   

  46:  print N''

  47:  SELECT ul.uname as 'user name to check', su.name as 'database user', sl.name 'server login'

  48:  FROM (select uname = 'logintest' union all select uname = 'dbo' union all select uname = 'fakesa' union all select uname = 'orphanedlogintest') ul

  49:    left join sysusers su

  50:    on ul.uname = su.name

  51:    left join master..syslogins sl

  52:    on su.sid = sl.sid

  53:   

  54:  use master  

  55:  EXEC sp_dropdevice 'TestLoginProblemsData', 'delfile'

  56:  drop database testloginproblems

  57:  drop login logintest