February 28, 2011 at 12:06 pm
Yeah that is essentially what I am trying to do. It needs to know it's the "c" or "t" version of the database, and then from there - know which version the schema is at, based on what is in it's version table. I thought I could get it to recognize that entry by using a wild card for the representative value of each, but it's giving me both of them instead.
When you say, once it determines which value it is - do something, that is where I am trying to get it to resolve to either "c000" or "t000", and so on as time and updates go on.
February 28, 2011 at 12:12 pm
Ah, OK, this is about what I suspected.
The % is used as a wildcard within a SQL statement using LIKE. That is NOT what you are doing at all.
Within the function, you are setting the variable to the literal string 'c%' or 't%', then using an equivalency check outside of the function. If the function could return say t001, t002, t003, c001, c002, c004, etc. and you only wanted to distinguish between the 't' and the 'c' part, then your if statements after you call the function would be:
If @myfunctionreturned LIKE 't%'
then do something for t001, t002, t003
If @myfunctionreturned LIKE 'c%'
then do something for c001, c002, c004
February 28, 2011 at 12:27 pm
OK - getting closer. Now - I need to keep the historical stuff in there, so the idea is to not hard code the version t000, t001, t002, c000, c001, c002 and so on. Now - to achieve this, I need a way to just down to the @iSchemaVersion part. Is that possible?
February 28, 2011 at 12:32 pm
Here's a thought.
1. Change the table to include a "database indicator" to hold the C or T values.
2. Make the version an integer.
3. Stop using the function and make it a JOIN.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 28, 2011 at 12:36 pm
It already has an indicator called "TheTextValue" where the row is the "SchemaVersion".
February 28, 2011 at 12:37 pm
Post what you have now and highlight the areas that you want to change. I'll take a look at it later if you don't have a response from someone else by then.
February 28, 2011 at 12:46 pm
Pam, thank you. I appreciate your's (and everyones) assistance. Here is the script's logic thus far...
USE <database name>
GO
--DROP and CREATE Function to determine the schema version.
IF EXISTS (SELECT 1 FROM sysobjects WHERE NAME = 'determine_current_schema_version' AND TYPE = 'FN')
DROP FUNCTION determine_current_schema_version
GO
CREATE FUNCTION determine_current_schema_version() RETURNS NVARCHAR(10)
BEGIN
DECLARE @v_curTextValue NVARCHAR(10)
SET @v_curTextValue = (SELECT TheTextValue FROM dbo.VERSION WHERE TheKey = 'SchemaVersion')
--Determine which database
IF @v_curTextValue IS LIKE 't%'
BEGIN
--Not certain what to do here.
END
ELSE IF @v_curTextValue IS LIKE 'c%'
BEGIN
--Not certain what to do here.
END
RETURN @v_curTextValue
END
GO
--CREATE variable to hold schema version
DECLARE @iSchemaVersion NVARCHAR(10)
SELECT @iSchemaVersion = dbo.determine_current_schema_version()
--SELECT '*'+@iSchemaVersion+'*' as CheckiSchemaVersion
The way each section will look is as follows (for either the "c" or "t" version of the database, but only one example is given here for the sake of space...)
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--*****************************************************VERSION 001**************************************
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IF @iSchemaVersion <> 't001'
BEGIN
--start updates here
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR DROP/CREATE/ALTER TABLE & COLUMN CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR INSERTING & UPDATING ROW ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR FOREIGN KEY RELATIONSHIP CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR DML TRIGGERS
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR UNIQUE INDEX CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
--update the schema version
UPDATE VERSION SET TheTextValue = 't001' WHERE TheKey = 'SchemaVersion'
END
--end updates here
The result I am looking to achieve here is to have the function's logic be able to determine which database is running the script, based on "TheTextValue" in the VERSION table (see early part of this thread for DDL - changes to data types may be needed to reflect that they are all nvarchars now).
If I can somehow get it to achieve this logic, I will have 1 script that can manage both sets of updates for both databases. The only updates that a given database will receive will be the updates intended for that Schema's version (dictated by the logic in the Function).
Many thanks again to you all!
March 1, 2011 at 4:04 pm
A few more questions for you to make sure I have this...
So your database can be of type "c" OR type "t"
Version can be 000, 001, 002, etc. this version will always be numeric?
You have this table:
CREATE TABLE [dbo].[VERSION](
[TheKey] [nvarchar](20) NOT NULL,
[TheValue] [numeric](10, 0) NOT NULL,
[TheTextValue] [nvarchar](255) NULL,
What is TheKey for? the c or t or for something else entirely?
How about TheValue? is that the actual number of the version or something else?
You need to make decisions in your script based on both the type and the version, yes? Will there ever be the possibility of another type? How is the versioning and the script going to be handled as your DBs continue to grow and change through the months and years?
Also, will there ever be a need to do something to ALL db's of type t (or c) regardless of version?
March 2, 2011 at 9:04 am
Pam Brisjar (3/1/2011)
A few more questions for you to make sure I have this...So your database can be of type "c" OR type "t"
Version can be 000, 001, 002, etc. this version will always be numeric?
Hi Pam! Yes - the 2 types of databases are either of type "c" or type "t". This will not change, nor will the Schema versioning. It is going to only grow numerically if necessary (but it the schema has to go beyond 999 changes, then it's time to run for the hills, as Gail Shaw would put it.
You have this table:
CREATE TABLE [dbo].[VERSION](
[TheKey] [nvarchar](20) NOT NULL,
[TheValue] [numeric](10, 0) NOT NULL,
[TheTextValue] [nvarchar](255) NULL,
The only difference here now is that I have changed the data type for TheValue to NVARCHAR(10).
What is TheKey for? the c or t or for something else entirely?
How about TheValue? is that the actual number of the version or something else?
TheKey, TheValue, and TheTextValue are just the column names for the records that are retained in the VERSION and VERSION_HISTORY table. What TheKey is for is the names of the records. For instance, DatabaseVersion, SoftwareVersion, SchemaVersion. Those are values in the rows of TheKey column in dbo.VERSION. The Trigger that is firing when this script runs is sending values over to the dbo.VERSION_TABLE in the form of these values. The problem is that it's being populated with both sets of updates. That's as close as I can get it to work since changing the data types to NVARCHAR from INT in the script.
You need to make decisions in your script based on both the type and the version, yes? Will there ever be the possibility of another type? How is the versioning and the script going to be handled as your DBs continue to grow and change through the months and years?
The decision for the function is driven the TheTextValue column, and the value in that record for where TheKey is equal to SchemaVersion. It then looks at that row, and under TheTextValue, looks for the "c" or the "t", which is then followed by 3 digits - which is why I was trying to use that WildCard value of "c%" or "t%".
Also, will there ever be a need to do something to ALL db's of type t (or c) regardless of version?
The answer to that is yes - there is the potential that an update will be needed for both, but more rare than common. When that happens - said updates will be placed in their specific section for that revision, and when the script is run against whichever database it gets run against, then that code will execute for it (based on those values in the VERSION table). That's basically it in a nutshell.
Once we get this thing finished - you are going to receive MAJOR kudos from me, in the form of this being shared for all time in the Scripts section.
Thanks so much Pam
March 3, 2011 at 1:54 pm
YES!!! Pam - I've got it! Thank you so much for the development of the logic. I was able to coax one of my developers to help me with the rest.
The following will allow you to have a single script for updating 2 databases with the same names, but different schemas/software/database versions...
VERSION Table
/****** Object: Table [dbo].[VERSION] Script Date: 03/03/2011 12:12:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VERSION](
[TheKey] [nvarchar](20) NOT NULL,
[TheValue] [nvarchar](10) NOT NULL,
[TheTextValue] [nvarchar](10) NOT NULL,
CONSTRAINT [PK_TheKey] PRIMARY KEY CLUSTERED
(
[TheKey] ASC,
[TheValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
VERSION_HISTORY Table
/****** Object: Table [dbo].[VERSION_HISTORY] Script Date: 03/02/2011 12:12:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VERSION_HISTORY](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TheKey] [nvarchar](20) NOT NULL,
[LastUpdate] [datetime] NOT NULL CONSTRAINT [DF_VERSION_HISTORY_LastUpdate] DEFAULT (getdate()),
[TheValue] [nvarchar](10) NOT NULL,
[TheTextValue] [nvarchar](10) NOT NULL,
CONSTRAINT [PK_ID4] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
VH-INSERT Trigger
--Creates a Trigger for the VERSION table so that upon an update, the VERSION_HISTORY table receives the info.
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[Trg_VH-INSERT]'))
DROP TRIGGER [dbo].[Trg_VH-INSERT]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Create Trigger
CREATE TRIGGER [dbo].[Trg_VH-INSERT]
ON [dbo].[VERSION]
AFTER UPDATE
AS
BEGIN
--Creates new VERSION_HISTORY table record when an UPDATE takes place on the VERSION table. ~RWY 2/23/2011
INSERT INTO dbo.VERSION_HISTORY ([TheKey],[TheValue],[TheTextValue])
SELECT TheKey, TheValue, TheTextValue
FROM inserted
WHERE TheTextValue IS NOT NULL
END
GO
We ended up modifying the logic just a little bit from my initial inquiry. The VERSION table on the "Cloud" database is housing both the Cloud's and the other database's version information. Thus the reason for the compound PK in the VERSION table on the Cloud database. The other database still will only house it's version information alone, so there is no reason for the compound PK on it's VERSION table. The VERSION_HISTORY on the "Cloud" database will also house the updates to both it's db, and the ones that are pushed down to the other db, but the VERSION_HISTORY table on the other db - again - will house only it's own updates to it's various versions.
This will be posted in the scripts section for general use, later on today. Here is the script:
UPDATE.sql
--The following script has the ability to update both the Cloud and Client versions of the
--database. Simply apply changes in the appropriate VERSION section for the specific
--database, and then execute it against that database. Thank you G. Russell for the logic rewrite.
-- ~RWY 03/03/2011.
USE 'Database Name'
GO
--DROP and CREATE Function to determine the schema version.
IF EXISTS (SELECT 1 FROM sysobjects WHERE NAME = 'determine_current_schema_version' AND TYPE = 'FN')
DROP FUNCTION determine_current_schema_version
GO
CREATE FUNCTION determine_current_schema_version() RETURNS NVARCHAR(10)
BEGIN
DECLARE @v_curTextValue NVARCHAR(10)
DECLARE @v_Count INT
SET @v_Count = (SELECT COUNT(*) FROM dbo.VERSION WHERE TheKey = 'SchemaVersion')
IF @v_Count = 1
BEGIN
SET @v_curTextValue = (SELECT TheTextValue FROM dbo.VERSION WHERE TheKey = 'SchemaVersion')
END
ELSE
BEGIN
SET @v_curTextValue = (SELECT TheTextValue FROM dbo.VERSION WHERE TheKey = 'SchemaVersion' AND TheValue=1)
END
RETURN @v_curTextValue
END
GO
--CREATE variable to hold schema version
DECLARE @iSchemaVersion NVARCHAR(10)
SELECT @iSchemaVersion = dbo.determine_current_schema_version()
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--*****************************************************VERSION 001**************************************
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IF CONVERT(INT, SUBSTRING(@iSchemaVersion,2,2000)) < 1 AND @iSchemaVersion NOT LIKE 'c%'
BEGIN
--start updates here
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR DROP/CREATE/ALTER TABLE & COLUMN CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR INSERTING & UPDATING ROW ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR FOREIGN KEY RELATIONSHIP CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR DML TRIGGERS
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR UNIQUE INDEX CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
--update the schema version
UPDATE VERSION SET TheTextValue = 't001' WHERE TheKey = 'SchemaVersion' AND TheValue=0
END
--end updates here
IF CONVERT(INT, SUBSTRING(@iSchemaVersion,2,2000)) < 1 AND @iSchemaVersion NOT LIKE 't%'
BEGIN
--start updates here
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR DROP/CREATE/ALTER TABLE & COLUMN CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR INSERTING & UPDATING ROW ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR FOREIGN KEY RELATIONSHIP CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR DML TRIGGERS
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR UNIQUE INDEX CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
--update the schema version
UPDATE VERSION SET TheTextValue = 'c001' WHERE TheKey = 'SchemaVersion' AND TheValue=1
END
--end updates here
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--*****************************************************VERSION 001**************************************
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--*****************************************************VERSION 002**************************************
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IF CONVERT(INT, SUBSTRING(@iSchemaVersion,2,2000)) < 2 AND @iSchemaVersion NOT LIKE 'c%'
BEGIN
--start updates here
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR DROP/CREATE/ALTER TABLE & COLUMN CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR INSERTING & UPDATING ROW ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR FOREIGN KEY RELATIONSHIP CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR DML TRIGGERS
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR UNIQUE INDEX CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
--update the schema version
UPDATE VERSION SET TheTextValue = 't002' WHERE TheKey = 'SchemaVersion' AND TheValue=0
END
--end updates here
IF CONVERT(INT, SUBSTRING(@iSchemaVersion,2,2000)) < 2 AND @iSchemaVersion NOT LIKE 't%'
BEGIN
--start updates here
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR DROP/CREATE/ALTER TABLE & COLUMN CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR INSERTING & UPDATING ROW ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR FOREIGN KEY RELATIONSHIP CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR DML TRIGGERS
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR UNIQUE INDEX CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
--update the schema version
UPDATE VERSION SET TheTextValue = 'c002' WHERE TheKey = 'SchemaVersion' AND TheValue=1
END
--end updates here
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--*****************************************************VERSION 002**************************************
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Simply add new VERSION sections in the script for both databases (regardless to whether an update for both is needed or not), and you can now have 2 similar databases, with very different schemas, be managed from a single update script. Feel free to change the values for whatever suites your needs best.
Hope this helps anyone else out there who has a similar environment issue.
March 3, 2011 at 2:06 pm
Awesome. Almost the solution I'd come up with.
Possible changes:
at start of script, get the initial character and the numeric split into their own variable rather than parsing them at every conditional. (Advanced: have your version function do this.)
Have an initial decision on type, then check versions inside type.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply