Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2003-09-29 : 15:41:44
|
| Hi,We have a oltp database and we create reporting database for each month. so every month one new database is created dynamically and tables are created and populated from oltp database based on date values. Now since we need to create procedure that will call tables in oltp databases we need to create SP in this reporting databases. since if we need to change the logic of any particular SP we need to modify it in all reporting databases.So we are thinking of putting these procedures in master database with name as sp_ so that all the reporting databases can call procedure in master database and we don't need to worry about multiple copies of same procedure.I read somewhere that it is not good practice to put objects in master databse--- i can understand that logic for tables(they can be big)..is there any reasoning that we should not create procedure in master database?Thanks--rubs |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-09-29 : 18:02:56
|
| Only that it is a system database and has different recovery procedures to other databases. Also what happens if a system SP happens to be created with the same name as one of yours.It complicates release procedures and copying databases around and may have permission implications.Note that a user SP named sp_ created in master doesn't act the same as a system SP. For the user SP it will take the version in the current database rather than the on e in master (at least it did last time I checked).If you change an SP why not run a script to alter it in all your reporting databases?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-09-29 : 22:36:13
|
| Why not just have another database called it "Reporting" and then reference your sp's with exec Reporting.dbo.sp_Report1 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-09-30 : 09:32:03
|
| I would guess because then the SP would run in the Reporting database rather than the calling database and would need to use dynamic sql to access the tables.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
procedure in master database