none
Problem with Scripter class in powershell

    Question

  • tweaked my wits on this one. can't figure it out!

    i'm trying to follow the steps at the bottom of this page http://www.mssqltips.com/tip.asp?tip=1842 

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')| out-null

    # Create an SMO connection to the instance
    $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2005_1"

    $dbs = $s.Databases
    $dbs["Northwind"].Script() | Out-File D:\PSScripts\NWind.SQL

    $scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

    $scrp.Options.AppendToFile = $True
    $scrp.Options.ClusteredIndexes = $True
    $scrp.Options.DriAll = $True
    $scrp.Options.ScriptDrops = $True
    $scrp.Options.IncludeIfNotExists = $True
    $scrp.Options.IncludeHeaders = $True
    $scrp.Options.ToFileOnly = $True
    $scrp.Options.Indexes = $True
    $scrp.Options.WithDependencies = $True

    $scrp.Options.FileName = "D:\PSScripts\NWind.SQL"
    $scrp.Script($dbs["Northwind"].Tables)



    I changed the instance, database, and file paths

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
    $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST" 

    $dbs=$s.Databases

    #$dbs["AdventureWorks"].Script() 

    $dbs["AdventureWorks"].Script() | Out-File C:\temp\myscript.SQL

     

    $scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)


    $scrp.Options.AppendToFile = $True
    $scrp.Options.ClusteredIndexes = $True
    $scrp.Options.DriAll = $True
    $scrp.Options.ScriptDrops = $True 
    $scrp.Options.IncludeHeaders = $True
    $scrp.Options.ToFileOnly = $True
    $scrp.Options.Indexes = $True
    $scrp.Options.WithDependencies = $True

    $scrp.Options.FileName = "C:\temp\myscript.SQL" 
    $scrp.Script($dbs["AdventureWorks"].Tables)

     








    it works except for the last line "$scrp.Script($dbs["AdventureWorks"].Tables) "
    which errors out:

    Multiple ambiguous overloads found for "Script: and the argument count: "1".
    At line:1 char:13
    + $scrp.Script <<<< ($dbs["AdventureWorks"].Tables)
            + CategoryInfo             : NotSpecified: (:) [], MethodException
            + FullyQualifiedErrorId   : MethodCountCouldNotFindBest



    Friday, February 26, 2010 8:43 PM

Answers

All replies

  • btw,

    Windows Server 2008 R2
    SQL Server 2008
    Powershell 2.0

    also have set powershell script execution to unrestricted



    thanks!
    Friday, February 26, 2010 8:45 PM
  • Hi,

    Scripter's script method does not take smocollection class as input($dbs["AdventureWorks"].Tables is TableCollection derived from smocollection class)
    as you can see at http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scripter.script.aspx
    Scripter's script method takes array of SqlSmoObject as input so you can convert the SmoCollection class to array using CopyTo of ICollection, and then use it as parameter.

    Regards,
    Alok Parmesh
    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    • Marked as answer by KJian_ Monday, March 08, 2010 9:42 AM
    Friday, February 26, 2010 10:01 PM
  • Hi Alok,
    I'm quite new in Powershell scripts. Could you please post an explicit command to "convert the SmoCollection class to array using CopyTo of ICollection, and then use it as parameter."
    Just as the solution of the problem above.

    Regards
    Adam
    Wednesday, March 03, 2010 4:48 PM
  • Hi Adam,

    Here is how you can get the scripts

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
    $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "."

    $db=$s.Databases["AdventureWorks"]

    $db.Script() | Out-File C:\temp\myscript1.SQL
     
    $scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)


    $scrp.Options.AppendToFile = $True
    $scrp.Options.ClusteredIndexes = $True
    $scrp.Options.DriAll = $True
    $scrp.Options.ScriptDrops = $True 
    $scrp.Options.IncludeHeaders = $True
    $scrp.Options.ToFileOnly = $True
    $scrp.Options.Indexes = $True
    $scrp.Options.WithDependencies = $True

    $scrp.Options.FileName = "C:\temp\myscript2.SQL" 

    foreach($item in $db.Tables)
    {
     $tablearray+=@($item)
    }

    $scrp.Script($tablearray)

    This will give you drop script.

    Following statement on the site mssqltips is wrong
    ScriptDrops - specifies whether to include the IF EXISTS..DROP statements

    As ScriptDrops is for the script operation generates a Transact-SQL script to remove the referenced component i.e DROP the objects.
    so if you want CREATE script you should set it false

    IncludeIfNotExists is the scripting option which will generate IF Exists statements.

    Regards,
    Alok Parmesh


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Friday, March 05, 2010 5:59 PM
  • Hi Alok,

    Thanks for the clarification. I will have the reference updated. My mistake on the typo for ScriptDrops property
    bass_player http://bassplayerdoc.blogspot.com/
    Saturday, March 13, 2010 10:54 PM
  • Hi Everybody,
    Alok's answer was not satisfying enough for me.
    I expected the use of copyto method.
    But OK.
    Could you please take a look at this script:

    param
    (
      [string] $ServerName,
      [string] $DatabaseName
    )

    [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
    $MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
    $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$ServerName"
    $db = $srv.Databases["$DatabaseName"]
    $MyScripter.Server=$srv
    $tbls = new-object array[] $db.tables.count
    $db.tables.copyto($tbls,0)
    $arr = new-object array[] $tbls.length
    [system.array]::copy($tbls, 0,$arr, 0, $tbls.length)
    $Myscripter.Script($arr)

    It produces the same error as above
    Multiple ambiguous overloads found for "Script: and the argument count: "1".
    Could you please explain what is wrong with it?

    Thanks in advance
    Regatds,
    Adam

    Monday, March 15, 2010 6:22 PM
  • Thank you Alok
    Tuesday, March 16, 2010 8:11 PM
  • Hi adamjz,

    THis scripts worked for me. The trick is to treat smoObjects as a Urn collection rather than an individual object. Then you simply populate the collection in the loop and Script everything at the end:

     

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

    $srv = new-Object Microsoft.SqlServer.Management.Smo.Server("servernameorip")
    $conContext = $srv.ConnectionContext
    $conContext.LoginSecure = $FALSE
    $conContext.Login = "sa"
    $conContext.Password = "sapassword"
    $db = New-Object Microsoft.SqlServer.Management.Smo.Database
    $db = $srv.Databases.Item("AdventureWorks2008")

    $scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv)
    $scrp.Options.NoFileGroup = $True
    $scrp.Options.AppendToFile = $False
    $scrp.Options.ClusteredIndexes = $False
    $scrp.Options.DriAll = $False
    $scrp.Options.ScriptDrops = $False
    $scrp.Options.IncludeHeaders = $True
    $scrp.Options.ToFileOnly = $True
    $scrp.Options.Indexes = $False
    $scrp.Options.WithDependencies = $False
    $scrp.Options.FileName = 'C:\TEMP\bch\AhorroTablas.SQL'

    $smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection

    Foreach ($tb in $db.Tables)
    {
       If ($tb.IsSystemObject -eq $FALSE)
       {
          $smoObjects.Add($tb.Urn)
       }
    }

    $sc = $scrp.Script($smoObjects)

    Hope this helps

    • Proposed as answer by amihanov Monday, August 30, 2010 3:59 PM
    Monday, August 30, 2010 3:58 PM