Post reply

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

  • 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

     

  • July 1, 2004 at 4:59 pm

    #513046

    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.

  • July 2, 2004 at 1:53 am

    #513082

    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

  • July 2, 2004 at 3:42 am

    #513094

    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

  • July 2, 2004 at 3:44 am

    #513095

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

    Julian Kuiters
    juliankuiters.id.au

  • July 2, 2004 at 4:46 am

    #513110

    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

  • July 2, 2004 at 5:12 am

    #513115

    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.

  • July 2, 2004 at 5:52 am

    #513125

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

    on you guys!

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

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