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.
| Author |
Topic |
|
tomino79
Starting Member
13 Posts |
 Posted - 2005-09-12 : 10:31:21
|
| Hi, try to write this to your SQL Query Analyzer and you will get an error message:create table aaa ( i int) insert into aaa ( i ) values ( 2 )alter table aaa add j intinsert into aaa ( i,j ) values ( 2,3 )The first time you execute the query you get Unknown column "j". Then when you execute every step everything is ok. (I know maybe it cannot be in one batch because when the select is preparing he doesnt know that the i will be there, but why ???) BUT HTE PROBLEM IS:When you then drop the table and try to execute the whole sequence as a batch, there is NO ERROR. (Is SQL Server remembering something about table aaa???) And then you can execute the sequence as many times as you wish, you will not get any error messages.It is very strange, and I couldnt find any explanation for it on MSDN.Can anybody help me with that ?Tomas. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-09-12 : 10:33:15
|
put "go" between statementsGo with the flow & have fun! Else fight the flow  |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-12 : 10:34:33
|
| After Alter statement use Goalter table aaa add j intGoinsert into aaa ( i,j ) values ( 2,3 )MadhivananFailing to plan is Planning to fail |
|
|
|
tomino79
Starting Member
13 Posts |
Posted - 2005-09-12 : 10:48:47
|
| But I cant put directly GO to the sequence, because GO is only a SQL Query Analyzer Statement.I am executing this query through ADO Command. But anyway, that is not important, because after the separate execution (with go) you can simply remove go and it will work..You know what is strange in this case.. That the SQLSrv acts different in the first time then the other. Because after executing it with go, you can remove GO, and execute it as a whole sequence.Tom.Thanks for help, I was looking for some theoretical explanation (cache ? has SQL Server cached that aaa will have i and j columns so in the step of generating execution plan it will not raise an error ? ) |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-09-13 : 05:17:35
|
| GO is a job seperator...not a T-SQL command. The code fails without the GO, because at 'plan generation stage', the 'J' column doesn't exist. Basically you're hitting a problem because you're adding columns on the fly....."don't" is the best advice. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-09-13 : 05:26:26
|
| Wow! That was a long post to ask for the statement terminator in ADO. That would be the humble semi-colon ;.Works in the SqlClient classes too.DavidMA front-end is something that tries to violate a back-end. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
|
|
|
|
|
|
|
Unexecutable sequence