What is wrong my script?

Question

Post reply

What is wrong my script?

adonetok

SSCrazy Eights

Points: 8395
More actions
February 11, 2011 at 1:12 pm

#231816

It works:
CREATE TABLE #TMP
(
MEMBID VARCHAR(20) NULL
)
INSERT #TMP
(MEMBID)
VALUES('111')
ALTER TABLE #TMP ADD PQI INT
------
But once adding update statement, I got an error said "Invalid column name 'PQI'"
CREATE TABLE #TMP
(
MEMBID VARCHAR(20) NULL
)
INSERT #TMP
(MEMBID)
VALUES('111')
ALTER TABLE #TMP ADD PQI INT
UPDATE #TMP
SET PQI = 1

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

I copied and pasted your script directly into my SSMS 2005... and don't get a failure. No idea why this is breaking.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Lowell SSC Guru Points: 323518 More actions · Answer 2

in this specific example, i cannot get it to raise the error, but I've encountered other situations where i must issue the GO command after an ALTER TABLE command before I was able to do DML operations to the table i issued DDL commands against; it's weird that it's not raising the error, because I'm sure I'd seen it before;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sqlraider SSCertifiable Points: 5441 More actions · Answer 3

I believe the OP is seeing the error in SQL Server Management Studio prior to execution.

I copied the script in and on the line:

SET PQI = 1

PQI is underlined in RED. Placing your cursor over it displays the error.

Just execute it.

adonetok SSCrazy Eights Points: 8395 More actions · Answer 4

You are right.

After adding "go", it works fine.

What is happend?

Does SQL have refresh table command?

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

adonetok (2/11/2011)
You are right.
After adding "go", it works fine.
What is happend?
Does SQL have refresh table command?

Original, non-modified version of #TMP may have still been in memory. Usually when I get odd behavior like that I make sure all my current #table are dropped and retry.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA