Solved

creating tempdb from sybase

Posted on 2010-09-16
6
721 Views
Last Modified: 2012-05-10
Hi,

I am unable to create a temporary table in the tempdb database from within a stored proc in a test database. However when I go directly to the tempdb database I am able to create temp tables. Can anyone tell me what is going wrong...

Eg:

Use my_test_db
create proc procname
as
   ----- procedure code
   create table tempdb..mytemptable1 ( col1 int null)
go
<<<<Result: Stored Proc not created>>>>


When I switch to tempdb and give the following command
create table tempdb..mytemptable1 (col1 int null)

... the table is successfully created in tempdb database.


Please help...
Thanks!

0
Comment
Question by:4Learning
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:tlovie
ID: 33695938
Have you tried the syntax like this:

create table #mytemptable1 ( col1 int null)
0
 
LVL 13

Expert Comment

by:alpmoon
ID: 33696035
What is the error message you receive?
0
 

Author Comment

by:4Learning
ID: 33696078

Yes, I did try and #mytemptable1( col1 int null) works fine. The reason I am trying to use tempdb instead of # is because I want to see the values in those tables during my development phase.... even after the stored proc has finished execution.

I am not getting any error messages. As I mentioned earlier, I am able to create tempdb tables when I directly log into tempdb, but cannot create those same tables from within a stored procedure with a create table command.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 7

Accepted Solution

by:
tlovie earned 250 total points
ID: 33696141
See, create table is going to fail if the table already exists...  What I generally do if I want to see what's inside of a temp table while developing is put a select statement at the end of the proc.

Or you can also create a permanent table either on your database or on tempdb, and use it, but in your proc, comment out the create table part.
0
 

Assisted Solution

by:4Learning
4Learning earned 0 total points
ID: 33696228
Thanks tlovie.....

I figured out the problem....... and a silly one at that :-(

The procedure was running fine, and I was checking after creating my procedures to see if the temp tables were being created... THAT was a mistake.

The tables were created after I executed my stored procedure...

Thanks to you both for your time and knowledge....

Regards,
4learning
0
 

Author Comment

by:4Learning
ID: 33851583
Solution available
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Unified and professional email signatures help maintain a consistent company brand image to the outside world. This article shows how to create an email signature in Exchange Server 2010 using a transport rule and how to overcome native limitations …
Deploying our service is a grudge match between customer benefits and customer pain. In one corner, rolling out fixes (yay!) and delivering new features (double yay!). In the other corner, training on new features (boo – sounds like work), and chan…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

751 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