Distinct Values based on a single column

Distinct is a great tool! However, sometimes I find myself trying to retrieve records that are distinct in repsect to a single column. consider the folowing.

SELECT c1 , c2 
FROM (
SELECT 1 c1,'a' c2
UNION ALL
SELECT 1,'d'
UNION ALL
SELECT 2,'b'
UNION ALL
SELECT 3,'c'
UNION ALL
SELECT 4,'e'
UNION ALL
SELECT 4,'f'
) q1

This will return the rowset

c1 c2  
1  a
1  d
2  b
3  c
4  f
4  e

Well what if I only wanted to return distinct values based on c1 so that my result set looks like this:

c1 c2   
1  d
2  b
3  c
4  f

Distinct wont Quite get the job done in that case since even though c1 has repeated values the other rows in the data set make the row as a whole unique.

Review the following

SELECT c1 , c2 , ROW_NUMBER() over (PARTITION by c1 order by RAND() ) RowNum
FROM (
SELECT 1 c1,'a' c2
UNION ALL
SELECT 1,'d'
UNION ALL
SELECT 2,'b'
UNION ALL
SELECT 3,'c'
UNION ALL
SELECT 4,'e'
UNION ALL
SELECT 4,'f'
) q1

In this case we are setting up a rownumber for each row. The parition by statement makes the number specific to column specified. this will return the following result set

c1 c2 RowNum
1  d  1
1  a  2
2  b  1
3  c  1
4  f  1
4  e  2

so as you can see subsequent rows that have duplicate c1 values have a value for rownum greater than 1. If we wrap this in a outerquery such as

select * from (

SELECT c1 , c2 , ROW_NUMBER() over (PARTITION by c1 order by RAND() ) RowNum
FROM (
SELECT 1 c1,'a' c2
UNION ALL
SELECT 1,'d'
UNION ALL
SELECT 2,'b'
UNION ALL
SELECT 3,'c'
UNION ALL
SELECT 4,'e'
UNION ALL
SELECT 4,'f'
) q1
) q2 where RowNum <2

we get this :

c1 c2 RowNum
1  d  1
2  b  1
3  c  1
4  f  1

which is exactly what we were looking for!

Powershell Detecting DB state(Mirroring, Primary,Failover)

This script pulls back the status of your DBs on a selected server. Its just a base script; modify as needed.

$server = "dwhtest-new"
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $server
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$dbs = $srv.Databases
foreach ($db1 in $dbs)
{
$db = New-Object Microsoft.SqlServer.Management.Smo.Database 
$db = $db1
$DatabaseName = $db.Name
Write-Host $DatabaseName
Write-Host "MirroringStatus:" $db.MirroringStatus
Write-Host "DBState:"  $db.Status
Write-Host
}     

If your DB's mirroring is still intact you will recieve 'Synchronized' for MirroringStatus and its its the Primary it will say "Normal" for the status and if its the failover it will say "Restoring". Unfortunately there is no way, that im aware of, to just pull out the status of "Mirror" or "principle". You will jsut have to build logic to check both fo those values. Restoring

String Concatenation

Concatenate multiple rows into a comma delimited string.

declare @table table (
id int
,val varchar(2)
)
insert @table
Values (1 ,'a'),
(1,'b'),
(1,'c'),
(1,'d'),
(2,'a'),
(2,'b'),
(2,'c'),
(3,'a'),
(3,'b')


select id, stuff((
select (','+val) from @table q1
where q1.id = q2.id
for xml path('')),1,1,'') val
from @table q2
group by q2.id

String Split in SQL Server (No storeproc or looping)

Common problem in sql server is manipulating strings. Whether this involves converting multiple rows of data into one row or splitting them there is a multitude of examples of either. Having some wextensive experience with manipulating xml directly in sql server in set based fashion, I decided to take a diffrent approach to the splitting of strings.
Consider the following example:
.

declare @String varchar(200)
set @string = 'test,test2,test3'

select @string

