Solved

creating tempdb from sybase

Posted on 2010-09-16
6
700 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Code not seeing ODBC driver for Sybase 1 1,059
SQL Query Syntax 11 166
sql statement error sybase 2 52
Converting some Sql server syntax to Sybase Syntax. 8 49
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Ransomware is a growing menace to anyone using a computer or mobile device. Here are answers to some common questions about this vicious new form of malware.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

825 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