Friday, February 24, 2012

Global temp tables in SQL Server

Hi!
I have a stored proc that creates a global temp table. How can I have multiple users select records from and insert records in that table without overwriting and/or deleting data?
Thanks so much for your help!
-ParulA global temp table is like any other table.
How would you prevent users from overwriting data in a permanent table?
A better question is why are you using a global temporary table, and would local temporary tables suffice?|||I am using a global temp table because I have the following:

Stored Proc A calls Stored Proc B calls Stored Proc C
(both Stored Proc B and C need global temp tables)

Is it possible to somehow lock these tables so the records inserted by one are not affected by another. Is there a way to use some kind of a userID?|||I am using a global temp table because I have the following:

Stored Proc A calls Stored Proc B calls Stored Proc C
(both Stored Proc B and C need global temp tables)This does note require the use of "GLOBAL" temporary tables (prefixed with "##"). It only requires "LOCAL" temporary tables (prefixed with "#").|||Won't the variables lose scope if local temp tables are used?|||Not if one procedure calls the next. Then they are nested, and any temporary table declared in the outermost procedure exists until that procedure completes, making it available to the nested procedures.|||The downside to any temp table being used by nested procs is that each and every nested proc will recompile each time it is called. This could cause severe overhead in the execution time.|||ehhhhh...I've yet to see compile time become a factor outside of processes that are run hundreds of times per hour. And you'd have the same problem with global temporary tables.|||In any case....

Add a column to your table..and this could be employed using a permanent table...that carries the spid of the transaction

Use @.@.SPID to insert the value into the column, then each sproc would use the SPID in the predicate. This in effect would isolate the data from the other sproc calls|||That should work, thanks!|||You should make a permanent table though...what for example, do you think would happen if you had a global table in existance and then tried to create it again?

Maybe you can explain to use what you are trying to do instead of trying to sledge hammer a solution with technology

Simple is usually the best answer|||while i am using a global table, if the stored proc is running in multiple sessions, it errors out saying "##temp already exists" or "it cannot be dropped."|||In any case....

Add a column to your table..and this could be employed using a permanent table...that carries the spid of the transaction

Use @.@.SPID to insert the value into the column, then each sproc would use the SPID in the predicate. This in effect would isolate the data from the other sproc calls
We gotta agree to disagree on this one. I doubt that a permanent table is an appropriate solution tohis problem, and adding the SPID to a global temporary table is just a hack for treating it like a local temporary table.|||so what else do you recommend i should try?|||Local.

Temporary.

Tables.|||Who's we?

And you mean respectfully diagree...you wouldn't want to get banned

In any case, I'd rather know what you are really trying to accomplish in the first place.

Also, it would be interesting to know how much data we are talking about.|||You lookin' fer a fight, Joisey-boy?

You are free to "diagree" with me, while'st I shall "disagree" with you (respectfully, as always).

Global Temp Tables Getting Dropped form Time to Time

Hi all,

I have created several global temp tables to cache some intermediate results ...
However, it seems that after a while those tables will be dropped by SQL Server 2005 automatically (I have not restarted the server and no drop table statement ever executed against those tables). Is this a feature by design? How to make those global temp tables persistence to next service restart?

Thanks,
Ning
....maybe, but either way, you may want to consider using something other than global temp tables. Why don't you just create user tables and clear them out when you need to?|||

This is by design. ##tmp is dropped if the service is restarted and when there is no one else connects to it.

"Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended."

|||

Global temp tables are dropped after the last connection to the server closes. It is by design. As long as a connection is open, the tables persist.

If you need permenance of a table, then don't use 'Temp' tables -they are temporary...

Global Temp tables

Hi group,

I want to create several global temp tables. I've created a script:

if not object_id('tempdb..##tbl_ProductTypes') is null
begin
drop table ##tbl_ProductTypes
end
select * into ##tbl_ProductTypes from dbo.tbl_ProductTypes

