Annoying 'Invalid column name' error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Annoying 'Invalid column name' error

Author

Topic

chizer
Starting Member

3 Posts

Posted - 2005-07-12 : 07:27:58

I have created a script to update the structure of a database. Once the structure is updated a field is removed. The problem is caused because I want to be able to run this script multiple times without errors being generated (this is part of an application installer and halts execution on any error!). Because [Field1] no longer exists, I receive an error on the second execution of the script.

Invalid column name 'Field1'

The code is as follows:

IF EXISTS (SELECT * FROM dbo.syscolumns WHERE ID = object_id(N'[dbo].[Table1]') AND Name = 'Field1')
BEGIN
SET @Field1= (SELECT TOP 1 ISNULL([Field1], -1) FROM Table1)
END

/* DO SOME STUFF ... USING @Field1 */

IF EXISTS (SELECT * FROM dbo.syscolumns WHERE ID = object_id(N'[dbo].[Table1]') AND Name = 'Field1')
BEGIN
ALTER TABLE Table1
DROP COLUMN Field1
END

GO

As you can see, on the second iteration, Field1 no longer exists.

I could wrap the whole thing in an EXEC statement, but I don't feel comfortable with that as there is quite a bit of code. It is annoying because even though line 3 gets executed once only (it would never get run on a second execution) parsing still fails on the now missing column.

Has anyone ever found a solution for this problem?

Kristen
Test

22859 Posts

Posted - 2005-07-12 : 08:02:31

Use a Stored Procedure for the stuff within the BEGIN / END - thus moving the parsing error out to a sub-task which is only called if the column exists ??

Kristen

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-07-12 : 09:47:07

move the /* DO SOME STUFF ... USING @Field1 */ into the first BEGIN/END block should do the trick too.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Kristen
Test

22859 Posts

Posted - 2005-07-12 : 10:00:54

I don't think that's Chizer's problem Don - I think the stuff in the BEGIN / END block is causing the Parser to blow up if the field does not exist

Kristen

chizer
Starting Member

3 Posts

Posted - 2005-07-12 : 10:15:02

Thanks for the comments! Kristen, you put me on the right track, though I'm still not 100% happy with all the fudging I've had to do.

To elaborate (because I know you guys won't be able to sleep tonight ;)), the error was reported on line3 in my script there (second iteration).

I have now got it working, so here comes the fudge.

I created a stored proc (thanks K) but had to use text in an EXECUTE function, or the parser would have failed again on second iteration. Here's the "code":

-- Note that I am testing for the presence of the Table1.Field1 in each case and not the SP in this bit!
IF EXISTS (SELECT * FROM dbo.syscolumns WHERE ID = object_id(N'[dbo].[Table1]') AND Name = 'Field1')
BEGIN

EXEC('CREATE PROCEDURE dbo.sp_GetOldField1 ' +
'@Field1 int OUT ' +
'AS ' +
'IF EXISTS (SELECT * FROM dbo.syscolumns WHERE ID = object_id(N''[dbo].[Table1]'') AND Name = ''Field1'') ' +
'BEGIN ' +
'SET @Field1 = (SELECT TOP 1 Field1 FROM Table1) ' +
'END ')
EXEC('GRANT EXECUTE ON dbo.sp_GetOldField1 TO [MyRole] ')
END

DECLARE @Field1 int -- omitted from previous example
IF EXISTS (SELECT * FROM dbo.syscolumns WHERE ID = object_id(N'[dbo].[Table1]') AND Name = 'Field1')
BEGIN
--now run the SP to get the value.
exec sp_GetOldField1 @Field1 OUTPUT
END

/* DO SOME STUFF ... USING @Field1 */

IF EXISTS (SELECT * FROM dbo.syscolumns WHERE ID = object_id(N'[dbo].[Table1]') AND Name = 'Field1')
BEGIN
--now drop the temporary proc I created earlier
DROP PROC sp_GetOldField1
ALTER TABLE Table1
DROP COLUMN Field1
END

GO

--end of sample

And that's it! Seems to work OK now. Thanks for the help.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-07-12 : 10:18:58

i see. i read it as, IF field exists, do this. THEN, do other stuff, regardless if it exists, THEN drop the column if it exists.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Kristen
Test

22859 Posts

Posted - 2005-07-12 : 10:27:40

I would have pre-create the SProc, rather than trying to create it in-line at that point, but much-of-a-muchness.

Sticking a GO after the creation of the SProc would mean the rest of the script would run, so even if the SProc CREATE failed the remainder would run. (The error message would be a pain though!)

Kristen

chizer
Starting Member

3 Posts

Posted - 2005-07-12 : 10:58:27

I have to create it inline at the time of execution so I can use it. I wouldn't have any other opportunity to create it other than in a single script. Maybe I'm not comprehending what you meant by that K, but if I created the SP without using EXEC I would have just got the same parser error

I run the script from an installer via osql, so any errors returned would cause the installer to abort, and I'm not about to start parsing osql errors.

Thanks again, onto my next SQL nightmare, chopping up strings > 4000 chars

Kristen
Test

22859 Posts

Posted - 2005-07-12 : 13:36:19

My being dense I expect! What I mean was a script that looks like this:


PRINT 'Ignore any error in this section'
GO
CREATE PROCEDURE dbo.sp_GetOldField1 
...
Some potential syntax error here
...
GO
PRINT '*** End of Ignore Error bit ***'
GO

IF EXISTS (SELECT * FROM dbo.syscolumns WHERE ID = object_id(N'[dbo].[Table1]')
           AND Name = 'Field1')
BEGIN
EXEC dbo.sp_GetOldField1 ...
END
... rest of "patch" script ...
GO
IF EXISTS( ... 'sp_GetOldField1' ...)
BEGIN
  DROP PROCEDURE dbo.sp_GetOldField1
END
GO

ISQL would march right past any errors, maybe OSQL doesn't, I haven't checked

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources