Powershell SMO works in cmd, not from script

Question

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?

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

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

joseph.uher SSC-Addicted Points: 444 More actions · Answer 1

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)