global temp table issue with SQL Server 2000

In a SP I create a global temp table as follows:
if not object_id('tempdb..##t') is null drop table ##t
create table ##t (a int, b int)
Then, only on occasion, I get an "invalid column" error when referring to
column b. I think this is due to the fact that I create a table with the sam
e
name -- one that doesn't have column b -- prior to running this SP. However,
this doesn't make any sense since I drop it and create it again...
What I am missing here?Why are you using a global temp table?
Have you ever printed the result of object_id('tempdb..##tt')?
A
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:22351B35-23E4-4C75-954B-4502791E5E4C@.microsoft.com...
> In a SP I create a global temp table as follows:
> if not object_id('tempdb..##t') is null drop table ##t
> create table ##t (a int, b int)
> Then, only on occasion, I get an "invalid column" error when referring to
> column b. I think this is due to the fact that I create a table with the
> same
> name -- one that doesn't have column b -- prior to running this SP.
> However,
> this doesn't make any sense since I drop it and create it again...
> What I am missing here?
>|||Thank you for your reply.
The global temp table is used to interface with another SP. It's global
because this other SP is called in different contexts, including the SP at
hand and a manual query analyser sessions.
I have never printed the return value of the existence test. But here's my
thinking: if the code fails to drop the table for any reason, why doesn't th
e
'create table' statement generate an error?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why are you using a global temp table?
> Have you ever printed the result of object_id('tempdb..##tt')?
> A
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:22351B35-23E4-4C75-954B-4502791E5E4C@.microsoft.com...
>
>|||> The global temp table is used to interface with another SP.
http://www.sommarskog.se/share_data.html|||These are very useful tips in general, but they do not explain my problem in
particular. I looked at the comments regarding the use of temp tables for
parameter passing and the related maintenance drawbacks, but mine is a
different situation. I did not modify any parameters. This article (please
correct if I'm wrong) says that errors will occur when the column definition
changes in either the caller or the callee. And that is perfectly normal
behavior. However, my error does not occur in the callee. This is what I do
in the caller:
drop table ##t
create table ##t (a int, b int)
-- fill ##t with input parameters
exec p -- SP 'p' fills column a in ##t, but it does not use column b
select b from ##t -- this is where the error occurs => 'b is an invalid
column'
"Aaron Bertrand [SQL Server MVP]" wrote:

> http://www.sommarskog.se/share_data.html
>
>|||Without exact code where we can reproduce the problem, it is tough to
speculate what happens "only on occasion." Is it even remotely possible
that you are reusing the name #tt in more than one place, for tables with
different structures? You may consider using a more useful, legible and
unique name. I would have absolutely no concept of what would reside in a
table called #tt and would find myself doing a grep for that name in other
people's code, if it were even close to an attractive name to use in my
own...
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:6E47FE3B-5525-445D-BB12-05301672B231@.microsoft.com...
> These are very useful tips in general, but they do not explain my problem
> in
> particular. I looked at the comments regarding the use of temp tables for
> parameter passing and the related maintenance drawbacks, but mine is a
> different situation. I did not modify any parameters. This article (please
> correct if I'm wrong) says that errors will occur when the column
> definition
> changes in either the caller or the callee. And that is perfectly normal
> behavior. However, my error does not occur in the callee. This is what I
> do
> in the caller:
> drop table ##t
> create table ##t (a int, b int)
> -- fill ##t with input parameters
> exec p -- SP 'p' fills column a in ##t, but it does not use column b
> select b from ##t -- this is where the error occurs => 'b is an invalid
> column'
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||You are absolutely right about naming and '##t' is not the real name of this
table. I just used it to explain my point here.
The callee SP p expects temp table ##t with column 'a'. This SP is called by
caller 1 and caller 2.
Caller 1 does:
drop table ##t
create table ##t (a int, b int)
-- populate ##t
exec p
Caller 2 does:
drop table ##t
create table ##t (a int, c char)
-- populate ##t
exec p
select c from ##t -- this is where the error occurs (column c not recognized
)
I never use caller 1 and caller 2 concurrently.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Without exact code where we can reproduce the problem, it is tough to
> speculate what happens "only on occasion." Is it even remotely possible
> that you are reusing the name #tt in more than one place, for tables with
> different structures? You may consider using a more useful, legible and
> unique name. I would have absolutely no concept of what would reside in a
> table called #tt and would find myself doing a grep for that name in other
> people's code, if it were even close to an attractive name to use in my
> own...
>
>
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:6E47FE3B-5525-445D-BB12-05301672B231@.microsoft.com...
>
>|||Here is the code that will allow reproduction of this error. I take back the
"only on occasion", since you can actually reproduce this behavior all the
time. Create the three SPs below, and then call them one after the other.
-- callee
create procedure [dbo].[callee] as
insert ##t (a)
select 1
-- caller 1
create procedure [dbo].[caller1] as
if not object_id('tempdb..##t') is null drop table ##t
create table ##t (a int, b int)
exec dbo.callee
update ##t set b = 1
-- caller 2
create procedure [dbo].[caller2] as
if not object_id('tempdb..##t') is null drop table ##t
create table ##t (a int, c char)
exec dbo.callee
update ##t set c = 'x'
-- test
exec dbo.caller1
exec dbo.caller2 -- blows up here
/* The code in caller2 that drops the table and creates it again never
executes. In fact, the error happens before any code in caller 2 runs at all
.
It seems as caller 2 does some checking prior to executing, which includes
matching column names to the columns in the tables, if those tables exist. I
f
a table doesn't exist (i.e. you drop ##t before running caller 2), it checks
without errors. */
"Aaron Bertrand [SQL Server MVP]" wrote:

> Without exact code where we can reproduce the problem, it is tough to
> speculate what happens "only on occasion." Is it even remotely possible
> that you are reusing the name #tt in more than one place, for tables with
> different structures? You may consider using a more useful, legible and
> unique name. I would have absolutely no concept of what would reside in a
> table called #tt and would find myself doing a grep for that name in other
> people's code, if it were even close to an attractive name to use in my
> own...
>
>
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:6E47FE3B-5525-445D-BB12-05301672B231@.microsoft.com...
>
>|||And this is exactly what I was suggesting. You have two different stored
procedures, creating a global temp table with the same name, with different
structure.
STOP DOING THAT. Problem solved.
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:E5DBA890-F8EB-4011-9E7F-7C09015AC4B7@.microsoft.com...
> Here is the code that will allow reproduction of this error. I take back
> the
> "only on occasion", since you can actually reproduce this behavior all the
> time. Create the three SPs below, and then call them one after the other.
> -- callee
> create procedure [dbo].[callee] as
> insert ##t (a)
> select 1
> -- caller 1
> create procedure [dbo].[caller1] as
> if not object_id('tempdb..##t') is null drop table ##t
> create table ##t (a int, b int)
> exec dbo.callee
> update ##t set b = 1
> -- caller 2
> create procedure [dbo].[caller2] as
> if not object_id('tempdb..##t') is null drop table ##t
> create table ##t (a int, c char)
> exec dbo.callee
> update ##t set c = 'x'
> -- test
> exec dbo.caller1
> exec dbo.caller2 -- blows up here
>
> /* The code in caller2 that drops the table and creates it again never
> executes. In fact, the error happens before any code in caller 2 runs at
> all.
> It seems as caller 2 does some checking prior to executing, which includes
> matching column names to the columns in the tables, if those tables exist.
> If
> a table doesn't exist (i.e. you drop ##t before running caller 2), it
> checks
> without errors. */|||On Wed, 21 Dec 2005 08:28:02 -0800, Ken Abe wrote:

>Here is the code that will allow reproduction of this error. I take back th
e
>"only on occasion", since you can actually reproduce this behavior all the
>time. Create the three SPs below, and then call them one after the other.
(snip)
Hi Ken,
This has to do with how the compilation process works. The complete
batch is compiled at once, and SQL Server does not attempt to parse
CREATE TABLE, ALTER TABLE and DROP TABLE statements. If a table does not
exist at compile-time, checking the columns is delayed until execution
time (or rather: until the re-compilation that will occur after creating
the table). If the table does exist, the current definition is used to
check the code and create a plan.
The solution is to drop the table when you're done with it, not retain
it and only drop it right before you want to create a new version. This
way, the table won't exist when the stored procedure is called and there
won't be any compilation time errors.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)