Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

creating tempdb from sybase

Posted on 2010-09-16
6
Medium Priority
?
752 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
  • 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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
LVL 7

Accepted Solution

by:
tlovie earned 1000 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
The decision to migrate to the cloud is not a simple one—many factors, such a cost, ease of use, and ongoing maintenance come into play. The goal is always for cloud platforms to make storage and backups more seamless and effective. Here’s a look at…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

783 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