February 18, 2004 at 8:19 am
I have been having problems with altering a stored proc via DMO. Here is the code that I use.
For i = 1 To oSPs.Count
If oSPs.Item(i).Name = storedprocedure Then
Set oSP = oSPs(i)
oSP.Alter (txtSps.Text)
End If
Next i
The variable 'storedprocedure' is passed to the function and is the name of the selected stored proc. 'txtSps.text' is a vb textbox.
The error I get is the following:
[SQL-DMO]Stored procedure definition must include name and text( for Standard StoredProcedure) or libraryname(for Extended StoredProcedure).
Any ideas why?
February 18, 2004 at 6:34 pm
Did some quick testing, the text you supply in the alter must contain the existing name of the stored proc. This works (used .Net with option strict=false):
Dim oserver As SQLDMO.SQLServer
Dim odb As SQLDMO.Database
Dim osp As SQLDMO.StoredProcedure
oserver =
New SQLDMO.SQLServer
oserver.LoginSecure =
True
oserver.Connect(".")
odb = oserver.Databases.Item("Northwind")
For Each osp In odb.StoredProcedures
If osp.Name.ToLower = "custordersorders" Then
'uncomment the following to test, but it WILL overwrite the proc in Northwind!
'osp.Alter("alter proc custordersorders as set nocount on")
End If
Next
oserver.DisConnect()
oserver =
Nothing
April 1, 2004 at 2:12 am
Thanx Andy, works 100%
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply