January 19, 2012 at 8:55 am
We have a large project that is using many stored procedures. The project uses multiple database so a stored procedure may call tables in another database. The issue that one large database is updated daily and we need the stored procedures to be able to switch over and query tables in the another database once its been updated. Maybe this would help in explaining DB1 has stored procedures that access tables in DB2, after the upload DB1 stored procedures now have to access tables in DB3. We are using SQL 2008.
Thanks any help or ideas is appreciated.
January 19, 2012 at 8:57 am
jflynn-735049 (1/19/2012)
We have a large project that is using many stored procedures. The project uses multiple database so a stored procedure may call tables in another database. The issue that one large database is updated daily and we need the stored procedures to be able to switch over and query tables in the another database once its been updated. Maybe this would help in explaining DB1 has stored procedures that access tables in DB2, after the upload DB1 stored procedures now have to access tables in DB3. We are using SQL 2008.Thanks any help or ideas is appreciated.
Yes... why not?
Either you can build a dynamic sql statement on the fly to point to the desired target database or have both queries (against DB1 and DB2) coded in your storedproc and decide which one to use based on a system flag of sorts.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 19, 2012 at 9:07 am
The best way to do this is to fully qualify the table when you reference it in the stored proc. So databasename.schema.tablename or databasename..tablename if it is the default schema. Similarly, you can reference linked servers by doing linkedservername.database.schema.tablename.
SELECT *
FROM tablename a
INNER JOIN otherdatabase..tablename b
ON a.product_id = b.product_id
Get it?
Jared
CE - Microsoft
January 19, 2012 at 9:18 am
SQLKnowItAll (1/19/2012)
The best way to do this is to fully qualify the table when you reference it in the stored proc. So databasename.schema.tablename or databasename..tablename if it is the default schema. Similarly, you can reference linked servers by doing linkedservername.database.schema.tablename.
SELECT *FROM tablename a
INNER JOIN otherdatabase..tablename b
ON a.product_id = b.product_id
Get it?
Just one little thing, instead of otherdatabase..tablename you really should specify the schema as well like this:
otherdatabase.dbo.tablename
January 19, 2012 at 9:53 am
jflynn-735049 (1/19/2012)
We have a large project that is using many stored procedures. The project uses multiple database so a stored procedure may call tables in another database. The issue that one large database is updated daily and we need the stored procedures to be able to switch over and query tables in the another database once its been updated. Maybe this would help in explaining DB1 has stored procedures that access tables in DB2, after the upload DB1 stored procedures now have to access tables in DB3. We are using SQL 2008.Thanks any help or ideas is appreciated.
What do you mean switch automatically? Does this mean that this code:
create procedure myproc
@id int
as
select id, name
from db2.dbo.mytable
where id = @id
return
needs to automatically refer to DB3 after a change? As in:
create procedure myproc
@id int
as
select id, name
from db3.dbo.mytable
where id = @id
return
The stored procedure can't move to a new database if it's coded. You could use dynamic SQL, but what happens on the next load? Is there a DB4? Or does the proc go back to DB2?
Your question needs a little clarification. There are potentially ways to handle things, but it's not clear what you are asking.
January 19, 2012 at 10:01 am
Yes that is what I mean in DB1 it has the proc myProc.
I need the database to used to be either DB2 or DB3 after the data has been uploaded.
We know that is the best method of doing it but it is out of our control. I remember reading that you may be able to use an Alias for a database but can't seem to find anything on it now. I was thinking if the procs would do something like
Select id from aliasDB.dbo.mytable and that we would be able to change the database the alias points to after daily upload.
January 19, 2012 at 10:08 am
jflynn-735049 (1/19/2012)
Yes that is what I mean in DB1 it has the proc myProc.I need the database to used to be either DB2 or DB3 after the data has been uploaded.
We know that is the best method of doing it but it is out of our control. I remember reading that you may be able to use an Alias for a database but can't seem to find anything on it now. I was thinking if the procs would do something like
Select id from aliasDB.dbo.mytable and that we would be able to change the database the alias points to after daily upload.
You could store the database names in a table in a database (we create a dba database for these things) and use that in conjunction with dynamic SQL. Do you understand?
Jared
CE - Microsoft
January 19, 2012 at 10:35 am
You could use a local view or synonym, but that would be for objects, not all globally.
If these are stored procedures, why not do it the reverse way, have them in the db2 and db3 and refer back to db1 if needed? Then have the client connect to the correct DB?
January 19, 2012 at 10:50 am
yes... yes... yes... 😀 either way you need a way to know to which database to point which means that "load processes" have to set a flag in some place a.k.a. a configuration table.
Storedproc should always read from such a table and once it know where to point either build the statement on the fly or exec the proper statement - for the former you have to have the two versions coded and a simple "if" statement on the "configuration flag" to know which one to use.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 26, 2012 at 11:35 pm
Can you go into more detail, I don't thing I fully understand.
Thank you
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply