Post reply

Connect across database

  • February 15, 2007 at 1:24 pm

    #122986

    Hi I am writing a stored procedure that reads from a table in one database and joins to another table in another database on the same server.  The database name may change i.e. dev, test, prd.  So I don't want to hard code the dtabase name in the stored procedure.  I also don't want to use a parameter with the name in it because i don't want to use dynamic sql.  I am going to create a view in the database that connects to the other db.  is there a better way to do this?

     

    Thanks

  • February 16, 2007 at 7:22 am

    #690209

    Based on my SQL Server 2000 experience, you either need to write dynamic SQL, which you don't want to do, or you need the view, which will have to be re-generated when you want to change which database it is connecting to.

    While there was something about synonyms in SQL Server 2000 they definately did not work like aliases in Oracle and I never found a use for them.

    It looks like they have improved synonyms in SQL Server 2005, but I haven't check them out, but look for them in Books On-line and you might find what you want.

    Brian

Viewing 2 posts - 1 through 1 (of 1 total)

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