altering temporary tables

hi all,
i have a simple stored procedure that takes input an integer..then inside it
it creates a temporary table & adds columns which are equal in number to the
integer that was inputed.
but iam encountering variuos error when i execute this procedure
here is the code(a very simple & small stored procedure):
create procedure mySolution @.colCount int as
declare @.i int
declare @.currTable varchar(10)
declare @.query nvarchar(255)
set @.i = 0
while @.i <= @.colCount
begin
set @.i = @.i + 1
if (@.i % 15 = 1)
begin
set @.query = 'create table #tasksecf'+cast((@.i/15) as nvarchar(255))+'
(empid int)'
set @.currTable = '#taskecf'+cast((@.i/15) as NVARCHAR(255))
exec(@.query)
end
set @.query = 'alter table '+@.currTable+' add col'+ cast(@.i as varchar)+ '
nvarchar(255)'
exec(@.query)
end
here are the errors :
if i execute this line
exec mySolution '15'
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf1' because this table does not exist in database
'MyPracticeDB'.Perhaps you should tell us what are you actually trying to achieve.
Each EXEC statement runs in a different context in which the local temporary
table created in the main procedure does not exist. Using a global table
would work, but I would advise against that - it may lead to other issues.
ML
http://milambda.blogspot.com/|||Hmmm, I think that temp table doesnt exists outside of the execution od
dynamic string and that would cause errors you get.
What are you trying to do with this sp? Perhaps you could explain the
requirement so we can try to find alternative solutions?
MC
"kishore bondada" <kishore bondada@.discussions.microsoft.com> wrote in
message news:361D5746-7137-4361-8BD0-D77150AF7326@.microsoft.com...
> hi all,
> i have a simple stored procedure that takes input an integer..then inside
> it
> it creates a temporary table & adds columns which are equal in number to
> the
> integer that was inputed.
> but iam encountering variuos error when i execute this procedure
> here is the code(a very simple & small stored procedure):
> create procedure mySolution @.colCount int as
> declare @.i int
> declare @.currTable varchar(10)
> declare @.query nvarchar(255)
> set @.i = 0
> while @.i <= @.colCount
> begin
> set @.i = @.i + 1
> if (@.i % 15 = 1)
> begin
> set @.query = 'create table #tasksecf'+cast((@.i/15) as nvarchar(255))+'
> (empid int)'
> set @.currTable = '#taskecf'+cast((@.i/15) as NVARCHAR(255))
> exec(@.query)
> end
> set @.query = 'alter table '+@.currTable+' add col'+ cast(@.i as varchar)+ '
> nvarchar(255)'
> exec(@.query)
> end
>
> here are the errors :
> if i execute this line
> exec mySolution '15'
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf0' because this table does not exist in
> database
> 'MyPracticeDB'.
> Server: Msg 4902, Level 16, State 1, Line 1
> Cannot alter table '#taskecf1' because this table does not exist in
> database
> 'MyPracticeDB'.
>|||I've never tries t his, but perhaps you can switch your context to
tempdb and alter from there...once you find it.|||No, the problem is pre-compile, e.g. the parser can't find #table because
#table hasn't been created yet.
An alternative is to complete the whole chunk in dynamic SQL. Here is one
approach thatuses a permanent table tied to the SPID, this way you don't
have to do everything inside a single EXEC() (but you will still have to use
dynamic SQL to reference the table name):
DECLARE @.colCount INT;
SET @.colCount = 5;
DECLARE @.i INT,
@.TableName VARCHAR(32),
@.Query NVARCHAR(4000);
SET @.TableName = 'TaskSecF_'+RTRIM(@.@.SPID);
SET @.Query = 'IF OBJECT_ID('''+@.TableName+''') IS NOT NULL
DROP TABLE '+@.TableName+';
SELECT col1 = CONVERT(NVARCHAR(255), N'''')';
SET @.i = 1;
WHILE @.i <= @.colCount
BEGIN
SET @.i = @.i + 1
SET @.query = @.query + ',col'+RTRIM(@.i)+' = CONVERT(NVARCHAR(255), N'''')'
END
SET @.query = @.query + ' INTO '+@.TableName+' WHERE 1=2;'
EXEC(@.query)
EXEC('SELECT * FROM '+@.TableName);
...
EXEC('DROP TABLE '+@.TableName);
"MJKulangara" <RutgersDBA@.gmail.com> wrote in message
news:1139324385.781046.242250@.g43g2000cwa.googlegroups.com...
> I've never tries t his, but perhaps you can switch your context to
> tempdb and alter from there...once you find it.
>|||hi all,
first of all thank you all for your valuable replies. As asked by you iam
explaining the requirement a bit more clearly here:
Iam supposed to write a stored Procedure that takes input, an integer ' N '
and inside it create some temp tables, where each temp table contains ' X '
number of columns with datatype NVARCHAR(255).
since the SQL Server 2000 MAXROWSIZE limit is 8060 bytes, each temp table
can atmaximum contain 15 to 16 fields only (with datatype NVARCHAR(255)).
now if the user gives an input as integer 37, then
these temp tables should be created
#taskecf1 with fields col1 .... col15
#taskecf2 with fields col16 .... col30
#taskecf3 with fields col31 .... col37
and iam supposed to create temp tables only (not ordinary tables, i should
follow this coz, told by my project guide), after i complete this task, this
stored procedure is given to another student who will add his code to do
inserts & updates on these temp tables. hope this provides enough explanatio
n.
so i request you all to help me with this .
here is the code :
create procedure mySolution @.colCount int as
declare @.i int
declare @.currTable varchar(10)
declare @.query nvarchar(255)
set @.i = 0
while @.i <= @.colCount
begin
set @.i = @.i + 1
if (@.i % 15 = 1)
begin
set @.query = 'create table #tasksecf'+cast((@.i/15) as nvarchar(255))+'
(empid int)'
set @.currTable = '#taskecf'+cast((@.i/15) as NVARCHAR(255))
exec(@.query)
end
set @.query = 'alter table '+@.currTable+' add col'+ cast(@.i as varchar)+ '
nvarchar(255)'
exec(@.query)
end
if i execute this
exec mySolution '15'
here are the errors :
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf0' because this table does not exist in database
'MyPracticeDB'.
Server: Msg 4902, Level 16, State 1, Line 1
Cannot alter table '#taskecf1' because this table does not exist in database
'MyPracticeDB'|||i got the work done with the help of global temporary tables.
but some of you commented that it would lead to some other issues...can u
please elaborate what they are?
"ML" wrote:

> Perhaps you should tell us what are you actually trying to achieve.
> Each EXEC statement runs in a different context in which the local tempora
ry
> table created in the main procedure does not exist. Using a global table
> would work, but I would advise against that - it may lead to other issues.
>
> ML
> --
> http://milambda.blogspot.com/|||Well, its global. That means that all processes 'see' and affect those
tables. If there are two calls of your procedure at the same time, you'll be
in trouble.
MC
"kishore bondada" <kishorebondada@.discussions.microsoft.com> wrote in
message news:89E8BD16-C428-4C77-9BBB-CC2D4CDEC055@.microsoft.com...
>i got the work done with the help of global temporary tables.
> but some of you commented that it would lead to some other issues...can u
> please elaborate what they are?
> "ML" wrote:
>|||Try executing your script in two separate sessions step-by-step - i.e. creat
e
the object in the first session, then in the second, make the changes in the
first, then in the second, etc.
After each step check the state of the object (perhaps from a third
connection). You'll see what I mean.
Even this situation can be remedied by giving the objects unique names (e.g.
including SPID in the table name), but this only leads to more dynamic SQL.
Is this the purpose of this exercise?
In real-life situations the requirements for a data model are known in
advance and allow for more efficient planning. But that's another story. :)
ML
http://milambda.blogspot.com/|||i thank u all for ur valuable suggestions..
i solved the uniqueness issue of global temporary tables by using your idea
of including the @.@.SPID as part of the table name when creating the
tables...please inform me of any other issues involved
regards,
kishore
"ML" wrote:

> Try executing your script in two separate sessions step-by-step - i.e. cre
ate
> the object in the first session, then in the second, make the changes in t
he
> first, then in the second, etc.
> After each step check the state of the object (perhaps from a third
> connection). You'll see what I mean.
> Even this situation can be remedied by giving the objects unique names (e.
g.
> including SPID in the table name), but this only leads to more dynamic SQL
.
> Is this the purpose of this exercise?
> In real-life situations the requirements for a data model are known in
> advance and allow for more efficient planning. But that's another story. :
)
>
> ML
> --
> http://milambda.blogspot.com/