June 18, 2008 at 10:11 am
Hey all,
Long story short, I have an 'admin' database on all my SQL servers which contain over 50 or so objects to perform any and all DBA related tasks. I have a script I made to push objects appropriately between 2000 and 2005. I create all my procedures as procname80, or procname90 as I create them appropriately per version. I then have a wrapper procedure procname which determines which proc to use based on version. 2000 servers only get the 80 version and 2005 only get the 90 version. Great system, works perfect.
I want to implement something similar with my views ... but I am struggling to find a way to do so. What I was originally thinking of doing was something like this:
SELECT * FROM viewname80
WHERE LEFT(REPLACE(CAST(SERVERPROPERTY('PRODUCTVERSION') AS VARCHAR(10)),'.',''),2) = 80
UNION
SELECT * FROM viewname 90
WHERE LEFT(REPLACE(CAST(SERVERPROPERTY('PRODUCTVERSION') AS VARCHAR(10)),'.',''),2) = 90
Which works great as is, but when you attempt to create it as a view it fails because one of the two objects won't exist on the server.
So for now what I do is simply create the view as viewname and name the file differently so this allows me to push out the appropriate version of the view per version of SQL. It works, but I'd like I said, I'd like to use a wrapper just like I've done with my procedures.
Any thoughts on how to accomplish this?
Thanks
June 18, 2008 at 11:28 am
First, make that a UNION ALL or you are forcing a DISTINCT.
I am not that keen on using these wrapper procedures around procedures or views, but it is a neat idea and I cannot think of any pressing issues - except of course having calls to procedures that do not exist (that's creeps me out a bit).
Your wrapper VIEWS are going to need both base objects, but the objects do not necessarily need to do anything. In your CREATE VIEW scripts, you could check the version of the server and insert a stub if it is the wrong server version. So, you would end up with the 80 and 90 views in name, but only the appropriate one would actually return any data.
June 18, 2008 at 12:15 pm
Yeah I thought about that as well ... but not really a route I want to take ... was hoping there was something I was overlooking.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply