Cannot find type [Microsoft.SqlServer.Management.Smo.Server] while running drop SQL DB in VSTS Powershell inline script task

Question

In my VSTS release when I run the following inline Powershell (Version 5.1) script

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server(".") $db = New-Object Microsoft.SqlServer.Management.Smo.Database($srv, "MyDB") $db.Drop()

I get the following error

##[error]new-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is loaded.

From the above error, It is clear it can not find the 'SMO Server' and I have found some solutions how to add SMO server in windows environment but don't really know who to deal with that scenario in my VSTS Powershell VSTS task? As I am a newbie in Powershell world so not even sure if the script I am running is the right approach or if there is any other way of doing the job? I will appreciate if someone can help me with the inline script option rather the script file.

Answer 1

First, with this code: new-Object Microsoft.SqlServer.Management.Smo.Server("."), it uses windows authentication, so it uses the agent service account, make sure the agent service account can connect to the database (by default, it is Network Service if the agent is running as service)

I recommend that you can use SQL authentication and the code like this:

$assemblylist =   
"Microsoft.SqlServer.Management.Common",  
"Microsoft.SqlServer.Smo",  
"Microsoft.SqlServer.Dmf ",  
"Microsoft.SqlServer.Instapi ",  
"Microsoft.SqlServer.SqlWmiManagement ",  
"Microsoft.SqlServer.ConnectionInfo ",  
"Microsoft.SqlServer.SmoExtended ",  
"Microsoft.SqlServer.SqlTDiagM ",  
"Microsoft.SqlServer.SString ",  
"Microsoft.SqlServer.Management.RegisteredServers ",  
"Microsoft.SqlServer.Management.Sdk.Sfc ",  
"Microsoft.SqlServer.SqlEnum ",  
"Microsoft.SqlServer.RegSvrEnum ",  
"Microsoft.SqlServer.WmiEnum ",  
"Microsoft.SqlServer.ServiceBrokerEnum ",  
"Microsoft.SqlServer.ConnectionInfoExtended ",  
"Microsoft.SqlServer.Management.Collector ",  
"Microsoft.SqlServer.Management.CollectorEnum",  
"Microsoft.SqlServer.Management.Dac",  
"Microsoft.SqlServer.Management.DacEnum",  
"Microsoft.SqlServer.Management.Utility",
    "Microsoft.SqlServer.Management.Smo"
foreach ($asm in $assemblylist)  
{  
    $asm = [Reflection.Assembly]::LoadWithPartialName($asm)  
}  
    $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection("{sql server instance name}","{sql user name}","{password}")

$srv = new-object Microsoft.SqlServer.Management.Smo.Server($conn)

Write-Host $srv.Information.Version

    $db = New-Object Microsoft.SqlServer.Management.Smo.Database($srv, "{database name}") 
    $srv.KillAllProcesses($db)
    $srv.databases[$db.Name].drop()