| Author |
Topic |
|
Nemisis
Starting Member
8 Posts |
 Posted - 2006-03-14 : 04:18:55
|
| Hi guys,Hope everyone is ok. My problem is that i have 50+ databases, each database is for different clients with their own data. These databases all have the same tables, views, functions and sprocs.I was wondering if it is possible to store all my views, stored procedures etc in one database, so i know everyone is using the same code. Then somehow when i execute the stored procedure, it will execute on the data in another database?? I know that i could build the SQL using EXEC statements, but wondered if there is another way??i.e. If database A stores all my objects (views, sprocs etc), can i run a stored procedure within database A, using the data in database B?? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-14 : 05:00:45
|
| From Database A, you can runSelect Columns from DatabaseB..tableDatabaseB..Sp 'Parameters'MadhivananFailing to plan is Planning to fail |
 |
|
|
Nemisis
Starting Member
8 Posts |
Posted - 2006-03-14 : 10:47:08
|
| I know that, but i want to use the data from databaseB, when executing DatabaseA.dbo.Sproc1 |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-14 : 10:52:04
|
| Try using the fully qualified name for all the tables |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-14 : 11:01:27
|
quote:
Originally posted by Nemisis
Hi guys,Hope everyone is ok. My problem is that i have 50+ databases, each database is for different clients with their own data. These databases all have the same tables, views, functions and sprocs.I was wondering if it is possible to store all my views, stored procedures etc in one database, so i know everyone is using the same code. Then somehow when i execute the stored procedure, it will execute on the data in another database?? I know that i could build the SQL using EXEC statements, but wondered if there is another way??i.e. If database A stores all my objects (views, sprocs etc), can i run a stored procedure within database A, using the data in database B??
Not without dynamic SQL unfortunately.. I am guessing that you only want to keep one version of each sproc/view etc..You can do it like:declare @servera varchar(50)set @servera = 'Pubs'exec('select * from ' + @servera + '.dbo.authors')but not like:select * from @servera.dbo.authors |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-14 : 11:06:51
|
| Which in turn will mean that the user has to have SELECT permissions on the tables in the other database, rather than just Execute permissions on the Sproc in the other database ...Kristen |
|
|
|
Nemisis
Starting Member
8 Posts |
Posted - 2006-03-15 : 09:12:07
|
| Thanks for all the input, i did wonder if Exec was the only way, but wanted to hear from the experts. lolThanks everyone for your help |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-15 : 09:15:17
|
| If you want to pass object names as parameter then Dynamic SQL is the only wayMadhivananFailing to plan is Planning to fail |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-03-15 : 09:21:04
|
| Have you considered migrating all the databases in to one single database? If all your clients have the exact same database-schema with different data only I would seriously consider creating a common database for all of them, and then distinguish the data from eachother by adding a CustomerID or something to each row. Must be a total pain in the neck when you find bugs or need to do changes in the database...this might be quite timeconsuming in the beginning but my guess is that it will pay off big time after a while...Just a thought.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
Execute Stored Procedure on other databases