This script creates a global temp table. When I run it in QA the table is created. When I close QA the table is dropped. This is correct since I found the following in BoL:
[Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.]

I created a job that executes the statement above. The job exists with success, however the table is not created!

Why wasn't the table created?

TIA

Regards,

SDerix
Hi,

after finishing the job why should SQL Server keep the global temp table in life ? It is automatically dropped as the job ends.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

The job will run, create the table, and then when the job finishes the table will be dropped unless you have some other connection reading from it immediately. Or am I confused by what you are asking.

What you are trying to do is likely best served by creating a permanent table in tempdb.

if not object_id('tempdb..tbl_ProductTypes') is null
begin
drop table tempdb..tbl_ProductTypes
end
select * into tempdb..tbl_ProductTypes from dbo.tbl_ProductTypes

Now you just run this, and it will exist. If you can clean up afterwards that would be a good thing, as it will waste tempdb space. But if it is minimal it wouldn't be a big deal (just add more space to tempdb, it is more or less just a database, as long as it has plenty of space and is on optimal storage :)

|||Thnx, I was affraid of that!

I really need these 'in memory' tables. When I use 'normal' tables my performance drops from seconds to minutes! Side note: I use several temp tables...

Louis,
I'll give it a go and see what the results are.

global temp table vs. permanent table use

I need to decide what is better to use: global temp table ( I can't use local one) or permanent table in SQL 2000 stored procedures. I extract data from linked server table and update several tables on our server.
Those procedures scheduled to run every 3 hours.

Another question: for some reasons when I used global temp table, I wasn't able to schedule multi steps with every step executing one of the stored procedures.I think global temp tables should be visible to other stored procedures, right?

Your suggestions?Only if it's created in a driver sproc, then it calls nested sprocs.

Otherwise it's gone at the end of the process...

I'd say create a permanent one...you won't incurr the overhead of using tempdb...

Sounds like a staging table to me anyway...

Global temp table permissions

I have a pivot table implementation, part of which is posted below. It
returns no errors in query analyzer, but when profiler is run, it shows
that "Error 208" is happening. I looked that up in BOL and it means
that an object doesn't exist. This block of code below works fine on my
local development machine, but not on our shared development server
until I go into the tempdb and make the user have the role db_owner.
Even wierder is that when I do a select * from ##pivot there is no
error, but if I specify the single column name (pivot) i.e. select
pivot from ##pivot, it takes the error...

Obviously this is a rights issue, but is there any way around this
other than making the user owner of tempdb??

declare @.select varchar(8000), @.PackageId int
set @.PackageId = 10
set @.select = '
select
Company = COALESCE(Users.Company, Contact.Company, ''''),
SubContractPackageVendor.Id, SubContractPackageVendor.isActive,
SubContractPackageVendor.isAwarded,
SubContractPackageVendor.UserOrContactType,
SubContractPackageVendor.UserOrContactId
FROM
SubContractPackageVendor
LEFT JOIN SubContractPackage ON SubContractPackageVendor.PackageId =
SubContractPackage.Id

LEFT JOIN Users ON UserOrContactType = ''User'' AND UserOrContactId =
Users.UserId
LEFT JOIN UserRoles ON UserOrContactType = ''User'' AND
UserRoles.UserId = Users.UserId AND UserRoles.ProjectId =
SubContractPackage.ProjectId
LEFT JOIN Role ON Role.RoleId = UserRoles.RoleId

LEFT JOIN Contact ON UserOrContactType = ''Contact'' AND
UserOrContactId = Contact.Id

LEFT JOIN SubContractLineItem ON
SubContractLineItem.RefType = ''Package'' AND
SubContractLineItem.RefId = SubContractPackageVendor.PackageId
LEFT JOIN SubContractLineItem as SubContractPackageVendorItem ON
SubContractPackageVendorItem.RefType = ''PackageVendor'' AND
SubContractPackageVendorItem.RefId = SubContractPackageVendor.Id AND
SubContractPackageVendorItem.RefSubId = SubContractLineItem.Id
Where
SubContractPackageVendor.PackageId = ' + CAST(@.PackageId as varchar)
+ '
GROUP BY
SubContractPackageVendor.Id, SubContractPackageVendor.isActive,
SubContractPackageVendor.isAwarded, Users.Company, Contact.Company,
SubContractPackageVendor.UserOrContactType,
SubContractPackageVendor.UserOrContactId'
--print @.sql

declare @.sumfunc varchar(100),
@.pivot varchar(100),
@.table varchar(100),
@.FieldPrefix varchar(5),
@.TotalFieldName varchar(50),
@.PivotFieldFilter varchar(1000)

select
@.sumfunc ='Sum(isnull(SubContractPackageVendorItem.Total,0) )' ,
@.pivot ='SubContractLineItem.Category' ,
@.table ='SubContractLineItem' ,
@.FieldPrefix='~' ,
@.TotalFieldName = 'Total' ,
@.PivotFieldFilter = ' AND RefType=''Package'' AND RefId=' +
CAST(@.PackageId as varchar)

set nocount on

DECLARE @.sql varchar(8000), @.delim varchar(1), @.TotalSql varchar(8000)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + '
WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' +
@.table + ' WHERE '
+ @.pivot + ' Is Not Null ' + @.PivotFieldFilter)

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char',
data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

select * from ##pivot

DROP TABLE ##pivotpb648174 (google@.webpaul.net) writes:
> I have a pivot table implementation, part of which is posted below. It
> returns no errors in query analyzer, but when profiler is run, it shows
> that "Error 208" is happening.

Because of deferred name resolution, you see quite a lot of 208 in
Profiler which are "false alarms". When SQL Server first compiles your
batch, ##pivot does not exist which causes a 208 error which is then
suppressed. When you hit a statement where ##pivot is referred, the
batch is recompiled.

> I looked that up in BOL and it means that an object doesn't exist. This
> block of code below works fine on my local development machine, but not
> on our shared development server until I go into the tempdb and make the
> user have the role db_owner. Even wierder is that when I do a select *
> from ##pivot there is no error, but if I specify the single column name
> (pivot) i.e. select pivot from ##pivot, it takes the error...

And that is a 208 and not a 207 ("Column does not exist"?) There could
be an old ##pivot which hanging around.

In any case, I'm not a friend of global temp tables, and I don't recommend
use of them. I don't know what your underlying problem is, but my favourite
is a spid-keyed table. That is, a permanent table, but which is intended
to hold any data only transitionary. The first column is spid, and in
the simplest form you use @.@.spid for the value.

Now, @.@.spid, does not work well, if you are to share the table between
processes. For this reason, we use negative values in these cases. You
could simply do:

BEGIN TRANSACTION
SELECT @.nextksy = coalesce(MIN(spid), 0) - 1 FROM tbl (UPDLOCK)

INSERT tbl (spid, ...) VALUES (@.nextkey, ...)

COMMIT TRANSACTION

Of course you should take measures to have old data removed from the
table once you are done with it. But that is really not any different
from the global temp table. Except, that is, you have more control
over it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Why does giving the user running the process db_owner access solve the
problem? Is there some other way other than rewriting large blocks of
working code?|||pb648174 (google@.webpaul.net) writes:
> Why does giving the user running the process db_owner access solve the
> problem? Is there some other way other than rewriting large blocks of
> working code?

Judging from your post, the code is not working in the general case, only
in a protected environment.

I don't know where db_owner comes into play. I played around a little
with a plain user and global temp tables, but I did not ran into any
problems.

Then again you have not specified what "not works on the shared development
server" means. Do you get error messages? Incorrect results?

Maybe the problem is access rights to the other tables, and not with
the global temp table?

Anyway, global temp tables, is in my opinion a poor solution, because
there is always a lot uigly what-if scenarios. Whence my recommentation
to change to something better.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Actually I specifically said "It returns no errors in query analyzer,
but when profiler is run, it shows that "Error 208" is happening" - it
is only on one machine that it does this, and the fix for that machine
was to give the user running the command db_owner access to the tempdb
database.

The reason I am using the global temp table implementation for this is
because the below statement correctly (and dynamically) creates the
type of the column automagically. If there is a way to do that with the
method you suggested, or better yet, to find out why giving the tempdb
db_owner access fixes the 208 error and keep the existing working,
tested and released code, that would be great. Lastly, if there is a
better way to perform a pivot table in SQL Server(with working sample),
I am all ears - this was simply the best I found that actually works.

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + '
WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM
' +
@.table + ' WHERE '
+ @.pivot + ' Is Not Null ' + @.PivotFieldFilter)|||pb648174 (google@.webpaul.net) writes:
> Actually I specifically said "It returns no errors in query analyzer,
> but when profiler is run, it shows that "Error 208" is happening" - it

And as I explained to you, the 208 in Profiler is a false alarm.

So you don't get any error, but how then you do conclude that it doesn't
work? Or don't you really have any problem at all? Do you get incorrect
results, or what is your real problem?

> is only on one machine that it does this, and the fix for that machine
> was to give the user running the command db_owner access to the tempdb
> database.

Or because when you reran the batch, the plan was in the cache, and the
208 did not happen behind the scenes.

db_owner could possibly have something to do with your reading
of INFORMATION_SCHEMA.COLUMNS, since that view is supposed to only
show things you have access to, so somesuch. Try using syscolumns
instead. (But here I am guessing very wildly of what you problem you
really have.)

> The reason I am using the global temp table implementation for this is
> because the below statement correctly (and dynamically) creates the
> type of the column automagically. If there is a way to do that with the
> method you suggested, or better yet, to find out why giving the tempdb
> db_owner access fixes the 208 error and keep the existing working,

Again, the 208 you see in Profiler is not a problem!

Rather than using SELECT INTO, create the table with CREATE TABLE. Then
you can use a plain #temp table.

> tested and released code, that would be great. Lastly, if there is a
> better way to perform a pivot table in SQL Server(with working sample),
> I am all ears - this was simply the best I found that actually works.

Popular for crosstabs and that sort of thing is RAC,
see http://www.rac4sql.net.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ok, that worked great, thanks.

I was thinking I couldn't used the #temp table since it was doing an
exec sql select and had already tried the @.temp table style... I also
wish I would have seen that reference to tempdb in there, that was a
good educated guess. I pulled that pivot code from another site,
apparently it had some problems. It is now working well with a normal
#temp table (although I have to do some shenanigans to get the type of
the column correct, which that statement with the global temp table did
so easily.)

Thanks for the help!

Global Temp Table permissions

I have a VB6 program which has to execute a SQL Server 2005 Stored Procedure
.
The SP creates a global temp table on the data and structure of an input .DB
F
file. I have SYSADMIN rights to the SQL Server. If I run the program, the SP
runs beautifully and creates the GTT. If any of my end users run it, the SP
doesn't give an error, but also doesn't create the GTT. It's got to be a
security issue, but as soon as I take myself off SYSADMIN, I can't create th
e
table, and my users can't create a GTT until I make them a SYSADMIN.
Microsoft can't be serious about this!!!! You can't possibly have to be a
SYSADMIN to create a GTT. Is there anything else I'm overlooking?That does sound strange, but I don't think Microsoft has any constraints lik
e
that. Please verify that you are granting execute premissions to the public
(or any other database role).
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Dave" wrote:

> I have a VB6 program which has to execute a SQL Server 2005 Stored Procedu
re.
> The SP creates a global temp table on the data and structure of an input .
DBF
> file. I have SYSADMIN rights to the SQL Server. If I run the program, the
SP
> runs beautifully and creates the GTT. If any of my end users run it, the S
P
> doesn't give an error, but also doesn't create the GTT. It's got to be a
> security issue, but as soon as I take myself off SYSADMIN, I can't create
the
> table, and my users can't create a GTT until I make them a SYSADMIN.
> Microsoft can't be serious about this!!!! You can't possibly have to be a
> SYSADMIN to create a GTT. Is there anything else I'm overlooking?
>|||Dave
Perhaps the sysadmin account has an appropriate permissions on filesystem.
Can you verify it?
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:433C1E5B-20E8-479D-BD8C-B0DD11CFDFF1@.microsoft.com...
>I have a VB6 program which has to execute a SQL Server 2005 Stored
>Procedure.
> The SP creates a global temp table on the data and structure of an input
> .DBF
> file. I have SYSADMIN rights to the SQL Server. If I run the program, the
> SP
> runs beautifully and creates the GTT. If any of my end users run it, the
> SP
> doesn't give an error, but also doesn't create the GTT. It's got to be a
> security issue, but as soon as I take myself off SYSADMIN, I can't create
> the
> table, and my users can't create a GTT until I make them a SYSADMIN.
> Microsoft can't be serious about this!!!! You can't possibly have to be a
> SYSADMIN to create a GTT. Is there anything else I'm overlooking?
>|||Dave (Dave@.discussions.microsoft.com) writes:
> I have a VB6 program which has to execute a SQL Server 2005 Stored
> Procedure. The SP creates a global temp table on the data and structure
> of an input .DBF file. I have SYSADMIN rights to the SQL Server. If I
> run the program, the SP runs beautifully and creates the GTT. If any of
> my end users run it, the SP doesn't give an error, but also doesn't
> create the GTT. It's got to be a security issue, but as soon as I take
> myself off SYSADMIN, I can't create the table, and my users can't create
> a GTT until I make them a SYSADMIN. Microsoft can't be serious about
> this!!!! You can't possibly have to be a SYSADMIN to create a GTT. Is
> there anything else I'm overlooking?
As you can see from this repro, no particular permissions are needed to
create global temp tables:
CREATE LOGIN hans with password = '//)LD((d'
CREATE USER hans
go
execute as login = 'hans'
go
create table ##temp (a int NOT NULL)
go
revert
go
drop table ##temp
drop user hans
drop login hans
There must be something more that you are not telling us. How do you
know that the global temp table is not created? Could you post the
relevant parts of the code?
An aside, I have rarely found global temp tables to be more a source of
confusing than of any real use. The one exception is when you want to export
data from a stored procedure by calling xp_cmdshell to run BCP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||We've been progressively adding permissions to various users and groups as
we've gone along. We have Connect and Execute permissions on the database fo
r
all the users (including Guest).
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> That does sound strange, but I don't think Microsoft has any constraints l
ike
> that. Please verify that you are granting execute premissions to the publ
ic
> (or any other database role).
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Dave" wrote:
>|||It turns out we had Adhoc Queries using xBase Drivers disabled in the
Registry for the SQL Server. In the Registry, when we turned
DisallowAdhocAccess off, everything works fine.
Thanks to everyone for your help.
"Dave" wrote:

> I have a VB6 program which has to execute a SQL Server 2005 Stored Procedu
re.
> The SP creates a global temp table on the data and structure of an input .
DBF
> file. I have SYSADMIN rights to the SQL Server. If I run the program, the
SP
> runs beautifully and creates the GTT. If any of my end users run it, the S
P
> doesn't give an error, but also doesn't create the GTT. It's got to be a
> security issue, but as soon as I take myself off SYSADMIN, I can't create
the
> table, and my users can't create a GTT until I make them a SYSADMIN.
> Microsoft can't be serious about this!!!! You can't possibly have to be a
> SYSADMIN to create a GTT. Is there anything else I'm overlooking?
>

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)