Database Script Issues With Linked Server
I have a problem with our database scripts. We have three environments: DEV, QA and UAT. We have two databases:
DB2. On DEV/QA those two databases are on different physical servers hence we have a linked server to
DB1. However on UAT it is a single server with both databases.
Our issue is that the scripts we write involve referring the
DB2 using the
DB2 linked server, which fails in UAT. The process we adopt is to use manual ways to deploy to UAT, which is not efficient.
Can someone suggest what can be done to better this?
You can create a linked server in your UAT environment's database referencing itself, with the same name as the linked server in your DEV/QA environment.
Suppose the server name of the UAT's database is
MYUATSRVNAME (use the output of
SELECT @@SERVERNAME). You can create a self-referencing linked server with the same name as the linked server name used in your DEV/QA's
DB1. Suppose that name is
UVWXYZ, on your UAT database execute
EXEC sp_addlinkedserver @server='UVWXYZ',@srvproduct='',@provider='SQLNCLI',@datasrc='MYUATSRVNAME';
That way you can use the same scripts referencing the same linked server name. Eg this query should then work in
DB1 on both DEV/QA and UAT:
SELECT * FROM [UVWXYZ].[DB2].[your_schema].[your_table];
- → How to make Laravel use my class instead of native PDO?
- → SQL: simple custom column in select doesn't work?
- → How to execute Stored Procedure from Laravel
- → Which database engine type should be specified for Microsoft SQL Database in Laravel?
- → How to troubleshoot PDOException?
- → laravel sql server stored procedure output
- → Issue with converting a date using Carbon
- → SQL microsoft query to Laravel 4.2
- → General error 20018 Cannot Continue the file execution because the session is in the Kill state
- → List names of all available MS SQL databases on server using python
- → Variable which replace DB of name in SSMS
- → Java: database connection. Where is my mistake?
- → How Can I use "Date" Datatype in sql server?