It is possible to call a stored procedure belonging to different database?

Question

It is possible to call a stored procedure belonging to different database?

szopdog

Old Hand

Points: 393
More actions
July 1, 2004 at 2:32 pm

#83261

Hi i hope you can help me out here.
I have a sql server with 2 databases on it (lets call them dbA and dbB). What i need to do is:
1 somehow get a value from a table on dbA
2 Depending on what the returned value is run a select statement on one of two tables in dbB

what i have done up until now is connect to dbA run a stored procedure to get my value then make
another totally separate connection to dbB run another stored procedure using my value and get the results.
That as you can imagine is terribly inefficient.
Thanks for any help you can provide

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

As long as the user account has access to dbA and dbB and has execute rights on both then you can use one connection and call either by 3 part name the SPs. Or as I have done call one SP to call the dbA one with OUTPUT parameters, then based on that have it decide the SP to call in dbB and return your resultset.

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 2

Easy!

Put Databasename.. before the table name... eg:

use dbB
GO
IF Exists (Select count(*) from dbA..TableA Where dbA..TableA.ID = 100)
Select * from TableB
Else
Select * from TableC

or example 2

Select * 
  from dbA..TableA as TA 
   LEFT OUTER JOIN dbB..TableB as TB 
      ON TA.ID = TB.FID

Julian Kuiters
juliankuiters.id.au

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 3

Best practice is to fully qualify your names:

owner.name in same database,

databse.owner.name for calling into another database.

iof: " ...from dbA..TableA... "

better: " ...from dbA.dbo.TableA... "´

/rockmoose

You must unlearn what You have learnt

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 4

true, but that does have security implications. using no owner name uses your current security context.

Julian Kuiters
juliankuiters.id.au

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 5

You still have the same security context if you use qualified names.

specifying owner.object does not override your current security context for that object.

Using qualified names will tell sql server exectly which object to look for and sql server will not have to work this out for itself, in some cases saving some time.

BOL - "cross-database permissions", "Using Ownership Chains".

/rockmoose

You must unlearn what You have learnt

Antares686 SSC Guru Points: 125444 More actions · Answer 6

The only probem with using the table itself is you will have to grant permissions on the table itself. Unless you can cotnrol with Cross-Database Ownership Chaining option. I suggest either use a view or as you originally stated the SP wth an output variable to push data bits back.

szopdog Old Hand Points: 393 More actions · Answer 7

Everyone: Thanks a lot for your input. I knew i could count

on you guys!