Month: August 2014

Drop Linked Server from All Servers

Quick script on how to drop a linked server from a list of servers.  Minimally tested, use at your own risk.

 $LinkedServerName = 'LinkedServerToFindAndDrop'

try{
    gc -Path c:\Servers.txt | %{

        $srvName = $_
	    $srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	    $srvConn.ServerInstance = $srvName
	    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

        $linkedServer = $srv.LinkedServers | where{$_.DataSource -eq $LinkedServerName} 
        if($linkedServer -ne $null){
            for([int]$i = 0; $i -lt $linkedServer.LinkedServerLogins.Count; $i++){
                $login = $linkedServer.LinkedServerLogins.item($i);
                $login.Drop();
            }

            $linkedServer.Drop();
        }
    }

}
catch{
    $_ | fl -Force
}

Scientific Notation and the ISNUMERIC function

Fun problem at work today.  Someone was importing some data, and the value 2d4 kept evaluating as 1 using the ISNUMERIC function.  The reason why this evaluates true, is because 2d4 is technically a number using scientific notation.  The ‘d’ stands for double-precision.  2e4 would also return 1 (exponential value), as would –12, +12, or $12.

In order to correctly check to see if the characters are numeric (in this case, the first 3 characters of a string) you could use this:

DECLARE @Test NVARCHAR(100) = '2D4jlkafkjl'

SELECT 
	CASE
	WHEN LEFT(@Test,3) LIKE '[0-9][0-9][0-9]' THEN 
		CAST(LEFT(@Test, 3) AS INT)
ELSE
999
END

Alternatively, if you want to check to see if a string has any letters at all in it, you could just do this:

DECLARE @Test NVARCHAR(100) = '11i111'

SELECT 
	CASE
	WHEN @Test NOT LIKE '%[^0-9]%' THEN 'all numbers'
ELSE
'has letters'
END