[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-05-01
14
Medium Priority
?
513 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:CMT Michigan
  • 6
  • 6
13 Comments
 
LVL 5

Expert Comment

by:morisce
ID: 16583784
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
 

Author Comment

by:CMT Michigan
ID: 16591439
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
 
LVL 5

Expert Comment

by:morisce
ID: 16591575
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
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 
LVL 5

Expert Comment

by:morisce
ID: 16591609
it's not necessary to build the columns list in a string. Just put  "select * ..." statement in an execute command.
0
 

Author Comment

by:CMT Michigan
ID: 16592213
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
 
LVL 5

Expert Comment

by:morisce
ID: 16595255
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
 

Author Comment

by:CMT Michigan
ID: 16598603
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
 
LVL 5

Expert Comment

by:morisce
ID: 16598893
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
 

Author Comment

by:CMT Michigan
ID: 16601475
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
 
LVL 5

Expert Comment

by:morisce
ID: 16601698
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
 

Author Comment

by:CMT Michigan
ID: 16605756
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
 

Author Comment

by:CMT Michigan
ID: 16605835
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
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 17061297
PAQed with points refunded (150)

CetusMOD
Community Support Moderator
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Applications for our next round of the Experts Exchange Scholarship Contest are starting to roll in. It made us wonder what our past winners are up to these days. Here's a look at what four winners experienced with the contest and what they're doing…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month19 days, 22 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question