I have looked all around and I am having no luck trying to figure out how to alter a stored procedure within an asp.net application.
Here is a short snippet of my code, but it keeps erroring out on me.
Try
myCommand.CommandText = "Using " & DatabaseName & vbNewLine & Me.txtStoredProcedures.Text
myCommand.ExecuteNonQuery()
myTran.Commit()
Catch ex As Exception
myTran.Rollback()
Response.Write(ex.ToString())
End Try
The reason for this is because I have to propagate stored procedures across many databases and was hoping to write an application for it.
Basically the database name is coming from a loop statement and I just want to keep on going through all the databases that I have chosen and have the stored procedure updated (altered) automatically
So i thought the code above was close, but it keeps catching on me. Anybody's help would be greatly appreciated!!!
This is one of the things that make stored procedures a maintenance nightmare.
It should be "USE", not "USING". It may be an idea to use separate connections or at least to execute the USE statement separately.
|||
Well, I changed it to Use (I should have seen that already) and still got nothing. I did try to do one stored proc at a time, but it kept catching on me. Any other ideas?
Why not just run the stored procedure create/update within Query Analyser / SQL Server Management Studio?
