Post reply

Cross Database Reference

  • August 18, 2011 at 7:43 am

    #255944

    I have two Dbs ABC and XYZ

    In XYZ DB i wrote 100's of stored procedures and functions in which i am using some queries which refer tables of

    ABC DB for eg: select firstname from ABC.dbo.tbl_Details;(this is a SP in XYZ DB)

    Now at the time of deployment in production Db or in future, Database name is different like ABC is now DEF. Now the

    problem comes:

    How can i change DB name in all places, consider sql server performance while answering

    I have so many solutions in place but confused which is the best soltuion which will not effect SQL Server

    performance and the code should not be hard coded.

    Solutions are:

    1. We can create Synonyms but synonyms are created at object(table) level so the number of synonyms created will be

    high & will be equal to number of tables

    Refer

    Using Synonyms:

    http://msdn.microsoft.com/en-us/library/ms190626(v=SQL.100).aspx

    Understanding Synonyms

    http://msdn.microsoft.com/en-us/library/ms187552(v=SQL.100).aspx

    2.Store the DB name in DB table and select that in variable in stored proc .(In case of DB name is changed, only DB

    table need to be updated and no impact on code).

    It will effect the sql Server performance.

    3. Code needs to be scripted ( SSMS -> Database ->Tasks -> Generate Script) and renamed (Find and replace) in the

    script.

    i guess this is not a best solution, might be there are some cases where this will not work

    4. Create a function like getParamValues ( Paramname) which will return the value for the corresponding Parameter

    Name passed.

    Call this function in the code.

    It will become hardcoded and it would have some performance hit

    5. Create Databse project but its a CLR based solution this also not suits with problem

    Refer:

    http://msdn.microsoft.com/en-us/library/bb386243(v=vs.80).aspx

    Please reply asap.

    Thanks in advance.

  • August 18, 2011 at 8:39 am

    #1370164

    Synonyms. It has problems, but less than the other options. I've tested this kind of thing, and synonyms worked best in my tests.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • August 18, 2011 at 11:01 pm

    #1370534

    I agree with Gus. Synonyms are the only way to go for something like this. With just a bit of dynamic SQL, you can "auto-magically" create them in about 2 heart beats.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 19, 2011 at 3:53 am

    #1370605

    I am using Generate script. Am i doing corerect

Viewing 4 posts - 1 through 3 (of 3 total)

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