March 2011 - Posts

A host of cumulative updates for SQL Server released!
22 March 11 01:06 PM | MartinBell | with no comments

Yesterday saw a whole host of Cumulative updates for SQL 2005 SP3 and 4 and SQL 2008 RTM released

Cumulative Update #2 for SQL Server 2005 Service Pack 4

Cumulative Update #3 for SQL Server 2005 Service Pack 4

Cumulative Update #14 for SQL Server 2005 Service Pack 3

Cumulative Update #15 for SQL Server 2005 Service Pack 3 

Cumulative Update #6 for SQL Server 2008 R2 RTM

Filed under:
SMO Enumerations in Powershell
17 March 11 11:20 PM | MartinBell | 1 comment(s)

In my Changing Database Collations post I mentioned the difficulty I had calling the Alter method on the database when specifying a termination clause. The examples in Books Online and MSDN were not at all relevant, and the solution was not immediately obvious (to me). In the end it turned out to be quite simple and version 2 of my Process-Database function is:

function Process-Database ( [string] $NewCollation ){
    try {
        $db.UserAccess = "Single" ;
        $db.Alter([Microsoft.SqlServer.Management.Smo.TerminationClause]::RollbackTransactionsImmediately) ;
        $db.Collation = $NewCollation ;
        $db.UserAccess = "Multiple" ;
        $db.Alter() ;
        Write-Log "End Process-Database" ; 
    catch { 
        $err = $Error[0].Exception ; 
        Write-Log "Error ruuning script $Sqlfile : " ; 
        Write-Log $err ; 
        Throw "Error ruuning script $Sqlfile" ; 

Another Enumeration is the RecoveryModel with possible members/values of Full, Simple and BulkLogged. I posted the following community content on MSDN for approval:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")  | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)") ;
$db = New-Object Microsoft.SqlServer.Management.Smo.Database($srv, "Test_SMO_Database") ;
$db.Create() ;
Write-Host $db.CreateDate ;
Write-Host "`r`n------------------------------------------`r`n";
$db.DatabaseOptions ;
$db.UserAccess = "Single" ;
$db.Alter([Microsoft.SqlServer.Management.Smo.TerminationClause]::RollbackTransactionsImmediately) ;
$db.DatabaseOptions.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple ;
$db.UserAccess = "Multiple" ;
$db.Alter() ;
Write-Host "`r`n------------------------------------------`r`n------------------------------------------`r`n" ;
$db.DatabaseOptions ;
Write-Host "`r`n------------------------------------------`r`n";

Filed under: ,
Collations in a FK relationship
16 March 11 09:48 PM | MartinBell | 1 comment(s)

Anyone who studied the Powershell script in my previous post may have seen the following code to remove foreign keys that reference an index that has a column with the database collation I am changing:

        $list = @{}
            foreach ($tbl in $db.Tables) {
                foreach ($fk in $tbl.ForeignKeys) {
                    foreach($fkcol in $fk.Columns) {
# Columns in a Foreign Key must have the same collation as those they are referencing
                        $col = $tbl.Columns[$fkcol.Name];
                        if ( $col.Collation -eq $db.Collation ) {
                            if(-not $list.Contains($fk)) {
                                $list.Add($fk, $tbl.Name);
            } ;

If I don’t drop the foreign key I won’t be able drop the index itself.

As the comment shows, I use the fact that the collations of the columns in the foreign key must be the same as the ones in the index. To prove this this is the case I wrote the following SQL script:

CREATE TABLE t1 ( col1 varchar(10) COLLATE Latin1_General_CS_AI NOT NULL,
      col2 varchar(10) COLLATe Latin1_General_100_CS_AI NOT NULL,
      CONSTRAINT pk_T1 PRIMARY KEY (col1) )


Trying to create the Foreign Key will give the following error:

Msg 1757, Level 16, State 0, Line 1
Column 't1.col1' is not of same collation as referencing column 't1.col2' in foreign key 'FK_T1_Col2_Col1'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Filed under: ,
Changing database collations with Powershell
07 March 11 09:02 PM | MartinBell | 5 comment(s)

Changing a database collations is rarely as simple as it may seen. You may have found articles which show you the ALTER DATABASE statement such as Example B, unfortunately that example only works because you have just created the testDB and nobody has connected to it!

Anyone who has tried to do this, will soon find the reality is that it is far from simple. Looking through the newsgroup/forums or just going through the pain of doing it yourself will make you realise that to fully change a database collation you will need to propagate the collation to all character based columns in every table. This can be a huge task so using Powershell seems an obvious choice to make the task easier, and example scripts such as this will show you how to change the collation of a column..

If you are very very very very lucky that is all you will need to do, but looking at shows that it will rarely be that simple. Character columns that have statistics, primary keys/indexes or check constraints will require them to be dropped before the collation can be changed. What’s more, if there is a foreign key referencing the index or primary key or a full text index using an index, they also have to be dropped before the primary key/index. Views or functions that are schema-bound have to be removed before changing the table they are bound to.

Anyone who has tried to solve this problem themselves may find that they are just moving from problem to problem without getting much nearer to solving it. After a while it may seem easier to give up and resort to creating a new database and schemas with the correct collation and then transferring all the data.

As an exercise I decided to try and write a script in Powershell that would do this frustrating and tedious task. My test database was the Adventureworks2008 database, so any issues that don’t manifest themselves during the testing may not be included!! If you do find anything that could improve the script let me know.

Initially I started out identifying the columns that had the database collation. The procedure Process-Indexes will all identify Indexes/Primary Keys that include columns with the database collation. Process-Statistics identifies columns with the database collation that have statistics created on them. Process-ForeignKeys will determine which Foreign keys and Process-Fulltextindexes will determine which Full Text Indexes need to be removed before the Indexes/Primary Keys are dropped. Using this method only the minimum number of changes are made and indexes aren't dropped or recreated when it isn't necessary. When I wrote Process-CheckConstraints it used a similar method, but when I tried to change the database collation I had additional errors referencing other constraints not removed by the function
, so I changed it to use a sledgehammer approach and removed all the constraints. Similarly Process-SchemaBoundFunctions and Process-SchemaBoundViews drop all schema-bound functions and views, as I would expect only a few of these to be present in a database dropping all of the schema-bound objects should be an issue.

Process-ComputedColumns will drop all computed columns because some computed columns that did not have the collation set in sys.columns still caused errors when trying to change the database collation. By dropping and recreating these columns then the column order in some tables may change. If you have some poorly written code that relies on the column order, then this will be an issue. The solution are either, change the code (my prefered solution if possible!) or re-create the table specifying the columns in the order you want. I will leave this up to you, but to help I have written the position of the computed column in the log file.

The functions Process-DependentCheckConstraints, Process-DependentFunctions, Process-DependentProcedures, Process-DependentViews, Process-DependentTriggers uses sys.sql_dependencies to identify which related objects need to be scripted and dropped before the columns can be changed. This should catch most of the objects that needs dropping but can't be not identified by the collation. Originally I had used Invoke-Sqlcmd as a cursor as shown in my previous blog post, SQL Server MVP Ben Miller suggested I use a dataset returned from ExecuteWithResults as this would use the existing connection. Ben also suggested using the hash table to obtain the unique list of objects to drop.

Once all constraints have been removed Process-Columns can then change the collation of all the columns in each table that has the database collation and Process-Database will change the database collation itself. Originally Process-Database used invoke-sqlcmd as I found it easier to implement than using SMO, but after re-visiting the problem I managed to get it working!

Process-SQLFile will run a given SQL script. This will allow the re-creation of the constraints etc. that had previously been scripted and removed.

Once I had created these functions it is then a matter of calling them in the correct order!
The full script to change the collation can be found HERE!!.

Simple help has been included in the script so using the command:

get-help .\Collation-Change.ps1

will give you information about the script and

get-help .\Collation-Change.ps1 –Examples

will show you examples of how to call the script. The script can use either Windows or SQL Server authentication. If the –Username parameter is specified SQL Server Authentication will be used.

The script comes with the usual “Health Warning” it has had limited testing. Always try the script on a backup of your database before running it against a live system. Due to the changes made by the script, I advise you any replication, log shipping, snapshots and database mirrors are removed. The script has not been tested with these in place. The database should be in simple recovery mode to avoid the log file growing too large, so restoring a backup will be the fallback recovery position. Make sure you can recover from this backup before running the script.

Thanks to Ben Miller for his invaluable help putting this post together.

** Update **

Even though I have not been able to re-create the problem experienced by JMAlvarez (see comments) the change so that you pass the server object to the scripter rather than the name should be made as it is expecting a server object (see here).~

During testing this change I have found that the SMO Statements can time out and processing the SQL Files (particularly the indexes) can also time out, so I have changed the script to include the timeout parameter. This may require changing depending on your environment/database.

I have also changed the processing of computed columns as this had errors with computed columns being duplicated in the column collection. I think this problem may have been due to the database, but the code is fine for all databases.

I have updated the script to work on SQL 2012 and include the sqlps module instead of loading the SqlServerCmdletSnapin100 snapin.

Filed under: ,
Index Structure
04 March 11 02:48 PM | MartinBell | 1 comment(s)

At the Manchester and Leeds Usergroup meetings David Betteridge presented a great talk about Index Structures. The slides for the talk can be found here and the scripts are here.

David wrote a great application to display page information and the structure of the indexes. This was a wonderful way to show the differences in the structure of indexes of different types.

The application uses some of the undocumented functions found in SQL 2008, for more information on these see Paul Randal's blog. The application will connect to the local default instance and IndexDemo database. This database is created at the start of the “Demo 1 - Max Row Size.sql” script.

The first screen you will see allow you to view the pages in a table if you choose the show table button, you can view the data pages and the data they contain. For a heap the pages aren’t linked so you will see the data as follows:

For an table with a clustered index the pages are doubly-linked shown by the arrows:

The show index option will display index pages for clustered and non-clustered indexes, such as the following clustered index with 1 index page and 3 data pages the output from DBCC IND shows page 94 as the index page and pages 90, 109 and 110 as data pages. 


The application shows this graphically 


This is a great application and if you follow the scripts David provided for the presentation you will see exactly how indexes are structured.

Filed under:
SQLBits 8 Agenda Published
03 March 11 10:06 AM | MartinBell | with no comments

Anyone who has been waiting for the agenda for SQLBits can now find the agendas for both Friday and Saturday at

To change the Conference Day choose the day from the drop down list.

This will be our biggest confernce to day as we have put added additional session in Kings East from 10:50 onwards. This means you have 6 sessions to choose from rather than the normal 5. We've also added some early sessions at 08:10 on Sturday for people staying overnight or arriving early and want to get the extra session in.

A new feature are the lightning talks, this allows speakers to present for 5 minutes on something they are passionate about. It is ideal for new speakers as you gain experience of presenting without having to prepare an hour long session.

Anyone who isn't registered should do as we are quickly as possible because we are very close to the number of people the venue can manage. Anyone registered who can no longer attend should cancel their place to allow someone else to be there. Please note that the early bird prices end at midnight on the 11th March 2011.

Filed under:

This Blog

SQL Blogs