store procs

say for example i have 6 databases on my server which are assigned for 6 different offices where they have to use it for their reporting tool so that they can add,update data in each db belonging to them. when they get into the reporting app they are going to select their database from their drop down list.

for this business I want to use all my procs for different db's but just one set of all these procs. If I have all these procs in each of the db then when I need to change anything in one of the proc there is a chance of missing the changes made in any of the db. So if i had just one set of procs which can be used for all the db's by this way I can not miss any updates made to any of the proc.

I just need a best solution to do this.


Thanks!
"He laughs best who laughs last"

http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx

_______________________________________________
Causing trouble since 1980
blog:http://weblogs.sqlteam.com/mladenp

One option is to have the sprocs in one database (might even be an additional db) and to use dynamic SQL to insert the proper database name in front of all the object references ...

CREATE PROC myProc (@dbName SYSNAME)
AS
EXEC ('SELECT t.* FROM [' + @dbName + '].dbo.MyTable t')
GO

Other than that, what's wrong with properly managing your databases?

Yet another option would be to have just one db, with an OfficeCode column added to each table, where you have views that filter on the OfficeCode for the current user. You then base all the sprocs on those views, rather than the tables. (And to tighten up security, you can add triggers to the tables to doublecheck on OfficeCode.)

Some reference tables might be left out of the filtering, to avoid confusion.

This means i need to update all my store procs so that the inserts,updates,deletes in the procs shud refer to the dynmic db. so a totoal rewrite of all the procs.

May i know wht are pros&cons for having one set of procs using it dynamically rather each set in different db's and monitoring them regularly for updates.

Thanks!
"He laughs best who laughs last"

Exactly. Looks like maintaining the sprocs in all the databases is the easiest option - just requires self-discipline.

If you dont mind...I would like to know dis-advantages of using dynamic proc to select the db.

Thanks!
"He laughs best who laughs last"

You mentioned it yourself - you'll have to go through all your procedures and change them into dynamic SQL. It will be much easier if you just implement the changes you make in your test database, into all production databases.

thanks, I understnad that except rewriting all the store procs as dynamic there is no other disadvantage like performance wise.

Thanks!
"He laughs best who laughs last"

Adriaan

about you 3rd solution having office code column added to each table..here i think all my data will be storing in single dataabse but my requirement is to have seperate db for each office.


Thanks!
"He laughs best who laughs last"

I wouldn't expect too much performance loss, as long as you use sp_ExecuteSQL with proper parameters, and apply all the recommended practices for queries (include the owner for each object you reference in the script: even if just 'dbo' ...).

Not too sure what it means for data caching ... you might still be better off with sprocs in each database.

Can i do something like..

in the begining of my each proc I want to have

CREATE PROC myProc (@dbName)
AS
Use @dbName
sql queries begin..
GO




Thanks!
"He laughs best who laughs last"

No, you most definitely cannot use "USE <db_name>" in a stored procedure.

You can use it in dynamic SQL, but you have to repeat it in each statement that you execute.

ok, so does it not work in that way are is that some thing voilating the rule.

Thanks!
"He laughs best who laughs last"

have you even read the link i posted?<br />It talks about exactly the thing you're looking for.<br /><br />i won't go into "best practices" discussions with this solution of course [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />_______________________________________________<br />Causing trouble since 1980<br />blog:<a target="_blank" href=http://weblogs.sqlteam.com/mladenp>http://weblogs.sqlteam.com/mladenp</a>

spirit..Actually i didnt follow tha link. can you please elaborate that.

Thanks!
"He laughs best who laughs last"

you ask for help and the don't even try to read what was provided?????
amazing...

_______________________________________________
Causing trouble since 1980
blog:http://weblogs.sqlteam.com/mladenp

wht I mean is i didnt understand the link you provided. how does that going to work for my scenario, can you explain if possible.

Thanks!
"He laughs best who laughs last"

The link that spirit1 provided is interesting.

One problem with the technique described there is that it uses an undocumented system sproc (master.dbo.sp_MS_upd_sysobj_category). This is red flag number 1 for production databases.

Number two is that this undocumented system sproc marks a non-system sproc as a system sproc. This is red flag number 2 for production databases.

The technique may be totally harmless, but you should not rely on it. And Microsoft is cleared of any responsibility if you use this type of technique, so you're on your own if it backfires.

Exactly thats where I was scared, I dont want to put my hands in master db.

Thanks!
"He laughs best who laughs last"

that is true and i admit its problems.
use it on your own risk.

What it does is puts all your stored procedures into master db and mark them as system objects.
because of the sp_ prefix they can be called from every database and they will run in that database context.

_______________________________________________
Causing trouble since 1980
blog:http://weblogs.sqlteam.com/mladenp

and no there is no elegant solutions for your problem.

either dynamic sql or every db has it's own set of sprocs.


_______________________________________________
Causing trouble since 1980
blog:http://weblogs.sqlteam.com/mladenp

But I agree that it would be nice if you could execute a sproc in the context of the database of your liking.

Then again it would only be useful for identical databases with different data on the same instance of SQL Server. Seeing that they didn't add this for SQL 2005, I doubt if they consider it a proper improvement.

If am putting procs in master db or having seperate set for each db , either way do I need to have seperate connections for each db from the app?

Thanks!
"He laughs best who laughs last"

no you don't have to, but this is up to whatyou need to do and how you're planning to do it.

_______________________________________________
Causing trouble since 1980
blog:http://weblogs.sqlteam.com/mladenp

I am planing to run reports for each database seperately with reporting app, for this how can the app connect to the particular db when db is selected by reporting app user without having seperateconnections from app for each db.

and finally as an admin I want to run final report from all the different db's together. How can i run full report of all the offices by single click. I have to provide a way for the user to connect to diferent db's and get the data needed to generate the report.



Thanks!
"He laughs best who laughs last"

have each db it's own connection string.

for the all databases report have a separate db that holds views that union data from all other databases.





_______________________________________________
Causing trouble since 1980
blog:http://weblogs.sqlteam.com/mladenp

so apart from all the other dbs do i need to have seperate db for a view which grabs data from all the other offices? or can i just create a view in one of them for that purpose.

Thanks!
"He laughs best who laughs last"

you can create a view in one of them of course.
i just thought that full separation would ease maintainance.

_______________________________________________
Causing trouble since 1980
blog:http://weblogs.sqlteam.com/mladenp