Interesting Update Error - SQL Server Forums

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

SQL Server Development (2000)

Interesting Update Error

Author

Topic

SMc
Starting Member

7 Posts

Posted - 2007-01-04 : 06:02:56

Hi guys,

I've come across some odd behaviour when trying to add a field programmatically, and wondered if anyone else has come across this (or if I'm doing something wrong I haven't spotted of course

)

The basic outline of what I want to do is as follows:
I have two tables each of which has a bit field, the first table references the second by an ID field. I want to move the bit field from table 2 into table 1, and I am trying to do this by creating a new field then using an UPDATE..FROM.. query to insert all the corresponding values.

When I run each query separately they perform as expected, however when I run them together in a batch SQL Server doesn't seem to acknowledge the new field exists. The commands below replicate the error - I am using Query Analyser in SQL Server 2000:

CREATE TABLE #temp1 (tid INTEGER NOT NULL, t1flag BIT NOT NULL)
INSERT INTO #temp1 (tid, t1flag) VALUES (1, 1)
INSERT INTO #temp1 (tid, t1flag) VALUES (2, 1)
INSERT INTO #temp1 (tid, t1flag) VALUES (3, 0)
INSERT INTO #temp1 (tid, t1flag) VALUES (4, 1)
INSERT INTO #temp1 (tid, t1flag) VALUES (5, 0)
INSERT INTO #temp1 (tid, t1flag) VALUES (6, 1)
INSERT INTO #temp1 (tid, t1flag) VALUES (7, 1)
INSERT INTO #temp1 (tid, t1flag) VALUES (8, 0)
INSERT INTO #temp1 (tid, t1flag) VALUES (9, 0)
INSERT INTO #temp1 (tid, t1flag) VALUES (10, 1)

CREATE TABLE #temp2 (tid INTEGER NOT NULL, t2flag BIT NOT NULL)
INSERT INTO #temp2 (tid, t2flag) VALUES (1, 1)
INSERT INTO #temp2 (tid, t2flag) VALUES (2, 1)
INSERT INTO #temp2 (tid, t2flag) VALUES (3, 1)
INSERT INTO #temp2 (tid, t2flag) VALUES (4, 1)
INSERT INTO #temp2 (tid, t2flag) VALUES (5, 1)
INSERT INTO #temp2 (tid, t2flag) VALUES (6, 1)
INSERT INTO #temp2 (tid, t2flag) VALUES (7, 1)
INSERT INTO #temp2 (tid, t2flag) VALUES (8, 1)
INSERT INTO #temp2 (tid, t2flag) VALUES (9, 1)
INSERT INTO #temp2 (tid, t2flag) VALUES (10, 1)

ALTER TABLE #temp2 ADD t1flag BIT NULL

UPDATE #temp2 SET t1flag = t1.t1flag FROM #temp1 t1 WHERE #temp2.tid = t1.tid

SELECT * FROM #temp2

Any ideas what is happening?

Steve

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-04 : 06:34:02

see
http://www.nigelrivett.net/SQLTsql/AccessTempTablesAcrossSPs.html
It explains your error and how to get round it.

The server will resolve object id's when it tries to run a batch - at this time the new column isn't there so error.
The way round it is to access the new column in a different batch - either by putting a go between them or accessing it in dynamic sql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SMc
Starting Member

7 Posts

Posted - 2007-01-04 : 06:46:27

Thanks Nigel, though in my actual situation the two tables in question are proper database (ie not temp) tables, so I wouldn't have thought this would be a scope problem.

I am working around it at the moment by doing the ALTER TABLE and UPDATE queries in different SQLEXEC calls.

Steve

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-04 : 07:45:28

No - the scoping works the same as with temp tables. As I pointed out the problem is trying to reference a changed structure in the batch which changes it - the object doesn't exist when the optimiser tries to resolve the object id.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SMc
Starting Member

7 Posts

Posted - 2007-01-04 : 07:50:52

Ah right, well that would explain it then :)

Thanks for the help, as I said I have implemented the suggested workaround and it is working fine.

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources