CODEWITHSUNDEEP
sql-server
MVC
MVC

Reputation: 679

Is there any way to fix hard coded databases name within my view in SQL Server?

I have a view where I have currently used hard coded database names. But the problem with this approach is that for each and every server environment environment, I have to change the database name because the database names are not consistent across all the servers.

I am using .dacpac to deploy the database from one server to another but the problem is same the database names are inconsistent.

e.g. for some server database name is PatientDemographics_Migration but on some servers the name is PatientDemographics_Migration_P and the same applies to other database name used here in the script.

Is there anyway to fix this problem? I do not want to provide database name for every environment.

CREATE VIEW [dbo].[vw_migration_Demographics] 
AS 
    SELECT DISTINCT 
        patdemo.NHI AS [NHI],
        demo.FamilyName AS [Surname],
        demo.FirstName AS [FirstName],
        demo.Prefix AS [Title],
        demo.Gender AS [Gender]
    FROM 
        [PatientDemographics_Migration].[dbo].[PatientDemographics] demo
    INNER JOIN 
        [Encounter_Migration].[dbo].PatientEncounter pe ON pe.NHI = demo.nhi

Upvotes: 0

Views: 568

Answers (2)

Thom A
Thom A

Reputation: 95544

There is only one real solution here, as has been mentioned in the other answer: "fix" the names of the databases across your servers, make them consistently named. That means changing the name of the databases across the servers to be the same. Based on your question, that would be PatientDemographics_Migration and Encounter_Migration.

You can rename a database as detailed in the documentation:

USE master;  
GO  
ALTER DATABASE MyTestDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE MyTestDatabase MODIFY NAME = MyTestDatabaseCopy ;
GO  
ALTER DATABASE MyTestDatabaseCopy SET MULTI_USER
GO

Obviously replace MyTestDatabase and MyTestDatabaseCopy with the appropriate names.

Of course, as well, renaming your databases may break other code if you have used 3 part naming else where, so you will need to fix the definitions of those objects too, after you rename the database.

As I mention in my comment under the question, VIEW objects cannot dynamically reference objects. That have to be well defined, as reference specific objects, and can only contain a SELECT statement (CTEs are allowed, as these are expressions).

They can, however, use Synonyms, which can be helpful in these circumstances. These Synonyms would still need to be created, and defined specifically for each instance, however, your VIEW's definition would then be static.

Firstly, you would create your SYNONYM:

USE {Database containing the VIEW};
GO
CREATE SYNONYM dbo.PatientDemographics FOR [PatientDemographics_Migration].[dbo].[PatientDemographics];
GO
CREATE SYNONYM dbo.PatientEncounter FOR [Encounter_Migration].[dbo].PatientEncounter;
GO

These synonyms would be different on every instance that has a different name for the database. So on one where you have the _P suffix it would be:

CREATE SYNONYM PatientDemographics FOR [PatientDemographics_Migration_P].[dbo].[PatientDemographics];

Then, once you've created the synonyms bespoke to the instance, in the database the VIEW is going to be created in, you can create the VIEW using a static definition, across all the instances:

CREATE VIEW [dbo].[vw_migration_Demographics] 
AS 
    SELECT DISTINCT 
        patdemo.NHI AS [NHI],
        demo.FamilyName AS [Surname],
        demo.FirstName AS [FirstName],
        demo.Prefix AS [Title],
        demo.Gender AS [Gender]
    FROM 
        [dbo].[PatientDemographics] demo --Notice no 3 part naming as it references the synonym in the same database
    INNER JOIN 
        [dbo].PatientEncounter pe ON pe.NHI = demo.nhi; --Notice no 3 part naming as it references the synonym in the same database

Upvotes: 2

betelgeuce
betelgeuce

Reputation: 837

The issue here is inconsistent database naming nomenclature. Follow the Principle Of Least Astonishment - In other words, the users should not be surprised, startled, or astonished by the system's behavior, or by the behavior of any of the system's components.

You should not be changing the database name in every environment. What you will end up is spending unnecessary time trying to code around this.

Standardize the naming of your databases and start using named instances if you need them for alternative environments etc. It should be an easy change to sell as the savings in time and effort will pay for themselves.

Upvotes: 1

Related Questions