I have a number of related databases that are version-ed. Instance of different versions may run side by side, identified by their different versions, i.e. [NorhwindV1.1] and [NorhwindV1.2] may be on the same server, along with [SouthwindV1.1] and [SouthwindV1.2].
我有许多版本相关的相关数据库。不同版本的实例可以并排运行,由它们的不同版本标识,即[NorhwindV1.1]和[NorhwindV1.2]可以与[SouthwindV1.1]和[SouthwindV1.2]一起位于同一服务器上。
There are a number of stored procedures that make cross database queries, i.e. exist on NorthwindV1.1 and also query tables in SouthwindV1.1. What is the best way to manage the change in database names with changing version number, so that the stored procedures query the correct version of the databases?
有许多存储过程可以进行跨数据库查询,即存在于NorthwindV1.1上,也存在于SouthwindV1.1中的查询表中。通过更改版本号来管理数据库名称更改的最佳方法是什么,以便存储过程查询正确的数据库版本?
Thanks, MagicAndi.
3 个解决方案
#1
5
Assuming the number of tables queried between the databases is manageable, you could create synonyms for those tables and use the synonym in the procedures. You would then just need to change the synonyms to switch between versions.
假设在数据库之间查询的表的数量是可管理的,您可以为这些表创建同义词并在过程中使用同义词。然后,您只需要更改同义词以在版本之间切换。
#2
2
There are only two ways I know of, and both kinda suck. The first is to use dynamic SQL, like:
我只知道两种方式,两种方式都很糟糕。第一种是使用动态SQL,如:
declare @db varchar(512)
set @db = 'NorthwindV1.1'
declare @sql varchar(max)
set @sql = 'select * from ' + @db + '.dbo.YourTable'
exec (@sql)
The second is to install multiple instances if SQL Server on a machine. Like localhost\v1.0, localhost\v1.1, and so on. You can then create a linked server which you query like:
第二种是在机器上安装SQL Server的多个实例。与localhost \ v1.0,localhost \ v1.1等一样。然后,您可以创建一个链接服务器,您可以查询:
select * from linkedserver.northwind.dbo.YourTable
Now you can change the linkedserver to point at one of localhost\v1.0, localhost\v1.1 and the stored procedures will follow.
现在,您可以将linkedserver更改为指向localhost \ v1.0,localhost \ v1.1之一,然后存储过程将跟随。
I'll be watching this question to see if better suggestions pop up :)
我会看到这个问题,看看是否弹出更好的建议:)
#3
1
You could create views within each database for the other tables needed. The stored proc's would reference the views and the views would point to the "correct" database.
您可以在每个数据库中为所需的其他表创建视图。存储过程将引用视图,视图将指向“正确”的数据库。
You could probably even create a simple TSQL stored proc to generate the views when they need to be switched to a new database.
您甚至可以创建一个简单的TSQL存储过程,以便在需要切换到新数据库时生成视图。
#1
5
Assuming the number of tables queried between the databases is manageable, you could create synonyms for those tables and use the synonym in the procedures. You would then just need to change the synonyms to switch between versions.
假设在数据库之间查询的表的数量是可管理的,您可以为这些表创建同义词并在过程中使用同义词。然后,您只需要更改同义词以在版本之间切换。
#2
2
There are only two ways I know of, and both kinda suck. The first is to use dynamic SQL, like:
我只知道两种方式,两种方式都很糟糕。第一种是使用动态SQL,如:
declare @db varchar(512)
set @db = 'NorthwindV1.1'
declare @sql varchar(max)
set @sql = 'select * from ' + @db + '.dbo.YourTable'
exec (@sql)
The second is to install multiple instances if SQL Server on a machine. Like localhost\v1.0, localhost\v1.1, and so on. You can then create a linked server which you query like:
第二种是在机器上安装SQL Server的多个实例。与localhost \ v1.0,localhost \ v1.1等一样。然后,您可以创建一个链接服务器,您可以查询:
select * from linkedserver.northwind.dbo.YourTable
Now you can change the linkedserver to point at one of localhost\v1.0, localhost\v1.1 and the stored procedures will follow.
现在,您可以将linkedserver更改为指向localhost \ v1.0,localhost \ v1.1之一,然后存储过程将跟随。
I'll be watching this question to see if better suggestions pop up :)
我会看到这个问题,看看是否弹出更好的建议:)
#3
1
You could create views within each database for the other tables needed. The stored proc's would reference the views and the views would point to the "correct" database.
您可以在每个数据库中为所需的其他表创建视图。存储过程将引用视图,视图将指向“正确”的数据库。
You could probably even create a simple TSQL stored proc to generate the views when they need to be switched to a new database.
您甚至可以创建一个简单的TSQL存储过程,以便在需要切换到新数据库时生成视图。