Yesterday I was debugging some SQL Server transaction logic, and came across the following handy command to see which transactions are running:

SELECT * 
FROM sys.dm_tran_session_transactions

Sometimes you want to clear out a database, but you can't just drop the entire database and create a new one. Recently, a colleague needed to do just that, but couldn't because the database was hosted elsewhere. Here is a simple script that works to drop all objects in any database I've tested it against so far. If your database contains object types that mine don't, you might need to add to this script using the same patterns established here.

declare @n char(1)
set @n = char(10)

declare @stmt nvarchar(max)

-- procedures
select @stmt = isnull( @stmt + @n, '' ) +
    'drop procedure [' + name + ']'
from sys.procedures

-- check constraints
select @stmt = isnull( @stmt + @n, '' ) +
    'alter table [' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
from sys.check_constraints

-- functions
select @stmt = isnull( @stmt + @n, '' ) +
    'drop function [' + name + ']'
from sys.objects
where type in ( 'FN', 'IF', 'TF' )

-- views
select @stmt = isnull( @stmt + @n, '' ) +
    'drop view [' + name + ']'
from sys.views

-- foreign keys
select @stmt = isnull( @stmt + @n, '' ) +
    'alter table [' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
from sys.foreign_keys

-- tables
select @stmt = isnull( @stmt + @n, '' ) +
    'drop table [' + name + ']'
from sys.tables

-- user defined types
select @stmt = isnull( @stmt + @n, '' ) +
    'drop type [' + name + ']'
from sys.types
where is_user_defined = 1

exec sp_executesql @stmt

Things You Didn't Know About Temp Tables and Table Variables

Lately I've needed to compare the schemas of two or more databases and highlight the differences. Based on internet research, I settled on two contenders: AdeptSQL Diff and Red Gate SQL Compare. Both are reasonably fast. AdeptSQL's UI looks very 16-bit and has limited reporting options. Red Gate's UI is very attractive and offers more reporting options. However, Red Gate's product currently has a bug that considers quoted and unquoted trigger identifiers to be different, even if the identifier itself is the same. This resulted in a large number of false differences, forcing me to go with AdeptSQL for now. If Red Gate can fix this problem, I would be inclined to switch to their product in the future.

This is actually right in the SQL Server Books Online, but not easily found. Given a temp table named #temp:

if ( object_id( 'tempdb..#temp' ) is null )
    -- table doesn't exist
else
    -- table exists

Here is an interesting article on a way to turn many looping row-based operations into set-based ones: http://www.sqlservercentral.com/articles/TSQL/62867/

I think the idea is a clever one, and I love the "Row By Agonizing Row" (RBAR) acronym!

Falafel is partnering with Microsoft to offer this free half day seminar at the beautiful Saint Claire hotel in downtown San Jose, CA to celebrate the release of Visual Studio 2008, LINQ, WPF, WCF, WF and other exciting technologies.

Wow!  I was told so many times that Visual Studio 2008 will ship in 2007, well it does not sound like that is a possibility anymore.

Microsoft released a new date, February 27th 20008 to release Windows Server 2008, Visual Studio 2008 and SQL Server 2008

	963

You may run into the 15023 error if you restore a MS SQL database from backup.  You expect a restored database to be in exactly the same state as the backup, but the login fails for a user that had permissions in the backed up database.  When you use the "User Mapping" SQL Management Studio functionality to allow the user permissions to the new database, you receive the 15023 error.  This is caused by Security identification numbers (SID) that are mismatched or 'orphaned' in the sysusers table. 

The SQL Server stored proc sp_change_users_login locates and fixes these records.  Run it with a single parameter 'Report' to get a listing of abandoned user names and corresponding SIDs:

exec sp_change_users_login Report

The 'Update_One' parameter will reconnect a single login:

exec sp_change_users_login Update_One, 'MyLogin', 'MyLogin'

You can find more info about this issue at:

http://support.microsoft.com/kb/246133

http://support.microsoft.com/kb/240872

This next blog expands on the available parameters for sp_change_users_login:

http://blog.sqlauthority.com/2007/02/15/sql-server-fix-error-15023-user-already-exists-in-current-database/

Also, try checking out the source for sp_change_users_login found in the Sql Server Management Studio under Databases | System Databases | Master | Programmability | Stored Procedures | sp_change_users_login.

SQL Management Objects (SMO) is something of a Swiss army knife that lets you traverse meta data, automate backup and restore, and otherwise manage SQL Server 2005 through .NET code.  For instance, to perform the classic database hierarchy walk use the SmoApplication object and enumerate the servers.  First you need to reference the Smo assemblies:

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll

Then call the SmoApplication EnumAvailableSqlServers() method, passing 'true' to list only local servers:

1 2 3 4 5 6 7

if (server.PingSqlServerVersion(server.Name).Major > 8) { foreach (UserDefinedDataType udf in database.UserDefinedDataTypes) { Console.WriteLine("Type: {0}", udf.Name); } }

A nifty SMO bonus is that objects Database and downwards in the hiearchy have a Script StringCollection property.  Script is pre-populated with T-SQL and can be used to recreate objects.

SMO provides DBA automation functionality like backup/restore and create/drop databases. These operations depend on the SMO Server object.  The constructor for Server can have a) no parameters for your local server, b) a server name only, or c) a ServerConnection object in case you need to supply user name and password.

1 2	ServerConnection serverConnection = new ServerConnection("MyMachine", "sa", "MyM@ch1n3"); Server raServer = new Server(serverConnection);

Here is an example of creating a new database to be used as a backup location:

1 2 3 4 5 6 7 8

static Database CreateBackupDatabase(Server server, string databaseName) { string backupDatabaseName = databaseName + "_" + DateTime.Now.ToString("yyMMddHHmmffffff"); Database backupDatabase = new Database(server, backupDatabaseName); Console.WriteLine("Creating new database {0}", backupDatabase.Name); backupDatabase.Create(); return backupDatabase; }

The backup operation requires a backup "device", in this case a file that will be used to store the backup.  Assign an SMO Database object to be backed up, add the backup device to its list of devices and call the SqlBackup() method.  You may also want to assign event handlers for PercentComplete and Complete events.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

static void DoBackup(Server server, string databaseName, Database backupDatabase) { Console.WriteLine("Backing up {0}", databaseName); BackupDeviceItem backupDevice = new BackupDeviceItem(databaseName + ".bak", DeviceType.File); Backup backup = new Backup(); backup.Database = databaseName; backup.Devices.Add(backupDevice); backup.PercentComplete += new PercentCompleteEventHandler(InProgress); backup.Complete += new ServerMessageEventHandler(Complete); backup.SqlBackup(server); } static void InProgress(object sender, PercentCompleteEventArgs e) { Console.WriteLine("Percent complete: {0}", e.Percent); }

Restoring can sometimes be trickier due to the original database having a strangle-hold on the physical data and log files.  To work around this use the Restore RelocateFiles property to map new file names and locations. Also notice that to get the physical file path we use the Database object's FileGroups property, drill down into the Files and use the path of the first file in the list.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

static void DoRestore(Server server, string databaseName, Database backupDatabase) { Console.WriteLine("Restoring {0}", backupDatabase.Name); Restore restore = new Restore(); restore.Database = backupDatabase.Name; restore.ReplaceDatabase = true; string backupPath = Path.GetDirectoryName(backupDatabase.FileGroups[0].Files[0].FileName); string dataPath = string.Format("{0}\\{1}.mdf", backupPath, backupDatabase.Name); restore.RelocateFiles.Add(new RelocateFile("Falafel", dataPath)); restore.RelocateFiles.Add(new RelocateFile("Falafel_log", Path.ChangeExtension(dataPath, ".ldf"))); BackupDeviceItem backupDevice = new BackupDeviceItem(databaseName + ".bak", DeviceType.File); restore.Devices.Add(backupDevice); restore.PercentComplete += new PercentCompleteEventHandler(InProgress); restore.Complete += new ServerMessageEventHandler(Complete); restore.SqlRestore(server); }

