Parameters hard coded Views

  • TRACEY-320982

    SSChampion

    Points: 13444

    One SQL Server has both databases

    Databases  DB1_QA   and   DB1_TEST

    Database   RP  has a view vw3

    vw3 consists of

    select * from DB1_TEST.dbo.Sometable

    Crystal Report calls the view select * from v3

    When the developers want to test QA they are having to change the view vw3

    select * from DB1_QA.dbo.Sometable

    I looked around and there are context sessions or use contexts, or RxSqlServerData features.

    Is there any way to "pass in a parameter to dynamically change the Databasename from DB1_QA to DB1_TEST

    I know you can do this with stored procedures and I could set up another database and make the databases DB1_QA become DB1

    That is a cost of two SQL Server Licenses.

    Any thoughts would be appreciated.

     

  • rVadim

    Hall of Fame

    Points: 3842

    Stored procedure is the way to go, I think. Why two SQL Server licenses if it's the same server?

  • TRACEY-320982

    SSChampion

    Points: 13444

    Yes I just created a sp as an example.

    If I keep views, I would need to put the DB1_QA   and   DB1_TEST onto a separate SQL Server.  Yes only 1 new SQL Server

     

  • Jeffrey Williams 3188

    SSC Guru

    Points: 87973

    TRACEY-320982 wrote:

    Yes I just created a sp as an example. If I keep views, I would need to put the DB1_QA   and   DB1_TEST onto a separate SQL Server.  Yes only 1 new SQL Server  

    If you have the resources available - you can install another instance on that same system.

    Have you considered using synonyms?  Changing the synonym for testing and then back for production would be much simpler than modifying the view every time.  However - that is still a change...

    You really need another instance and/or a separate test database for RP.  If you created another database as RP_Test - and used synonyms for accessing the objects in the other database(s) - you can then migrate the code as is to the other database (which has the same synonyms but directed to the correct database).

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • ScottPletcher

    SSC Guru

    Points: 97975

    If you're truly on SQL 2016+, as you said, you can use SESSION_CONTEXT, as below.

    It's easier if the tables have the exact same structure, but we could "fudge" around it if they didn't.

    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    GO
    CREATE VIEW dbo.dbs_vw
    AS
    SELECT *
    FROM DB1_QA.dbo.Sometable
    WHERE CAST(SESSION_CONTEXT(N'which_db') AS varchar(50)) = 'DB1_QA' OR
    CAST(SESSION_CONTEXT(N'which_db') AS varchar(50)) IS NULL
    UNION ALL
    SELECT *
    FROM DB1_TEST.dbo.Sometable
    WHERE CAST(SESSION_CONTEXT(N'which_db') AS varchar(50)) = 'DB1_TEST'
    GO

    EXEC sys.sp_set_session_context N'which_db', 'DB1_QA'
    SELECT * FROM dbo.vw3
    EXEC sys.sp_set_session_context N'which_db', 'DB1_TEST'
    SELECT * FROM dbo.vw3

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

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

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