Run a string to create a table 2

[jluost1]

When I run the following in Query Analyzer, I got an error:
"Invalid Object #tid_attrib_value"

---------------------------------
declare @q varchar(1000)

set @q = 'CREATE TABLE #tid_attrib_value (tid int PRIMARY KEY,[a1] varchar(255) NULL)'

exec (@q)

select * from #tid_attrib_value
---------------------------------

But when I run the following, it is OK.

-----------------------------------------
CREATE TABLE #tid_attrib_value (tid int PRIMARY KEY,[a1] varchar(255) NULL)
select * from #tid_attrib_value
----------------------------------

Can anyone tell me why?

 

[rajeevnandanmishra]

Hi jluost1,
Just a little bit theory.
Whenever you run the command batch in Query Analyzer, it checks for the syntax and the existence of the objects whose refreses are being used.
And the scope of # tables are just for the individual batch. When you run any statement in EXEC() it starts in its own batch. And as per the # table concern, it automatically deleted as soon as the batch(EXEC batch) completed.

So, in first case you are getting error because the refrenced table #tid_attrib_value does not exist at the point of executing the select statement.
While in second case it executes without an error because the temporary table is still exist.

Following batch will run without any error:

declare @q varchar(100)
select @q = "CREATE TABLE ##tid_attrib_value (tid int PRIMARY KEY,a1 varchar(255) NULL)"
exec(@q)
go
select * from ##tid_attrib_value
drop table ##tid_attrib_value

Because in this case, the ##table still exist(after the EXEC batch) and when sql server executes the select statement it picks up the reference of ##table.


Hope it helps you to move in right direction.

 

[jluost1]

Hi, rajeevnandanmishra,

Thank you for your explanation. It is very clear to me if I run the code in Query Analyzer. Now let's discuss it further, I use the code in a stored procedure. A temporary table created (using EXEC()) is available for the life of the stored procedure. Isn't it true?

I am thinking about the alternative way you give me here. It will create a global temporary table. I cann't share the temporary table with other users. Even though the temp table is dropped at the end, there will be a concurrent issue. One user may create it before it is dropped or one user may still not create it after it is dropped.

I am investigating using sp_executesql to execute my sql statement. Unfortunately, because my create table sql is actually very dynamical (uncertain number of columns and uncertain data types), it is very hard for me to convert it to Unicode (sp_executesql takes only the statement that can be implicitly converted to ntext).

I am also concern whether sp_executesql will achieve the same as EXEC().

If you or anyone seeing this can give me any idea, that will be very grateful to me.

 

[rajeevnandanmishra]

Hi jluost1,

Hmmm!
Let us see in case of Stored Procedure.
The program level can be seen as follows:
1.Procedure
1.1 EXEC(create temporary table)
1.2 SELECT (select from temporary table)
.
.
Now, whatever is created at level 1 can be used through out the procedure. But whatever you create at level 1.1 or 1.2 or 1.3 these are dropped after completion of those individual level. So, it is not possible to use Local temporary table in that way.

Now let us see about Global temporary table (GTT). GTT share the same workspace for all the users. So, if some one had created a GTT, other user will get an error if he also wants to create it and blah blah blah....

Hence, it is fruitful to create the GTT with unique names. And must be dropped after being used.

sp_executesql is having its own benefits. The measure benefit is that SQL server can take benefit of Query Optimizer.

Let us know if you face any problem.
And if you face any error while creating your

 

[jluost1]

Hi,

You are exactly right. At the level 1, I cannot use temp table that is created at level 1.1.

It's possible for me to create a temp table for each user, but it is not good for a large scale application.

I am thinking about the other alternative ways. I know they are not ideal, but I don't have choice.

Thank you very much for your help.

 

[rajeevnandanmishra]

Hi jluost1,
You are most welcome.
Please let other also know about the alternative you use for your condition.

 

[dbmsguy]

Just curiously, why do you need to use a temp table? you could make it a permenant table and just use it in a temporary fashion.

on thought that comes to mind it create it as a table and add a column for the spid. then when you use it, just include the spid in the queries. when complete, delete the data.

Paul

http://www.dbmsguy.com

 

[aalmeida]

What I've used on situations like this is:
declare @q varchar(100)
Declare @TableName varchar(15)
Declare @Locale varchar(5)
Set @Locale = Alabama
SET @TableName = SET @TableName = '##'+(SELECT @Locale + convert( varchar, @@spid ))

select @q = "CREATE TABLE "+@TableName "+ (tid int PRIMARY KEY,a1 varchar(255) NULL)"
exec(@q)

I run dynamic sql all through the sproc up to the permited level to query that GTT and I had no problem with the use of it, or sharing the table with other user since it is into the contest of the sproc. Also this is for a large application that has 1000 branches x 30 users and it is running just fine without "bottle necks".
Then my question is on sql server 7.0 sp3 what would be the performance issue that would force you to look towards other solutions?
Thanks
Al

 

[jluost1]

First, thank you all for participating this discussion and helping me.

dbmsguy is proposing a very good solution (I actually use it somewhere in my application). Unfortunately, the number of columns is uncertain, it is dynamic and it depends on other information.

aalmeida is proposing another good solution: create a GTT based on spid. Even though I may come back to try it, I have concerns now:

I am writing a Web application, which means, I always fetch data from database to the Web server with one single account(embedded in the code), for many time, within the same connection. From the browser end, different Web users request for the data; from the backend, all users get data using the same account. So, I believe SPID maynot be unique for each Web user all the time. But I do need unique set of data for each WEB user, I do need to create a temp table for each WEB user upon each request made by that user.

While you don't have this issue because you can make unique SPID for each user's request, GTT will not solve my problem since SPID may not be unique for each WEB user's request.

Right now I am making a temporary solution which is much less desirably but it is fine. I just write "CREATE TABLE" statement directly (not to put it in the string and execute the string).

To solve the uncertain number of columns, I use IF..ELSE.. statement: IF conditionA, create table with 6 columns ELSE create table with 10 columns).

Stupid? But that solves the scope issue anyway.

I have to submit now, otherwise the words I am typing now will disappear and I will have to login again and get here and retype it. This is a great site. But I hate the shortness of the Session period. You can imagine how many times I have to login. When I type in something and I get a phone call, then I come back and click, oops, it's gone (The trick is using BACK button on the browser and copy the typing into clipboard.) This is the stuff you don't want to see. Again, thank you very much.

 

[aalmeida]

From an web server or an web app you should be able to assotiate an session ID to an SPID to create an unique identifier for your connection and post that info to the sproc to creat your unique table name.