Thats a few of the things SMO can do for you. It's not a replacement for T-SQL, but if you need access to meta-data, automation or other DBA tasks from managed code, then the SMO namespace may be worth exploring. BTW, you can also use SMO in PowerShell directly, wrapped in commands or as the infrastructure for PowerShell providers.

Read all about it here. Something that looks very interesting is support for sometimes connected applications...

I recently ran into an unexpected behavior of SSRS while writing a complex report. The report required various calculations that would refer to specific previous groups and details, and I had decided to solve the problem by writing a custom report function that would evaluate on each detail row, saving the information I would need later in a dictionary. My solution worked fine, as long as my custom calculation in a group footer didn't depend on the contents of that group's details. Upon further investigation, I learned something very interesting about the order in which SSRS evaluates group headers, footers, and details...

Here is how to observe the behavior for yourself: create a stored procedure like this one, that generates some simple test data:

CREATE PROCEDURE dbo.HundredRows
AS
SET NOCOUNT ON

create table #result (
id int,
grp int
)

declare @id int
set @id = 1

while @id <= 100
begin
insert #result
values ( @id, ( @id - 1 ) / 5 )

set @id = @id + 1
end

select *
from #result

This will return a result set whose id column increments in steps of one from 1 to 100, and whose grp column increments in steps of one for every five rows. Next, create a simple report layout with a table, showing the values of each column and grouping by the grp column. In the group header and footer, include the same column references. Next, add this custom code:

Dim eval_order As Integer = 0

Public Function show_eval_order() As String
  eval_order = eval_order + 1
  Return eval_order.ToString()
End Function

Display the result of this function in the group header, footer, and detail rows. Your layout should look like this:

Once you have the report set up, click on the preview tab to see the surprising results:

I've only shown the first two groups here, but notice what has happened! The order column clearly indicates that the group header and footer are both evaluated before the details in between! By looking at the value of the id column in the footer, we can see that the the group's header and footer rows are printed while the "current" row in the dataset is still positioned on the first row in the group.

While this is interesting, it isn't very important as long as you stick to using SSRS's built-in aggregation functions. It is only when you attempt to "roll your own" by saving the dataset in your own storage for later reference that you might run into trouble. In my case, I used the following workarounds:

1. Move calculations that rely on the details having been traversed from the group footer to the following group's header. It's clunky and less intuitive, but that's what SSRS has forced upon us.
2. For the final group, move the calculation out of the table entirely, and put it into textboxes just below the table. While there is no way to tell SSRS to evaluate the textboxes after it evaluates the table, it does so at least for the 2005 version.

In summary, it sure would have been nice if SSRS would have evaluated group headers and footers after it evaluated the details that each group contains, but since we have clearly demonstrated that it doesn't, we now know what we will have to do in order to make custom report code produce the output we want: by putting those custom calculations into the following group's header, and by putting the final footer calculation outside the table entirely.

If you need to monitor your transactional replication with a custom monitoring service, Microsoft has provided some useful tools to help. Recently I was having trouble reliably monitoring my replication, and then I discovered this page.

http://msdn2.microsoft.com/en-us/library/ms146951.aspx

If you’re using transactional replication there is no better way to monitor its health than using your own tracer token. This is just like inserting a tracer token using the replication monitor utility in SQL Server Management Studio.

First you need to create a connection to the server.

   server = new ServerConnection(sci); 

then create a TransPublication object.

    TransPublication transPublication = new TransPublication(publicationName, publicationDBName, server);

Call LoadProperties of the new object to make sure that all publications and subscriptions are loaded.

   transPublication.LoadProperties(); 

Then post the tracer token and call refresh to send it on its way. You need to save the ID of the token so that you can clean it up later.

   id = transPublication.PostTracerToken();
   transPublication.Refresh(); 

