February 24, 2011 at 8:34 am
Hello - I have the following 2 tables in a database that receive updated information whenever an update script is run (dbo.VERSION and dbo.VERSION_HISTORY). These tables are maintained so that I can tell what a particular database has as it's database version, schema version, and software version. Below is the DDL for both of those tables:
--Creates new tables: VERSION and VERSION_HISTORY
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VERSION]') AND type in (N'U'))
DROP TABLE [dbo].[VERSION]
GO
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] [numeric](10, 0) NOT NULL,
[TheTextValue] [nvarchar](255) NULL,
CONSTRAINT [PK_TheKey] PRIMARY KEY CLUSTERED
(
[TheKey] 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
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VERSION_HISTORY]') AND type in (N'U'))
DROP TABLE [dbo].[VERSION_HISTORY]
GO
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] [numeric](10, 0) NOT NULL,
[TheTextValue] [nvarchar](255) 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
So - I created my tables, and began working on a script to use for doing various kinds of DDL updates with. The problem I am running into is that whenever a batch takes place, I lose the scope of my variable (which holds the result of a function) going into my next section of the script. The reason for the spliting up of the code entries like this is I have 2 databases that are made from the same schema, but very different in their end result. However - I need to maintain 1 single script to do the updates with, and thus - here is the DDL of my update script:
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 INT
BEGIN
DECLARE @v_curValue INT
SELECT @v_curValue = TheValue FROM VERSION WHERE TheKey = 'SchemaVersion'
--Determine which database
IF @v_curValue = '0'
BEGIN
SET @v_curValue = '0'
END
IF @v_curValue = '1'
BEGIN
SET @v_curValue = '1'
END
RETURN @v_curValue
END
GO
--CREATE variable to hold schema version
DECLARE @iSchemaVersion INT
SELECT @iSchemaVersion = dbo.determine_current_schema_version()
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--*****************************************************VERSION 001*****************************************************
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IF @iSchemaVersion = '0'
BEGIN
--start updates here
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
THIS SECTION IS FOR DROP/CREATE/ALTER TABLE & COLUMN CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
THIS SECTION IS FOR INSERTING & UPDATING ROW ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
SET NOCOUNT ON
SET IDENTITY_INSERT [dbo].[Country] ON
INSERT [dbo].[Country]([CountryID],[CountryName],[CountryAbbreviation]) VALUES (2,'Brazil','BRA')
SET IDENTITY_INSERT [dbo].[Country] OFF
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
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
IF @iSchemaVersion = '1'
BEGIN
--start updates here
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
THIS SECTION IS FOR DROP/CREATE/ALTER TABLE & COLUMN CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
THIS SECTION IS FOR INSERTING & UPDATING ROW ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
SET NOCOUNT ON
SET IDENTITY_INSERT [dbo].[Country] ON
INSERT [dbo].[Country]([CountryID],[CountryName],[CountryAbbreviation]) VALUES (3,'Turkey','TUR')
SET IDENTITY_INSERT [dbo].[Country] OFF
/****** Object: Table [dbo].[Log] Script Date: 02/24/2011 09:19:15 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Log]') AND type in (N'U'))
DROP TABLE [dbo].[Log]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Log](
[LocationID] [int] NOT NULL,
[OriginID] [smallint] NOT NULL,
[TriggerTicks] [bigint] NOT NULL,
[Notes] [nvarchar](max) NULL,
[Message] [nvarchar](max) NOT NULL,
[StackTrace] [nvarchar](max) NOT NULL,
[TriggerDate] [datetime] NOT NULL,
[Priority] [smallint] NOT NULL,
CONSTRAINT [PK_TriggerTicks] PRIMARY KEY CLUSTERED
(
[TriggerTicks] 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
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
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'
END
--end updates here
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--*****************************************************VERSION 001*****************************************************
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
So - the bottom line to all of this, is how can I get this script to work if I have the need to use the 'GO' command between batches? I've tried a couple of things to put the parameter/variable back in scope, but it's not working (e.g. re-declare it before the second section of code, not use the GO statement for the various batches, etc...), but to no avail.
Sorry that I do not have the DDL for the 2 databases, but if that becomes necessary - I will try to put something together.
Thank you for any help in advance!
February 24, 2011 at 9:36 am
Should work by just removing the 'GO's after the create function part.
Speaking of, why are you doing this:
IF @v_curValue = '0'
BEGIN
SET @v_curValue = '0'
END
IF @v_curValue = '1'
BEGIN
SET @v_curValue = '1'
END
It's redundant as written.
February 24, 2011 at 10:09 am
The point you make here is a good one, and in fact - has now raised a much different issue for me. Is there any way for me to have 2 variables/parameters in my function? I need to do something with that logic to tell which database it is, and am not sure how to go about constructing it.
Thanks again for any and all help.
February 25, 2011 at 8:54 am
Will it have to be a table valued function or a multi-valued function? This is 2005 SP3.
February 25, 2011 at 9:02 am
You're returning an INT, so it's a scalar function.
February 25, 2011 at 9:14 am
Should I consider using a SELECT CASE instead of the IF statement?
February 28, 2011 at 11:22 am
I think I am almost there...The script is running fine, using a single variable/parameter with potentially 1 of 2 values, but the problem I am getting is that when this script runs - it's still not able to determine which value to use, and instead uses both. I need it to know which is which, based of TheValue determined. Here is the script
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_curValue NVARCHAR(10)
SET @v_curValue = (SELECT TheValue FROM dbo.VERSION WHERE TheKey = 'SchemaVersion')
--Determine which database
IF @v_curValue = 0
BEGIN
SET @v_curValue = 't%'
END
ELSE IF @v_curValue = 1
BEGIN
SET @v_curValue = 'c%'
END
RETURN @v_curValue
END
GO
--CREATE variable to hold schema version
DECLARE @iSchemaVersion NVARCHAR(10)
SELECT @iSchemaVersion = dbo.determine_current_schema_version()
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--*****************************************************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
IF @iSchemaVersion < 'c001'
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'
END
--end updates here
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--*****************************************************VERSION 001**************************************
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--*****************************************************VERSION 002**************************************
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IF @iSchemaVersion < 't002'
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'
END
--end updates here
IF @iSchemaVersion < 'c002'
BEGIN
--start updates here
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ THIS SECTION IS FOR DROP/CREATE/ALTER TABLE & COLUMN CODE ENTRIES
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\*/
/****** Object: Table [dbo].[Command] Script Date: 02/24/2011 14:58:54 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Command]') AND type in (N'U'))
DROP TABLE [dbo].[Command]
/****** Object: Table [dbo].[Command] Script Date: 02/24/2011 14:58:59 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[Command](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TreaterID] INT NOT NULL,
[Requested] [datetime] NOT NULL CONSTRAINT [DF__Command__Request__5FB337D6] DEFAULT (getdate()),
[TableID] [smallint] NOT NULL,
[RecordID] [varchar](255) NOT NULL,
[Command] [smallint] NOT NULL CONSTRAINT [DF_Command_Command] DEFAULT (''),
[RetriesAttempted] [int] NOT NULL CONSTRAINT [DF_Command_Log] DEFAULT ((0)),
[Cancelled] [bit] NULL CONSTRAINT [DF_Command_Cancelled] DEFAULT ((0)),
[SentDate] [datetime] NULL CONSTRAINT [DF__Command__SentDat__619B8048] DEFAULT (NULL),
[ACKDate] [datetime] NULL CONSTRAINT [DF__Command__ACKDate__628FA481] DEFAULT (NULL),
[ResponseDate] [datetime] NULL CONSTRAINT [DF__Command__Respons__6383C8BA] DEFAULT (NULL),
[TimeoutDate] [datetime] NULL CONSTRAINT [DF__Command__Timeout__656C112C] DEFAULT (NULL),
[SecurityUserID] [uniqueidentifier] NULL,
[InterfaceHardwareIdentifierUsed] [varchar](50) NULL CONSTRAINT [DF_Command_Interface_Hardware_IDentifier] DEFAULT ((-1)),
CONSTRAINT [PK_ID3] 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]
/*/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ 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'
END
--end updates here
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--*****************************************************VERSION 002**************************************
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
I wind up with my VERSION_HISTORY table having both the updates for the c000 and t000 updates. Please note that I finally got this working to this point with a data type of NVARCHAR instead of INT (as int was not able to convert my 't%' not 'c%' wildcards).
Thank you for your help!
February 28, 2011 at 11:29 am
Using < with a character string is a tricky thing. What exactly do you want when you have:
IF @iSchemaVersion < 't001'
do you really mean "do this if the schema version is not t001"?
If so, that should be: IF @iSchemaVersion <> 't001'
February 28, 2011 at 11:34 am
Hi Pam -
Yes - in a nutshell I have tried:
< 001
= 000
<= 000
I'll give NOT EQUAL TO a try. I'm starting to thinking that I would be better of with a PROC instead of a FUNCTION for this need.
Thanks, and I'll report back on this here in a bit.
February 28, 2011 at 11:38 am
Same result...I still get both. It's not determining which db it is.
:ermm:
February 28, 2011 at 11:45 am
Add the highlighted line - what is it returning?
DECLARE @iSchemaVersion NVARCHAR(10)
SELECT @iSchemaVersion = dbo.determine_current_schema_version()
[highlight=#ffff11]SELECT '*'+@iSchemaVersion+'*' as CheckiSchemaVersion[/highlight]
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--*****************************************************VERSION 001**************************************
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IF @iSchemaVersion < 't001'
February 28, 2011 at 11:53 am
It returns *c%*, which I guess is the reason why it is bypassing the logic all together, and including both updates to a given database. But is there a way for me to get this to work with the NVARCHAR data type? I was initially using an INT, but the problem there was it was erroring out with the implied converstion from nvarchar to int.
February 28, 2011 at 11:58 am
c% is not an int so it makes sense that it would error out.
c% is a string and that's the data in the table. So if you want something else, you have to put something else in the table.
So let's bring this down to a basic level in English:
What values are you expecting to get from the table and what value(s) are actually there? Why are you using the %?
February 28, 2011 at 12:02 pm
The data types in the 2 tables in question are using nvarchar(10) and (20). TheTextValue holds a revision number, identified with either a "c" or a "t" and then 000 (which is where the schema level is starting off).
The reason I am needing for the logic to determine which of these it is, is due to having 2 instances of a database in my environment, with the same names (but are far from the same schemas). One is on our main server, and the other is a watered down version of it on a hardware device out ours. There is only going to be 1 script allowed for schema updates to either database, and thus I am trying to get the logic such that it can recognize which database it is by the entries in each ones respective VERSION table.
February 28, 2011 at 12:02 pm
Oh wait a minute I see it now...
In your function:
IF @v_curValue = 0
BEGIN
SET @v_curValue = 't%'
END
ELSE IF @v_curValue = 1
BEGIN
SET @v_curValue = 'c%'
END
RETURN @v_curValue
So the ONLY values you will ever see are 't%','c%' and NULL.
This means that your if statement should be something like:
If @thereturnedvalue = 'c%'
do something
If @thereturnedvalue = 't%'
do something else
If @thereturnedvalue IS NULL
do something else entirely - probably raise an alarm.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply