October 4, 2011 at 1:14 am
Hi'
I have a small challenge that I'm quite stuck with. Hoping for help here 🙂
The Setup:
We have an application that works on an SQL Server 2008 instance
During fresh install (via wix - msi package), the DB is installed and several config scripts etc are run.
During update, a pre and post script is run before and after creation of the tables, to do the magic that is needed for the upgrade to work.
The task:
We now have a column (OldColumn) on a table that is be replaced by another (NewColumnPK) in future versions of the app.
In fresh installs, this is no issue.
However in upgrade scenarios we have to seed the new column NewColumnPK based on the value of the old
(the new value in NewColumnPK is a foreign key to another table).
I try to accomplish this in the post script, by checking for the existence of OldColum and if it exists i do the mapping of stuff.
The script code for this looks at the value of OldColumn and based on this pulls a new value for NewColumnPK.
The problem:
And here the problem arises.
In the fresh install scenario the OldColumn is never created and therefore does not exists.
SQL Server then fails when trying to parse the code in the post script due to the missing column OldColumn...
And now I'm stuck :rolleyes:
The solution:
[fill in blanks] :hehe:
I have tried all kinds of workarounds but nothing but the real dirty ones seem to do the trick.
Help would be much appreciated.
Thanks in advance
/Jan
October 4, 2011 at 1:59 am
We have a similair setup here, where a software package upgrades a database to the latest version but occassionaly alterations have to be made before and after this for a variety of reasons.
Using a test database create the following table:
CREATE TABLE dbo.TestTable1(
IDINTIDENTITY,
OLDINT,
);
GO
INSERT INTO dbo.TestTable1(OLD)
SELECT 6
UNION
SELECT 7;
This just creates something to play with. The following code will either add a new column called 'NEW' or (if it already exists) update this column with the content of 'OLD':
IF EXISTS (SELECT * FROM sys.all_columns WHERE name='NEW' and object_id = object_id('TestTable1'))
EXEC sp_executesql N'UPDATE x
SET x.NEW = y.OLD
FROM dbo.TestTable1 x
INNER JOIN dbo.TestTable1 y ON y.ID = x.ID'
ELSE
BEGIN
ALTER TABLE dbo.TestTable1
ADD NEW Int;
END
Run the second segment of code once and it will create 'NEW'. Run it a second time and will will set 'NEW' to the values of 'OLD'.
Is this roughly what you're after?
October 4, 2011 at 2:08 am
Hi' BrainDonor,
I have to do a small test, but executing a query with some "inline" SQL statement could very well do the trick.
My take is, that this is not parsed upfront and therefore no error => me so happy 😀
Thanks a bunch for the reply.
/J
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply