Accessing data from another database in stored procedure

sql sql-server stored-procedures dynamic-data synonym
Durgesh Chaudhary picture Durgesh Chaudhary · Feb 18, 2014 · Viewed 19.6k times · Source

Following is my schema detail:

  • DB_A : schema_1, schema_2, schema_3
  • DB_B : schema_3

some procedures in schema_3 access resources(table, view, sp) from schema_1 and schema_2.

All procedures in schema_3 are same on both the dbs. How do I access schema_1 from schema_3 for both the dbs.

Now I can hard code DB_A in my procedures but when I move code to client machine, it will create a problem since DB_A may not be same(one of the reason being client is miser and having QA, Dev and Prod on same machine).

Second option is getting DB_A name as a parameter, but it will make all the schema_3 SPs dynamic (as I did not get any method to access something like @DBName.schema_name.ResourceName).

Third option is creating linked servers, which again do not solve my problem because of same reason as first.

Any idea how to proceed, where I do not want my procedures to be dynamic just because 80% of them are straight.

Edit Start:

So I can restate it as I have multiple databases with a database having resources (table/view/schema) which needs to be shared and then having other databases (one or more) which have stored procedures which computes on data from shared database and self database.

Shared database name is not going to be constant on all the environments and I want to change them(environment specific). I have come out with a solution where I will be creating synonym for all the shared resources and all procedures will be using them, that way they are all referring to shared resources from first database.

For each installation I need to modify synonyms definition to reflect correct share database name. Is there any SYNONYM For Database Name, that way I will have way less synonyms to handle.

Answer

Durgesh Chaudhary picture Durgesh Chaudhary · Feb 21, 2014

Well the best choice I found is as follows.

Create Synonym (independent database DB_B) for individual objects (in shared database DB_A) with same name in same schema. That way your existing procedures need not change, and will work as required. Synonym gives a good reference on this. I will soon be creating an app to ease creating synonyms for these kind of situations.

CREATE SYNONYM DB_B.schema_1.proc_1 FOR DB_A.schema_1.proc_1

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More