Powershell SMO works in cmd, not from script

  • joseph.uher

    Old Hand

    Points: 304

    Hope this is a correct forum.

    My code (Server 2003 Enterprise, SQL Server 10.0.5512.0, Powershell v1.0 ISE) is as follows:

    function Script-Database_01 {

    # Created: 09/06/2013

    # Edited: 09/06/2013

    [CmdletBinding()]

    param(

    [Parameter(

    Mandatory=$true,

    ValueFromPipeline=$true,

    HelpMessage="Local directory to save build-scripts output.")]

    [Alias('fp')]

    [string[]] $Filepath,

    [Parameter(

    Mandatory=$true,

    ValueFromPipeline=$true,

    HelpMessage ="Server or instance name.")]

    [Alias('ds')]

    [string[]] $DataSource,

    [Parameter(

    Mandatory=$true,

    ValueFromPipeline=$true,

    HelpMessage="The database to be scripted.")]

    #[Alias('db')]

    [string[]] $DatabaseName

    )

    BEGIN{

    Write-Output $Filepath

    Write-Output $DataSource

    Write-Output $DatabaseName

    Get-PSSession

    }

    PROCESS{

    # Set 'Option Explicit' to catch subtle errors:

    set-psdebug -strict

    #Load SMO assembly, and SMOExtended and SQLWMIManagement libraries:

    $ms = "Microsoft.SqlServer"

    $v = [System.Reflection.Assembly]::LoadWithPartialName("$ms.SMO")

    [System.Reflection.Assembly]::LoadWIthPartialName("$ms.SMOExtended") | out-null

    $my = "$ms.Management.SMO"

    $source = new-object("$my.Server") $DataSource

    if ($Source.Version -eq $null) {Throw "Cant find the instance $DataSource."}

    $db = $source.Databases[$DatabaseName]

    if ($db.name -ne $DatabaseName) {Throw "Cant find the database '$DatabaseName' in $DataSource."}

    $transfer = new-object("$my.Transfer") $db

    $transfer.Options.ScriptBatchTerminator = $true # This only goes to file

    $transfer.Options.ToFileOnly = $true # This only goes to file

    $transfer.Options.Filename = "$($Filepath)\$($DatabaseName)_Build_01b.sql"

    # Do it:

    $transfer.ScriptTransfer()

    }

    END{}

    }

    When I execute everything in the PROCESS{} section, the commands succeed, and the file is correctly output. When I run the script (as .\Script-Database_01.psq [+parameters] I get the follow error:

    New-Object : Cannot find an overload for "Server" and the argument count: "2".

    At C:\Powershell\Scripts\Script-Database_01.ps1:44 char:29

    + $source = new-object <<<< ("$my.Server") $DataSource

    + CategoryInfo : InvalidOperation: (:) [New-Object], MethodException

    + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

    I assume this has something to do with the import of SMOExtended, but I can't see it.

    Can anyone point me in the "write" direction?

  • joseph.uher

    Old Hand

    Points: 304

    Well, I guess I'm an idiot! My script file is not a script, but a function. I loaded the script file into memory, and then called the function, and it works fine -- just like the command line execution! Problem solved. Thanks to Don Jones / Jeffery Hicks and "Learn Powershell Toolmaking in a month of lunches" (p. 36)

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply