Problem with SQL Server

[MGMorden]

Ok, I know this isn't strictly programming, but I've got something that's stumping me and I figured this was as close as possible to the appropriate forum.

I'm using SQL Server 2005 - OS is Windows Server 2003 on the main box but I have several others running various OS's, but all have SQL Server 2005.

I have a need to create a number of views in 1 database that I will be using to export data to other systems (and in a few cases those systems will be live-linking to those views via Linked Servers).

Now, this server contains database names that correspond to the current year, as well as the future year. So we have 2009, and 2010. Naturally though eventually 2010 will roll back to become the current year, and 2011 will be created as the future year.

Modifying THAT behavior is not possible as it's required by a third party program which I cannot change. However, in creating my views, I will always need to retrieve SOME data from the current year's database, and some from the future year's database. Right now when I setup a view, I have to refer specifically to the 2009 database in order to pull information from it. That's fine for now, but when 2010 rolls around I don't want to go back through every single view changing which database it pulls from.

SO, what I'm looking for is a way in SQL server to create an alias for a database name. Have a "Current" and "Future" alias for example that I can reference in queries, and then when at the end of the year I can simply reassign those two aliases have have the scripts continue to hit whichever database is then appropriate.

I've found synonyms, but those appear to only apply to objects within a database and not a database itself. Any ideas on how to get this done?

Thanks.

 

[Markbnj]

Best I can come up with are these methods for aliasing a server, but I think maybe you can include the default database name as well. Anyway, have a look and see what you think:

http://www.mssqltips.com/tip.asp?tip=1620
http://www.sqlnewsgroups.net/g...server/topic24702.aspx

 

[Bulldog13]

Different schema's should do the job for you if I am understanding the problem correctly.

 

[KB]

Can you script the changes to the view? You could even create a scheduled job to run this script on the first day of the year.

DECLARE @Current varchar(25)
DECLARE @Future varchar(25)
DECLARE @Past varchar(25)

Set @Current = 'DB' + Convert(varchar(4), Year(GetDate()))
Set @Past = 'DB' + Convert(varchar(4), Year(GetDate()) - 1)
Set @Future = 'DB' + Convert(varchar(4), Year(GetDate()) + 1)

EXEC('ALTER VIEW [dbo].[TestView]
AS
SELECT OrderNumber, Type, UNID, addl, bbl, boiler
FROM ' + @Current + '.dbo.TestData')

 

[MGMorden]

Thanks for the suggestions. I think I'm going to do something pretty close to what you suggested KB. What I think I've consigned to do is add most of the views to the individual databases - since the future database simply becomes the current and a copy of it becomes the future again at the end of the year, they should always stay in place and not need to be recreated.. Then only in my reference/view database I'll create a stored procedure that takes the Current and Future DB names as paramaters, and programattically recreate synonyms to the actual views in the proper databases, and for the ones that need to merge views from multiple databases I'll alter a view in my "view" database.

Thanks again!