0

I have a PowerShell script which works well when I run it on server with SQL Server default instance (MSSQLSERVER) but the same script fails on a server with a named instance (MSSQL$instance)

For the default instance (MSSQLSERVER)

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$service = Get-service -name 'MSSQLSERVER'
$status = $service.status
$CreateDB = "db-Test"

if ( $status -eq "Running" )
{ 
    'Success' | Out-File -FilePath c:\sqltest.log -Encoding ASCII

    $srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
    $db = New-Object Microsoft.SqlServer.Management.Smo.Database($srv, "$CreateDB")
    $db.Create()
    $db.CreateDate
}
else
{
    'Failed' | Out-File -FilePath c:\sqltest.log -Encoding ASCII
}

Above script works very well. But below script throws error :

For named instance :

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$service = Get-service -name 'MSSQL$instancename'
$status = $service.status
$CreateDB = "db-Test"

if ( $status -eq "Running" )
{ 
     'Success' | Out-File -FilePath c:\sqltest.log -Encoding ASCII

     $srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
     $db = New-Object Microsoft.SqlServer.Management.Smo.Database($srv, "$CreateDB")
     $db.Create()
     $db.CreateDate
}
else
{
     'Failed' | Out-File -FilePath c:\sqltest.log -Encoding ASCII
}

Above script for named SQL instance throws below error :

New-Object : Exception calling ".ctor" with "2" argument(s): "SetParent failed for Database 'Netmagic-Test'. "
At C:\mssql-test.ps1:11 char:7
+ $db = New-Object Microsoft.SqlServer.Management.Smo.Database($srv, "$CreateDB")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [New-Object], MethodInvocationException
+ FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

You cannot call a method on a null-valued expression.
At C:\mssql-test.ps1:12 char:1
+ $db.Create()
+ ~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Error Screen

Thanks,

Viral

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Viral
  • 21
  • 5
  • What happens when you change the instance name to `Get-service -name "MSSQL$instancename"` – M O'Connell Nov 09 '16 at 22:48
  • I run the script and it throws the error mentioned at the end of my question. – Viral Nov 09 '16 at 22:55
  • Is your SQL named Instance `local`? A few different methods are discussed [here](http://stackoverflow.com/questions/8048822/is-it-possible-to-create-a-database-in-sql-server-with-powershell) – M O'Connell Nov 09 '16 at 23:43
  • When you connect to a **named** instance, you must also use the **instance name** in your "server/instance name" - so you **cannot** use `(local)` as your server name - it has to be `(local)\INSTANCE` ..... – marc_s Nov 10 '16 at 06:04

0 Answers0