Cross Server/database use by Stored Procs

[nicatt]

I have a stored proc that resides at a location named business_istestxc.dbo.A1 of a particular server. The proc in question pulls data from tables on A1 (mostly) but must also go out to business_test and pull data from tables B2 and C3. Since the proc resides on A1, the path to tables B2 and C3 are fully declared as business_test.dbo.B2 and business_test.dbo.C3 and all is good.

In the long involved process of bringing a large project up, the stored procs MUST be tested on a 'test' system database named salesmanship. Therefore the full paths will be salesmanship_istestxc.dbo.A1 and salesmanship_test.dbo.B2 and salesmanship_test.dbo.C2 so that each proc must be changed by hand to change the path. In addition, when they are placed 'back' on business they will again have to be changed.

Question: Is there a way to paramatize the paths so that they do not have to be changed manually?

In advance your help is gratefully received.

 

[nicatt]

Well we brainstormed and came up with a couple of possible ideas. On was to try RPC but I thought there has to be an easier way.

At this point we are going to try (select *) views. If we use views on the 'home' databse, the underlying tables can cross databases while the views keep the same name. Thus view A1, B2 or C3 on one server/database can refer to the table location anywhere on that server and the proc doesn't have to worry about where it is. All the proc does is use the view.

I will post if this doesn't work.

 

[jrhoffm]

Hi,
I have seen people use environment variables coded in the master copy of the stored proc file

select foo from NAME1DBXX.dbo.tablename A1,
where ...

and the use a and userid and/or script to change the variables the target database using sed on the script.

sed
FILE=/tmp/storeproc1.$$.sql

cat storeproc1.sql | sed -e 's/NAME1DBXX/${NAME1DB}' >
$FILE
isql -U -S $SERVER -i $FILE

rm /tmp/storeproc.$$.sql

and $NAME1DB is setup in the users .profile or configured in the make file to install the proc.

John Hoffmaster (jrhoffm)