June 12, 2019 at 3:45 pm
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.
June 12, 2019 at 4:35 pm
Stored procedure is the way to go, I think. Why two SQL Server licenses if it's the same server?
--Vadim R.
June 12, 2019 at 5:03 pm
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
June 12, 2019 at 6:04 pm
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
June 12, 2019 at 6:22 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply