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.

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...

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.

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

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