Post reply

Executing a Stored Proceedure from another database???

  • April 12, 2005 at 12:35 am

    #67185

    Hi all,

    I have a operational system which for name sake uses a database called database1.  This database contains both tables and stored proceedures.

    There is also a 2nd database called database2 which only contains views of some of the tables contained in database1. 

    A 2nd operational system interfaces with the views in database2 which works fine, however, it requires to run 1 stored proceedure that is in database1.

    I know the easy solution is that I could create a copy of the stored proceedure in database2.  (whereby it updates the tables in database 1 via the views) But what I am trying to avoid is that if this proceedure is ever modified then it will have to be modified in two databases. 

    My question is whether it is possible to create a stored proceedure (or by some other method) which can be called by the application using database2 that executes the stored proceedure in database1???

    Is this possible?  The application can only access 1 database so it is not possible to use a different session connecting to database1 inorder to run the stored proceedure.

    Any assistance would be greatly appreciated. 

  • April 12, 2005 at 12:42 am

    #552030

    Try something like following, you can put them in proc as well.

    use database2

    go

    exec database1.dbo.proc1

  • April 12, 2005 at 9:28 am

    #552166

    Jeremy

    One extra point to add.  If your user is only in database2, then you will need to use cross db ownership chaining to allow the procedure from database2 to call the procedure in database1.

    Have a look in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_8d7m.asp for details - note this only works with SP3 though!

    HTH

  • April 12, 2005 at 6:31 pm

    #552269

    Thanks Guys, I thought the solution would be far more complex and overlooked the simple solution. 

    Anyway, I've got it working.  Thanks again.

  • April 12, 2005 at 11:47 pm

    #552293

    You can better add the user to the second database than enabling cross db ownershipp chaning. This is quite a drastic measure because it affects the whole server and thus all databases. From a security point of view, better add the user to the second database with restricted rights (just execute rights to the procedure).

  • April 12, 2005 at 11:55 pm

    #552296

    Yep thats what I did.  As you said I thought cross db ownership chaining was a bit drastic in this instance.

  • April 13, 2005 at 5:23 am

    #552363

    One also wonders why there is two db's when the second db only contains views of objects in the first db...?

    Better (but I'm sure for one reason or other not possible) solution would perhaps be to scrap db 2 entirely and keep all in a single db instead.

    /Kenneth

  • April 14, 2005 at 8:00 pm

    #552837

    Because the application that uses the 2nd db creates 40 tables per user.   As this is a secondary application which our team does not have any role in supporting other than providing connectivity we did not want all these tables cluttering up the primary database that we are responsible for.

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

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