question

How can I specify a stored procedure schema for the SMO StoredProcedures collection? The documentation indicates that you specify the schemaname following the procedure name; this is not working for me (using SQL 2008R2). For example, using powershell I can script a procedure in the dbo schema if I omit the schema name. I am unable to script a specific procedure in any other schema. [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null [System.Reflection.Assembly]::LoadWithPartialName("System.Data") | out-null [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | out-null $SourceServerName = "myserver" $SourceDatabase = "mydatabase" $SMOSourceServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "$SourceServerName" $SMOSourceServer.ConnectionContext.LoginSecure = $true $SMOSourceDatabase = $SMOSourceServer.Databases["$SourceDatabase"] $SMOProcs = $SMOSourceDatabase.StoredProcedures $Scripter = New-Object ("Microsoft.SqlServer.Management.Smo.Scripter") ($SMOSourceServer) #does not work $SMOProc = $SMOProcs["dbo.getaline"] $Script = $Scripter.Script($SMOProc) echo $Script #works $SMOProc = $SMOProcs["getaline"] $Script = $Scripter.Script($SMOProc) echo $Script