Parameters hard coded Views

  • 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.

     

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

    --Vadim R.

  • 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

     

  • 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).

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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