When I connect to DB, always connects as tempdb

javasybasetempdb

I am trying to run a sybase DB query from a jsp session.

I can connect to the DB fine, but when I run my query, by default it always runs against tempdb.

Is there a way I can make a connection to a different DB and not by default connect to tempdb?

The aim is to be able to run a query for any table chosen by the user from a drop down list. I have the dropdown list working and can successfully select any DB. I know this is working as I am storing the DB selected in a variable and can confirm the name selected is always the name I can see in the variable.

Best Answer

It seems the DBA who has created your login has set tempdb as your default database. You may request him/her to do the change at login definition level as per this article. By default, if none of them are mentioned, user connects to master database. You may use below command:

sp_modifylogin login_name, defdb, "your_database"

Kindly note that above command needs to be run at Sybase Database level and only a System Administrator or System Security Officer can execute sp_modifylogin.

As already suggested by Markp, you can issue the command use <database_name> for changing your database. Alternatively you may use database name in the connection string itself.

Below is an example of isql for connecting to "your_database"

isql -S"Server_Name" -U"Login" -D"Your_Database" -P"Password"

You may refer below for isql options:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc30191.1570100/doc/html/san1367605044069.html

Hope above helps.

Related Solutions

PHP Sybase – What Do These Error Messages Mean

An unexpected token in TDS indicates a bad network packet was sent to the TCP Port Sybase is listening to.

This indicates one of two things:

an intermittent network connectivity problem where TCP TDS packets are being corrupted
you have some process attempting to connect to the Sybase instance without using TDS.

I've seen #2 happen with security scanning processes that are looking for open ports.

From your symptoms it sounds like a network connectivity issue. Since the Sybase Anywhere server is not on the same network segment as your client machine, I'd tend to think the client needs to gracefully handle connectivity problems.

SQL Server – Temp Tables in tempdb Not Cleaned Up by System

I believe this is to do with the plan cache. Just ran a little test on my end and I can replicate it.

To remove the table from tempdb DBCC FREEPROCCACHE works, obviously target the plan handle for the stored procedure.

You could do this but it would just come back again and freeing the cache all the time generally isn't the best idea.

If you're concerned about it taking up space you can run this query I wrote for a monitoring tool that should work. On my tests it always shows 0KB after the SP has finished. This leads me to believe it just keeps the shell of the table there for some internal use. So it seems harmless to me.

USE tempdb;
WITH KB
AS (SELECT t.name,
           SUM(a.used_pages) * 8 AS KBUsed,
           p.rows AS [Rows]
    FROM sys.tables t
        INNER JOIN sys.partitions p
            ON t.object_id = p.object_id
        INNER JOIN sys.allocation_units a
            ON a.container_id = p.partition_id
    WHERE t.is_ms_shipped = 0
    GROUP BY t.name,
             p.rows
   ) SELECT * FROM KB

Best Answer

Related Solutions

PHP Sybase – What Do These Error Messages Mean

SQL Server – Temp Tables in tempdb Not Cleaned Up by System

Related Question