Powershell SMO works in cmd, not from script

• joseph.uher

  SSC-Addicted

  Points: 444

  More actions

  September 9, 2013 at 8:19 am

  #104213

  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

  SSC-Addicted

  Points: 444

  More actions

  September 10, 2013 at 6:05 am

  #1648829

  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)