Now that the tracer token is on its way we need to create a publication monitor to look for its return. You have to go through a couple of layers to get to it.

   Microsoft.SqlServer.Replication.ReplicationMonitor monitor =

      new Microsoft.SqlServer.Replication.ReplicationMonitor(server);
   PublisherMonitor pub = monitor.PublisherMonitors[publisherMonitor];
   PublicationMonitor publicationMonitor = pub.PublicationMonitors[publicationDBName, publicationName];

Now we need to enumerate all of the tokens in the publication. You must call LoadProperties to refresh this list.

   publicationMonitor.LoadProperties();
   ArrayList tokens = publicationMonitor.EnumTracerTokens(); 

You can cast the items in the array list to a TracerToken type to find the token we sent with the ID we saved earlier.

   TracerToken token = null;
   foreach (TracerToken t in tokens)
   {
      if (t.TracerTokenId == tokenID)
      {
         token = t;
         break;
      }
   } 

Now that we have our token we need to enumerate the tracer token history with this call.

   DataSet tth = publicationMonitor.EnumTracerTokenHistory(token.TracerTokenId); 

This returns a dataset that you need to parse through to get the data we are interested in. These include distributor latency, subscriber latency, and overall latency. If these values are blank, then the token has not returned yet. You need to then enumerate the tokens and check again. When the token has returned you then need to cleanup. A simple call will do this for us.

   publicationMonitor.CleanUpTracerTokenHistory(tokenID);

If any part of the replication fails for any reason, the token will fail to return and you know you have a problem.

While I was writing a stored procedure that did some string manipulation and was debugging some unexpected behavior, I came across a fascinating, infurating, hidden-in-plain-sight "feature" of the LEN function: it returns the length of a string expression excluding trailing blanks. So, for example:

declare @string varchar(10)
set @string = '123456789 '

declare @len int
set @len = len( @string )

print @len
print right( @string, @len - 5 )

returns 9 and '789 ', not the expected 10 and '6789 '. Also, with this code:

declare @string varchar(10)
set @string = '123456789 '

declare @len int, @reverse_len int
set @len = len( @string )
set @reverse_len = len( reverse( @string ) )

print @len
print @reverse_len

One might expect that the length of a string would be the same forwards and backwards, but it isn't! @len = 9 and @reverse_len = 10!

A workaround for non-Unicode character data is to use the DATALENGTH function, which returns the size of an expression in bytes. If you choose to use DATALENGTH with Unicode data, you must divide by 2 to get the actual string length. Another workaround would be to append a non-blank character to the end of the string, get the length, then subtract one. Neither is very nice. If we had wanted to exclude trailing blanks, we could have called RTRIM before LEN ourselves.

I've been writing a lot of dynamic SQL that generates other SQL lately. While SQL isn't the best string manipulation language, it's got great native access to database schema information. However, my latest project was resulting in some very long scripts that coudn't be printed with PRINT, because it truncates the output after 8000 bytes, which is 8000 chars or 4000 nchars. One solution to this would have been to execute dynamic SQL to create a stored procedure containing the text, but in this case, I wanted to append multiple scripts together, so I chose to use PRINT as a simple way to output them all. The concept was simple: For each 4000 character Unicode block, find the last occurrance of CR, LF, or both, print the left part, and repeat on the right part. However, I ran into a few obstacles on the way; see my last two T-SQL Gotchas. Here is the final result, a general-purpose PRINT replacement that can handle any Unicode string, assuming that it contains at least one CR or LF every 4000 characters. The delimiter(s) could be parameterized, but I'll leave that as an exercise for you.

-- Works around the 4000/8000 character limit of the print statement
CREATE PROCEDURE dbo.LongPrint( @string nvarchar(max) )
AS
SET NOCOUNT ON

set @string = rtrim( @string )

declare @cr char(1), @lf char(1)
set @cr = char(13)
set @lf = char(10)

declare @len int, @cr_index int, @lf_index int, @crlf_index int, @has_cr_and_lf bit, @left nvarchar(4000), @reverse nvarchar(4000)
set @len = 4000

while ( len( @string ) > @len )
begin
   set @left = left( @string, @len )
   set @reverse = reverse( @left )
   set @cr_index = @len - charindex( @cr, @reverse ) + 1
   set @lf_index = @len - charindex( @lf, @reverse ) + 1
   set @crlf_index = case when @cr_index < @lf_index then @cr_index else @lf_index end
   set @has_cr_and_lf = case when @cr_index < @len and @lf_index < @len then 1 else 0 end
   print left( @string, @crlf_index - 1 )
   set @string = right( @string, len( @string ) - @crlf_index - @has_cr_and_lf )
end

print @string

This is an interesting article about the performance and scalability of temp table and table variable in SQL Server 2000 and 2005. http://www.lakesidesql.com/articles/?p=12

As an application developer, I often need to generate scripts for mutiple database objects, each in its own file, with both a DROP and a CREATE statement. In SQL Server 2000, there was a robust set of scripting options to choose from which allowed exactly this kind of use. However, in SQL Server 2005, these options have vanished. All selected objects get put into a single script file, and there is no way to generate DROP and CREATE in a single script. Some options can be accessed by choosing the Tasks | Generate Scripts... menu item at the database level, but they don't provide the desired functionality, and the wizard itself is cumbersome to navigate.

In response to this loss of functionality, Bill Graziano wrote a utility that mimics these commonly-used options of SQL scripting. The original blog, which contains links to an installer as well as source code, can be found here.

A lot has been written about the merits of multiple narrow indexes versus single larger compound indexes. This blog by Grant Fritchey yields juicy food for thought on that very topic, backed by some very thorough testing with real-world data.

I love working with MS SQL Server. Oh sure, Oracle is supposedly faster and all that jazz, but when it comes to rapid development, MSSQL's ease of use just can't be beat. However, there are still some things to watch out for. Today I want to warn you all off of using UNION.

Conceptually, there's nothing wrong with UNION. It takes two result sets with congruent columns and returns them as a single result set. There are two potential performance issues, however, one of which can be quite serious.

The first problem is that the UNION operator automatically performs a DISTINCT operation on the combined result set. If it's unnecessary, or worse, if it is necessary, but you've also used SELECT DISTINCT in one or both of the UNIONed queries, then UNION is causing a loss of performance for nothing. The workaround to this is simple; use UNION ALL instead.

The second problem can be far worse. I've run into situations many times where the UNION (or UNION ALL) operator caused inexplicably poor performance. In one recent scenario, I used UNION ALL to merge the results of two queries, each of which ran in about 7 seconds. When merged using UNION ALL, query execution time leapt up to 1:20. Neither the estimated execution plan nor the actual one reflected this; the total subtree cost was exactly the same as the sum of the two queries' costs when run separately. The fix is a little more complex, but extremely effective.

My solution to the problem is to use a table variable. In general, I recommend the use of table variables over temp tables unless you absolutely need a feature that temp tables support but table variables do not.

Example

The example below is not intended to demonstrate good database design; it merely serves to illustrate the solution. Given the original query:

SELECT FirstName, LastName, Salary
FROM Employees
UNION ALL
SELECT FirstName, LastName, Salary
FROM Managers

First declare a table variable with columns that match that of the result set:

DECLARE @Result TABLE (
  FirstName varchar(50),
  LastName varchar(50),
  Salary money,
  -- Example of how to declare a PK within a table variable
  PRIMARY KEY ( LastName, FirstName )
)

Next, insert each query into the result table:

INSERT @Result
SELECT FirstName, LastName, Salary
FROM Employee

INSERT @Result
SELECT FirstName, LastName, Salary
FROM Manager

Finally, select the results:

SELECT *
FROM @Result

This workaround will not only avoid potential performance problems with UNION, but will actually allow you to improve join performance through the definition of a good primary key, if the UNION result is joined to other data.