This would simply return a single row with the string showing as it was set. However, what if you wanted the query to return something like this.

row1: test
row2: test2
row3: test3

well it turns out it can be done pretty simply. The first step is constructing your delimited list into a roughly formated xml document. You can then use standard xquery syntax to shred that document into its row by row element values. Below is the finished code example:

declare @TableList varchar(100)
set @TableList = 'test,test2,test3'
--show what xml looks like for example purpose only.
select Convert(xml,('<R>'+replace(@TableList,',','</R><R>')+'</R>')) as xmldoc

select t.value('.','varchar(200)')
    from (select convert(xml,('<R>'+replace(@TableList,',','</R><R>')+'</R>')) as xmldoc)  as a
    Cross apply a.xmldoc.nodes('./R') as b(t)

Database Scripting and powershell (Copying Backups)

This is the second part to my series of posts describing how I automated our Production –> Staging database restores. This part is still fairly early on in the process and was basically for me to hone my powershell skills , anyways onto the script:

Function GetBackupFromFilesystem
{
    param
    (
        [string]$Source,
        [string]$Destination 
    )
    #Debug Info
    #Write-host "destination:$destination\$i"
    #Write-host "source"= $source
    #####
    foreach( $i in (ls $source )){ }
    if ((Test-Path -path ($destination +"\"+$i.Name)) -eq $True)
    {
        Write-host '**********************************************************************************'
        write-host ("File:"+ $destination+ "\"+$i.Name +" already exists")
        Write-host '**********************************************************************************'
        return @( "$($i.name)")
        exit
    }
    ls $destination | where {$_.name -like "*.bak"} | rm -force
    Write-host "DestinationFilesystem"
    Write-Host $destination
    #write-host "$i.path"
    Copy-Item  ($source + '\' +$i.name)  ($Destination + '\' +$i.name)
    return @( $i.name)
}

 

The script is fairly simple. It takes the Source directory and the target directory. It then grabs the most recent file and copies that to the destination drive. However it only copies it if the file doesn’t already exist. It only uses a filename at this time to determine if the file already exists. Eventually ill do some additional checking but for now this satisfies our requirement. Also of note, It grabs the most recent file assuming you are using the standard backup naming convention for sql server 2008 r2. The standard convention appends the date to the backup filename so more recent backups end up at the bottom of the directory listing and hence the most recent backup will be the last file. If you do something different then you will have to change the logic of the “ foreach( $i in (ls $source )){ }” line to ensure your proper directory listing shows the most recent as last. The function will then return the filename to the calling procedure. This allows you to perform further operations on that file without having to get the name again.

There is a downside to this script however. There is no status output using the Copy-Item method. You get no feedback about the progress of the file copy and in many cases with large backups you are staring at a console that could be doing nothing or it could be copying the file. you simply don’t know.The following is a function that will give you a status output of how the file copy is going. It continually gives you the percentage of the file copy that has completed. I believe I lifted a good portion of this logic from somewhere though now the source escapes me.

function Copy-File {
    param( [string]$Source, [string]$Destination)
    $Sourcefile = [io.file]::OpenRead($Source)
    $Destinationfile = [io.file]::OpenWrite($Destination)
    Write-Progress -Activity "Copying file " -status "$Source -> $Destination" -PercentComplete 0
    try {
        [byte[]]$buffer = new-object byte[] 4096
        [Long]$total = [Long]$count = 0
        do {
            $count =  $Sourcefile.Read($buffer, 0, $buffer.Length)
            $Destination.Write($buffer, 0, $count)
            $total += $count
            if ($total % 1mb -eq 0) {
                Write-Progress -Activity "Copying file" -status "$Source -> $Destination" `
                   -PercentComplete ([int]($total/$Sourcefile.Length* 100))
            }
        } while ($count -gt 0)
    }
    finally {
        $Sourcefile.Close()
        $Destination.Close()
    }
}

There's a problem with this method however. If you are moving files locally its just as fast as the built-in copy-item. However if your copying from one network source to another network source as a build server might do then you incur a heavy IO cost. The file buffer must be filled locally then written out to the destination network file. This creates a network bottleneck. As such I don’t actually use this function in my scripts but have included it because it’s a handy piece of scripting and might have a use for your application.

Database scripting and powershell

So on a regular basis we find ourselves needing to restore customer data to our staging environment to test our releases on actual data. However doing this often requires a DBA to grab the most recent backup and manually restore it to the DB server. Once the restore is finished mirroring was configured and then our business intelligence staging environment was hooked up. If staging already had an old copy of production then the mirroring had be dissolved before the DB could be dropped or replaced. While this isn’t difficult its definitely time consuming. This will be the first part of a Series of blog post aimed at simplifying this process and, using powershell-foo, automate our process a little.

So now to the how of it. We have three actions we want to perform: Break a mirror and remove an existing database, Restore a new backup of a database, reconfigure mirroring.

Breaking our mirror

Before we can Delete our existing database or restore over it we need to break the mirror that exists. This is fairly straight forward.

Function DissolveMirror {
    Param(
        [parameter(Mandatory=$true)]
        [String[]]
        $Principle,
        [parameter(Mandatory=$true)]
        [String[]]
        $Mirror,
        [parameter(Mandatory=$true)]
        [String[]]
        $Witness,
        [parameter(Mandatory=$true)]
        [String[]]
        $DBName)
    [string]$instance = 'master'
    [string]$query
 #Create Handlers to show error messages that might occur.
    $SqlInfoMessageHandler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Host "$($_.Message)";}
    $Pconnection = new-object system.data.sqlclient.sqlconnection( `
        "Data Source=$principle;Initial Catalog=$instance;Integrated Security=SSPI;");
    $PConnection.add_InfoMessage($SqlInfoMessageHandler);
    $Pconnection.open()
    $Pcmd = $Pconnection.CreateCommand()
    
    $Mconnection = new-object system.data.sqlclient.sqlconnection( `
        "Data Source=$Mirror;Initial Catalog=$instance;Integrated Security=SSPI;");
    $MConnection.add_InfoMessage($SqlInfoMessageHandler);
    $Mconnection.open()
    $Mcmd = $Mconnection.CreateCommand()
    
    "*********************************"
    "Configuring Principle"
    "*********************************"
    $query = "ALTER DATABASE "+$DBName+"
                SET PARTNER off"
    $Pcmd.CommandText = $query
    $result = $Pcmd.ExecuteNonQuery()
    "*********************************"
    "Configuring Mirror"
    "*********************************"
    $query = "ALTER DATABASE "+$DBName+"
                SET PARTNER Off"
    $Mcmd.CommandText = $query
    $query = "restore database "+$DBName
    $Mcmd.CommandText = $query
    $result =$Mcmd.ExecuteNonQuery()
    "*********************************"
    "Mirroring Dissolved"
    "*********************************"
    $PConnection.Close() 
    $MConnection.Close() 
    }

Essentially, we are establishing a connection to each database. Then running the query needed to break the mirror on the target server based on that servers role in the quorum. For the primary this is simply setting the partner to off. On the mirror we need to pull the database out of its restoring state so we perform a restore database command to make it droppable. Once dissolved we close are connections and then we are done. The parameters for the function take the hostnames of the roles in your mirror and the database to be affected by the dissolve. Of course I haven't tested this without a Witness server but logic could easily be inserted to handle that specific case if needed. Handy And accomplishes the first step in our journey to automated restores!

Excel Files and SQL

It never seems to fail that a project will come about that will require someone to enter some data into a spreadsheet for some form of import in to your database. It also never seems to fail that no matter how many times you ask you end up with a xslx or some other form of office spreadsheet that isn't friendly with bulk insert. Sometimes you do get a csv but they created it oddly and the delimiters are wrong or not in the right places. This little script opens an excel sheet and converts it to a csv. Then reads each file in and line by line turns that into a select union script. We used this in aback end process to add data to users to our system.

function ConvertFrom-XLx {
  param ([parameter(             
        Mandatory=$true,
        ValueFromPipeline=$true,
        ValueFromPipelineByPropertyName=$true)]
        [string]$path ,
        [switch]$PassThru
        )

    begin { $objExcel = New-Object -ComObject Excel.Application }
        Process { 
                    if ((test-path $path) -and ( $path -match ".xl\w*$")) {
                        $path = (resolve-path -Path $path).path
                        $savePath = $path -replace ".xl\w*$",".csv"
                        $objworkbook=$objExcel.Workbooks.Open( $path)
                        $objworkbook.SaveAs($savePath,6) # 6 is the code for .CSV
                        $objworkbook.Close($false)
                        if ($PassThru) {Import-Csv -Path $savePath }
                        }
                    else {Write-Host "$path : not found"}
                }
    end  { $objExcel.Quit() }
}

dir *.xlsx | ConvertFrom-XLx
$command = 'ls *.csv'
$array =[string] @()
$array = ls -filter "*.csv"
$out = [string] @()
foreach ( $i in $array) {
   $Sql = [string] @()
   "processing file:" + $i
   $Count = 0
   $sql += "Select * from ( select null as Column1,null as Column2,null as Column3,null as Column4,null as Column5 "
   foreach ($a in Get-Content $i) {
            $sql +=" Union All"+ "`n"
            $count += 1
            $Values = "$a".split(',')
            $sql += "select" + "'"+$values[0] + "'"+ ","+ "'"+$values[1] + "'"+ ","+ "'"+$values[2] + "'"+ ","+ "'"+$values[3] + "'"+ ","+ "'"+$values[4] + "'" #+" Union All" + "`n`r"
    }
    $sql += ") q2 where Firstname is not null"
    $out = $sql
    "Sql for File:`n`r" + $out 
    $outfile = "$i".replace(".csv",".sql")
    $out | Out-File $outfile
}
}

Powershell Exception trapping

About three months ago I wrote a handy little powershell script utilizing a variety of functions and cmdlets to download a copy of our production data, restore it to a staging environment, cleanse PCI from the data and configure mirroring. At some point this script stopped working. Unfortunately the error messages regarding why it was not working were as follows:

Exception calling "KillDatabase" with "1" argument(s): "Kill database failed for Server 'DBServer.domain.com'. "
Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server
 'DBServer.domain.com'. "
At D:\scripts\StagingDBMigrate\DBActions.ps1:60 char:20
+     $res.SqlRestore <<<< ($srv)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Unfortunately, that doesn’t tell us a whole lot.This is the highest level exception and doesn’t give us much information. Only that the command failed. Here is a handy little piece of code that will help you find the innermost exception that caused your error and spit it out to the host

Trap {
  $error = $_.Exception
  while ( $error.InnerException )
    {
    $error = $error.InnerException
    write-output $error.Message
    };
    continue
  }

This piece of code will loop through the exception chain until it gets to what should be the root cause of the failure, printing the trace along the way. This turned our previously arcane error message into a cleanly understandable error that looks something like this

Exception calling "KillDatabase" with "1" argument(s): "Kill database failed for Server 'DBServer.domain.com'. "
System.Data.SqlClient.SqlError: RESTORE cannot operate on database 'Database' because it is configured for database mirroring. Use ALTER DATABASE to remove mirroring if you intend to restore the database.

EDIT:Providing a more complete example:

Function foo {
    #Define Trap at top. Any exceptions within this function will automatically call this block
    Trap {
            Write-Host 'Caught an exception!' -ForegroundColor Red
            $error = $_.Exception
            write-Host $error.Message -ForegroundColor Red
            continue
        }
        $exception = [System.IO.IOException] "Dude, there's no file there!";

        Write-Host 'Before exception'
        throw $exception
        Write-Host 'After Exception'    
        }
#Call our Exception handling function! 
foo