question

dschaeff avatar image
dschaeff asked

How can I specify a stored procedure schema for the SMO StoredProcedures collection?

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
powershellsmo
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
dschaeff avatar image
dschaeff answered
use Item $SMOProc = $SMOProcs.Item("getaline","dbo")
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

yes, wrapping the schema and object name in quotes meant that it was loking for an object that doesnt exist.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.