Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Powershell SMO works in cmd, not from script Expand / Collapse
Author
Message
Posted Monday, September 09, 2013 8:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 07, 2014 5:53 AM
Points: 4, Visits: 28
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?
Post #1492803
Posted Tuesday, September 10, 2013 6:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 07, 2014 5:53 AM
Points: 4, Visits: 28
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)
Post #1493131
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse