Cannot get Stored Procedure parameters in SMO

Hi ,

I have tried it all and I cannot seem to get the stored Procedure parameters

StoredProcedure sp=new StoredProcedure (mActiveDatabase,spName);

foreach (StoredProcedureParameter spParam in sp.Parameters) {

string fldName = spParam.Name;

}

Now I can see in the Debug Visualizer the spName and other properties

however the parameter.count is 0 even though I know for a fact i have 3 parameters.

Why is not getting them Bug in SMO or AM I missing something.

Using reflection I get them!!

thanks in advance for your replies.

Answer this question

Cannot get Stored Procedure parameters in SMO

ImperfectTommy

Not a problem.

That is exacltly what I did. I took the images from SQL Server Management Studio.

Regards,
Joginder Nahil
www.starprint2000.com
Fabio Mattiussi
I modified procedure AfterSelect of smoBrowser.vb in the SMOBrowse sample program as shown below to dump the parameters of the selected stored procedure in the object treeview. It seems to work file:

00129 Private Sub AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs)

Handles objectTreeView.AfterSelect

00130 Dim oldCursor As Cursor = Me.Cursor

00131 Me.Cursor = Cursors.WaitCursor

00132

00133 Try

00134 Me.propertyGrid1.SelectedObject = e.Node.Tag

00135

00136 ' *********************************************

00137 ' * If the node clicked is a stored procedure

00138 ' * then debug its parameters

00139 ' *********************************************

00140 If TypeOf e.Node.Tag Is StoredProcedure Then

00141 Dim p As Parameter

00142 Dim sp As StoredProcedure

00143 sp = CType(e.Node.Tag, StoredProcedure)

00144

00145

00146 For Each p In sp.Parameters

00147 Debug.WriteLine(p.Name & " " & p.DataType.ToString)

00148 Next

00149

00150 End If

00151

00152 If TypeOf e.Node.Tag Is IScriptable Then

00153 Me.textBox1.Text = GetScriptText(CType(e.Node.Tag, IScriptable))

00154 Else

00155 Me.textBox1.Text = String.Empty

00156 End If

00157

00158 Catch ex As SmoException

00159 Dim emb As New ExceptionMessageBox(ex)

00160 emb.Show(Me)

00161 Finally

00162 Me.Cursor = oldCursor

00163 End Try

00164 End Sub

Regards,
Joginder Nahil
www.starprint2000.com
whytepaul

You are a star!!!

You are helping me so much thanks!

One question

To assign an image to the node in smoBrowser did you modify "CreateNode"

and depending on the item you assign the relative picture Is this how you did it

Thanks again
Cannot get Stored Procedure parameters in SMO

Answer this question