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 work in cmd, not from script Expand / Collapse
Author
Message
Posted Monday, September 9, 2013 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:07 AM
Points: 4, Visits: 41
This was originally posted (incorrectly) in the SMO/RDO/DMO forum. Sorry.

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 #1492812
Posted Tuesday, September 10, 2013 6:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:07 AM
Points: 4, Visits: 41
Script file does not contain a script, but a function. Loaded the script file into memory, and then called the function -- it works fine, just like execution at the command line. Thanks to D. Jones / J. Hicks "Learn Powershell Toolmaking in a month of lunches" (p. 36).
Post #1493133
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse