Sybase (with Asp.net) temp table cannot be dropped

Hi,

I am using Sybase and Asp.net.

I wish to create a temp table #Temp in a stored procedure.  The columns of this temp table will be dynamically created.  I will either use "create table" statement or I will use "select Col1, Col2.. into #Temp from .."  Then I will use a select statement to output the resultset.

I can create the table, but I can't seem able to drop it and recreate it.

I created it first time and then the second time it fails.  The error says it fails to find a column belonged to the first-time run.  Or it just gives me the result of the first time.    I even put "Drop table statement " in the beginning of my stored procedure..

What could be the reason?  Is it possible that the logon I am using to connect does not have right to drop a temp table?
CMT MichiganAsked:
Who is Participating?
 
CetusMODConnect With a Mentor Commented:
PAQed with points refunded (150)

CetusMOD
Community Support Moderator
0
 
morisceCommented:
drop table before create :

if object_id('#myTab') is not null
drop table  #myTab
select ... into #myTab from ... where ...

* I advise you not using long name (less than 20 chars)
* Becareful on the case : Sybase is case sensitif.
0
 
CMT MichiganAuthor Commented:
Thanks for the tip.  It still does not work.
 I use "declare local temporary table " syntax too, but still the table columns are fixed by the first run.  
How do we get to the tempdb database?  Are these temp tables still in the tempdb?

Thanks for any help!
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
morisceCommented:
I guess that you're using smoething like "select * from #tmp_table ..." ?

be aware that "*" is compiled and expanded at the procedure creation. If you take a glance at syscomments you will see that the first colums of your temporary table is stored isntead of "*".
You must replace "*" dynamically by the columns and execute the string with "execute" command.

0
 
morisceCommented:
it's not necessary to build the columns list in a string. Just put  "select * ..." statement in an execute command.
0
 
CMT MichiganAuthor Commented:
Thanks a lot.   It still does not work.  The following is my testing script.  I call this from asp.net and dump the result set into a datagrid.
The values of the two parameters passed are
@label = 'aa integer null,bb integer null,'
@val='aa,bb'

alter procedure dba.sp_test(in @label varchar(100),in @val varchar(100))
on exception resume
begin
  declare @mySQL varchar(1000);
  set @mySQL='create table #sss('+@label+')';
  execute immediate @mySQL;
  set @mySQL='insert into #sss('+@val+') values(1,2)';
  execute immediate @mySQL;
  execute immediate('select * from #sss'); //
  drop table #sss
end

If I use execute immediate('select * from #sss'), i hit an error on my web page.  If I used "select * from #sss", I got the result in the grid but cannot drop the table ..
0
 
morisceCommented:
it's strange !
If you try executing the prcoedure out of Asp.net (from sql advantage or isql), do you hit an error ? and what is the error message ?
0
 
CMT MichiganAuthor Commented:
Hi,
I am very new to Sybase, so I am not sure if these info helps.  I am using Sybase Central (Active Server Anywhere 6.07).  I tested in Interactive SQL...
0
 
morisceCommented:
Truly , I did never used ASA. Even so, I suppose all Sybase severs share the main skills.
What is the error returned in Interactive SQL ?
0
 
CMT MichiganAuthor Commented:
This is another part of the puzzle. Interactive SQL is not excuting the procedure as conveniently as things like SQL Server Manager from my personla point.  I might be not using it correctly.  

When I executed the sp with parameters, we cannot seem to execute the sp like we do in SQLServer "exe spr-name "", "", ".  I actually executed it by right clicking the "Test it in Interactive SQL" option.  Then it pulled out 3 panes.  The lower one shows all the steps in executing it.  The parameters will apper in separate lines like "@par1=''".  Then I enter the value into the single quotes and press continue..

Then it pulled out the result set.  It is fine for the first run. Then I close the Interactive SQL completely and try to test it in Interactive SQL again.  After the first time if I enter different values for the parameters (such as aa and bb columns for 1st time, cc and dd columns the second time), it erros out with message it cannot find aa column when I run 2nd time.  It seems that it does not drop that temp table and still want to map the columns to it..

Thanks for all the tips!!
0
 
morisceCommented:
The server seem still working with the old procedure code.
* If the temporary table isn't dropped, the "create table .." line must return error like "con not create existing table"
* I think you have a refreshing problem of your procedure in the server.
Some thought :
- check you don't use any "*" in all your procedure code
- instead of "alter procedure...", do "drop procedure ... create procedre"
- drop the temporary table before create it :
if object_id('#sss') is not null
begin
set @mySQL='drop table #sss';
  execute immediate @mySQL;
end
- create another procedure using another temporary table #ttt, just to see clearly ...
- it may be a confusion on several databases you are using
0
 
CMT MichiganAuthor Commented:
Hi,

I simplified the stored procedure completely and made it only run a dynamic statement from a table (Testing_Table).

alter procedure dba.sp_drs_fpr_chu_ss()
on exception resume
begin
  declare @mSQL varchar(100);
  set @mSQL='select AA, BB from Testing_Table;
  execute immediate @mSQL
end

In both the Interactive SQL and the ASP.NET pages, I got the error "the result set returns more than one row".  ASP.NET has more details: DBODBC6.DLLSystem.Data.Odbc.OdbcException: ERROR [HY000] [Sybase][ODBC Driver][Adaptive Server Anywhere]General error: SELECT returns more than one row..

The Sybase Central and the ASP.NET that I use are using both ODBC connection to our database server.  I started to wonder if this ODBC driver (Active Server Anywhere 6.xx) has a limitation or bug..??

Thanks so much for any tip.

0
 
CMT MichiganAuthor Commented:
I forgot to mention.

If I put "select AA, BB from testing_table" directly, instead of executing a string, it is fine without any error.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.