Using duplicated DB's for duped Websites

[abienz]

Hi there,

I've got a website that has been duplicated on a server into three different folders, and each of the websites uses a database to recal and store data. However I want each of the different websites to store data to different databases, also which are duplicated. The reason I want to do this is because I want one person to save some details under one website, but for it to not change any of the values under the other websites.

I've created the three sites no probs, and also created three databases no problem, and recalling data isn't a problem, I've checked it through and each each site takes it's data from the correct database.

However when I try and save data to the databases it always stores the data to the origional of the threee databasses regardless of the website used, this is because the stored procedures all point to the origional DB.
Is there a function I can run to change all the stored procedures to point to the correctDB that they are stored in, perhaps by using a rename function? or will I have to go through each stored procedure one by one and rename them all?

thanks in advance!

 

[aalmeida]

Actually you do not need to Rename the sprocs but you do need to go into the sproc code and change the db it is using to make sure it is using the correct db.
Also you need to verify through out the code if you statments like this:

Select Db1.dbo.Table1.Colum1, Db1.dbo.Table2.Colum2
FROM Db1.dbo.Table1 joint Db1.dbo.Table2 ON blah, blah

As you can see the Select statmente references a Db the same may be true for Delete, Insert and Update statementes AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[abienz]

That's what I said isn't it?

I've got loads of stored procedures though and to rename all the entries for the database for each stored procedure would take ages, is there an automated way of doing it?

 

[dbmsguy]

why do you need to specify the db in the sp's???? you should just refer to the table without the db qualifier and it will go to the db it is executed from.

fix it once and you would fix it forever. Paul

http://www.dbmsguy.com

 

[aalmeida]

Yeap! That's my point get read of all references to a DB so it would run only in the context of current DB AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[abienz]

Riiiiiigggght, gotcha!


That's the darn sp wizard for ya, it puts them in automatically

Cheers guys