Solved

Cant run Select Into

Posted on 2000-02-14
4
382 Views
Last Modified: 2012-05-04
Hello ppl,

I am trying to do a Select Into but i encounter with an error saying :

You cant run SELECT INTO in this database. Please check with the Database Owner.

I have already use the db creator loggin, but still the same problem. I suspect its to do with some settings in the db but cant figure out what is it..

this is what i get when i do a sp_helpdb :



name                     db_size       owner                    dbid
         created
         status

 ------------------------ ------------- ------------------------ ------
        --------------
        ------------------------------------------------------------------------
------------------------------
 ist                            18.5 MB oasis                         7
         Aug 17, 1999
         no options set


(1 row affected)
 device_fragments               size          usage                free kbytes
 ------------------------------ ------------- -------------------- -----------
 master                         18.5 MB       data and log                7872
 device
         segment

 ------------------------------
        ------------------------------------------------------------------------
------------------
 master
         default

 master
         logsegment

 master
         system


please help.. its kinda urgent.
Thanks in advance

Sytan
0
Comment
Question by:sytan
[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
  • 2
4 Comments
 
LVL 2

Accepted Solution

by:
jkotek earned 20 total points
ID: 2518281
Hi Sytan,
I see, that you haven't turned the "select into" database option to true. Its false by default.
You can do that from Sybase central (r-click on database/properties) or by executing this command sp_dboption ist,"select into", true (sp_dboption <dbname>,<option>,<true/false>)
0
 
LVL 9

Expert Comment

by:david_levine
ID: 2520098
Warning! Doing this invalidates the transaction logs!! You need to be cautious when doing this.

I would only suggest setting that option in a database that does not need the transaction logs and also has the Dump Tran on Checkpoint option set. Otherwise, your logs could fill up prior to the dump of the database.

For Select Into, you could actually just change your code to create the table in the sql you are running and do an insert into instead.

David

0
 

Author Comment

by:sytan
ID: 2521447

Thanks for the answer, actually.. my tech guys manage to solve the problem hours after i post this  question, but no doubt jkotek deserve the points.. thanks anyway..

And to David, thanks for the advice, altought i would like to know more on this transaction log thinggy, as how would i know if this db requires it. Btw, i have set the select into to false already.


And again i thank you both!

sytan
0
 
LVL 9

Expert Comment

by:david_levine
ID: 2522162
The transaction log is needed for recovery. Lets say you take a database dump at midnight. You also take transaction log dumps every 30 mins. If you lose your database for some reason, you would restore the full dump from midnight and then apply the transaction logs to get you back to within 30 mins at the most of what your system was before it went down.

Without transaction logs, or should I say with the Trunc Log On Checkpoint option set, the transaction logs are cleared pretty much at the time any user transaction commits. They are still used for doing rollbacks of transactions if that is needed during the life of a user transaction. But if you have a catastrophic failure, you can only recover back to the previous nights full database dump and everything since then is lost.

Make sense?

David
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

After seeing many questions for JRNL_WRAP_ERROR for replication failure, I thought it would be useful to write this article.
Multi-threading long-running processes can have a significant increase in overall performance and drastically decrease over time it takes for a process to complete. Unfortunately, not all applications support native multi-threading, some by design a…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

705 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