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

4
  • What happens when you change the instance name to Get-service -name "MSSQL$instancename" Commented Nov 9, 2016 at 22:48
  • I run the script and it throws the error mentioned at the end of my question. Commented Nov 9, 2016 at 22:55
  • Is your SQL named Instance local? A few different methods are discussed here Commented Nov 9, 2016 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 ..... Commented Nov 10, 2016 at 6:04

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.