How to create a proc or view on a different database

• zake

  SSC Enthusiast

  Points: 158

  More actions

  May 16, 2007 at 9:29 am

  #95214

  Hello,

  Does anyone know how to go about creating a view on a different database (same or diff server)?  I can drop the view, but can not create it on a diff database.  Execute will not work since the CREATE needs to be the first statement (so the use clause will not work).

  The same goes for a stored procedure.  Trying to create it on a different database.

  Does anyone have any information on how to accomplish this?

  Thank you.

• jezemine

  SSCrazy Eights

  Points: 8059

  More actions

  May 17, 2007 at 11:53 pm

  #707312

  do you mean create a view in database A that queries from database B?  if they are on the same server it's easy, just use 3 part names:

  create view dbo.MyView as select blah from B.dbo.tableInB

  if they A and B are on different servers, then you need to create a linked server on serverA to serverB, and then use 4 part names:

  create view dbo.MyView as select blah from ServerB.B.dbo.tableInB

  ---------------------------------------
  elsasoft.org