Alter / Update a Stored Procedure from within ASP.NET

Question

Alter / Update a Stored Procedure from within ASP.NET

vinno369-590205

Grasshopper

Points: 14
More actions
December 24, 2007 at 1:59 pm

#201949

I want to alter a stored procedure from within my code base. I'm basically wanting to write an in house app that will update my stored procedures across many databases that we have. Any help would be appreciated. I'm having difficulty finding anything on this topic.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004448 More actions · Answer 1

I'm basically wanting to write an in house app that will update my stored procedures across many databases that we have.

What do you want to do to the stored procedures? In other words, we need some more detail... are all the stored procedures the same? What alterations do you want to make to the procs and how many databases do you have? What are the naming conventions for the procs across the databases... are they all named identically or what? Etc, etc...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 2

You can alter a stored procedure from a client by submitting an ALTER PROCEDURE statement. You can do this within a stored proc by using dynamic sql.

vinno369-590205 Grasshopper Points: 14 More actions · Answer 3

Yes, the stored procedure is named the same across roughly 200 databases. So a change to usp_RPT_AdmByAge in the test database will need to be propagated to about 200 databases after passing QA. I have already created an app that will generate a long script for me, but that still needs to be copied into Query Analyzer and ran. I'm trying to get it to run from asp.net. Here is some code, that I was hoping would work, but I kept catching an error on it.

Try

myCommand.CommandText = "Using " & DatabaseName & vbNewLine & Me.txtStoredProcedure.Text

myCommand.ExecuteNonQuery()

myTran.Commit()

Catch ex As Exception

myTran.Rollback()

Response.Write(ex.ToString())

End Try

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 4

What's the error? I think you want "use databasex go alter proc xxx ..."

Jeff Moden SSC Guru Points: 1004448 More actions · Answer 5

I know a lot of people hate the idea but this is where I'd say "enough is enough" and drop the proc into the Master database and be done with it. Is that an option?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jacob sebastian SSChampion Points: 11822 More actions · Answer 6

Jeff Moden (12/25/2007)
I know a lot of people hate the idea but this is where I'd say "enough is enough" and drop the proc into the Master database and be done with it. Is that an option?

Jeff, could you explain what will happen in the following case? (probably I am missing something).

Assume that we have the following database (All having same schema but different data)

Sales1 (My sales data)

Sales2 (Jeff's sales data)

Sales3 (Steve's sales data)

Assume that there is a stored procedure in each database 'GetSalesData'.

is there a way to put this procedure in the "master" database, and make this work for the three databases (without using a dynamic query). For example, How do I retrieve the sales data of database "Sales2"?

If this does not work, then the next best option might be to execute a query like "USE #dbname# GO; ALTER ...."

.

vinno369 Newbie Points: 7 More actions · Answer 7

Jeff, unfortunately dropping the stored procedure in to Master is not an option for me.

Jeff Moden SSC Guru Points: 1004448 More actions · Answer 8

jacob sebastian (12/25/2007)
Jeff Moden (12/25/2007)
I know a lot of people hate the idea but this is where I'd say "enough is enough" and drop the proc into the Master database and be done with it. Is that an option?
Jeff, could you explain what will happen in the following case? (probably I am missing something).
Assume that we have the following database (All having same schema but different data)
Sales1 (My sales data)
Sales2 (Jeff's sales data)
Sales3 (Steve's sales data)
Assume that there is a stored procedure in each database 'GetSalesData'.
is there a way to put this procedure in the "master" database, and make this work for the three databases (without using a dynamic query). For example, How do I retrieve the sales data of database "Sales2"?
If this does not work, then the next best option might be to execute a query like "USE #dbname# GO; ALTER ...."

Stored procedures that begin with "sp_" are first looked for in the Master database. If you look at such stored procedures as sp_SpaceUsed, they do not refer to any database in particular... they are simply used as a source of code to be executed.

If all of the required user databases have the same tablenames and the tables have the same schema, then whatever the current database is is what the code will execute against without the 3 part naming convention.

Keep in mind all the possible problems that you can have with created "sp_" procedures in Master... if you name a proc with little care, Microsoft could end up overwriting it with a service pack or hot fix. Best to have some sort of naming convention such as "sp_XXX_" where "XXX" are the initials of you company or some such. Of course, the other disadvantage is that MS could just erase everything that's not theirs during some upgrade. Certainly, you must have excellent source control in case those things do happen so that you don't loose code.

Also... If you need to combine the information from, say, 3 different databases, your best best would probably be to create a view that selects from each table in each database using a Union All. Might even lead to a nasty fast "partitioned view" (too long to explain here, see Books Online). This type of view, partitioned or not, does have a limit on the number of Union Alls it can contain... I forget what the limit is but it's something around 256 in SQL Server 2000.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004448 More actions · Answer 9

vinno369 (12/26/2007)
Jeff, unfortunately dropping the stored procedure in to Master is not an option for me.

Then, unfortunately, you're gonna need some form of loop and some dynamic SQL with a USE statement in it. If the databases are similarly named, you can get the names from the Master.dbo.SysDatabases table using LIKE with a pattern. The dynamic SQL would contain a USE statement followed by your ALTER statement.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Doug S. SSCrazy Points: 2801 More actions · Answer 10

Vinno, are you using the RedGate tools? They have a product called SQL Multi Script which would be worth a look. It has a 14 day free trial.

Let me know what you're final solution is...

Thanks

Doug