sybase

Avoiding TSQL Data-conversion errors

I think this is best asked in the form of a simple example. The following chunk of SQL causes a "DB-Library Error:20049 Severity:4 Message:Data-conversion resulted in overflow" message, but how come? declare @a numeric(18,6), @b numeric(18,6), @c numeric(18,6) select @a = 1.000000, @b = 1.000000, @c = 1.000000 select @a/(@b/@c) go H...

How can I get data from a stored procedure into a temp table ?

Am working on sybase ASE 15. Looking for something like this Select * into #tmp exec my_stp; my_stp returns 10 data rows with two columns in each row. ...

Row number in Sybase tables

Sybase db tables do not have a concept of self updating row numbers. However , for one of the modules , I require the presence of rownumber corresponding to each row in the database such that max(Column) would always tell me the number of rows in the table. I thought I'll introduce an int column and keep updating this column to keep tra...

Testing for the existence of a temporary table in a multi tempdb environment?

Is there any way of determining whether or not a specific temp table has been created in a session without referencing the tempdb database that it was created on? Users are allocated to a specific tempdb when they log in, so I don't know which tempdb they'll be using. I don't need to specify a tempdb to select data out of the temp table...

Is there any way of reducing the dump file size of a Sybase database?

When I dump a Sybase database, it doesn't seem to matter whether there's data in the tables or not, the file size is the same. I've been told that this is down to the fact that my dump file is binary and not logical, so the file of the dump file is based on the allocated size of the database. I know that Oracle can use logical dump files...

Is it safe to use @@identity with replication enabled on Sybase ASE 15.

I am using @@identity to fetch recently updated identity column. Am not sure if this presents any problem when used on a SERVER with replication enabled. Tried googling but no luck. Can someone explain me how this works? http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.pb_10.5.connpb/html/connpb/connpb65.htm ...

Sybase Developer Asks: How To Create a Temporary Table in Oracle?

I'm familiar with Sybase / SQL server, where I can create a temp. table like this: SELECT * INTO #temp FROM tab1 , tab2 WHERE tab1.key = tab2.fkey SELECT * FROM #temp WHERE field1 = 'value' #temp only exists for the duration of this session, and can only be seen by me. I would like to do a similar thing in Ora...

what's the best way to implement logging during transactions in T-SQL?

I want to implement a simple debug log, which consists of a table into which I insert insightful messages about the current state of a transaction. How do I stop inserts to this table being affected by rollbacks on the transaction that I'm trying to debug? In Oracle I can use PRAGMA AUTONOMOUS_TRANSACTION to ensure that the inserts are d...

How to combine variable assignment with data-retrieval operations in T-SQL

Just to clarify, I'm running Sybase 12.5.3, but I am lead to believe that this holds true for SQL Server 2005 too. Basically, I'm trying to write a query that looks a little like this, I've simplified it as much as possible to highlight the problem: DECLARE @a int, @b int, @c int SELECT @a = huzzah.a ,@b = huzzah.b ,@c = ...

How to find which character set is used by the database

Hi, I can access the database either from a .NET program (using ODBC) or through a database management tool (written in Java). If I write a 'é' character to the database from the .NET program, it appears as 'Õ' (capital O with tilde) in the DB management tool. If I write a 'é' character to the database from the DB management tool, it ...

Find Sybase stored procedure in db given a text string that appears in the proc

How do I find a stored procedure in a Sybase database given a text string that appears somewhere in the proc? I want to see if any other proc in the db has similar logic to the one I'm looking at, and I think I have a pretty unique search string (literal) Edit: I'm using Sybase version 11.2 ...

How To Find the Locale in Sybase?

I have some servers in Europe and some in Asia. I would like to be able to work out where the current server is by querying ... something. Is there some global variable I can query or sp_xxx I can execute to find out the locale of the server? ...

SQL Conversion

I want to port a project from sybase to oracle. I need to port the tables scripts (around 30) and some data in meta data tables(100 rows 2/3 tables). What will the best tool for this work? ...

Can you use Sybase.Data.ASEClient.dll .Net Provider with SSIS?

I am trying to create a data connection in SSIS. I have the Sybase.Data.ASEClient.dll version 1.1.680 installed but this does not show as a .Net Provider. Is it possible to use this ADO.NET provider with SSIS and if so how can I make it accessible? ...

How do you minimise the number of auxiliary scan descriptors for a table in T-SQL?

I've recently seen occasional problems with stored procedures on a legacy system which displays error messages like this: Server Message: Number 10901, Severity 17: This query requires X auxiliary scan descriptors but currently there are only Y auxiliary scan descriptors available. Either raise the value of the 'number o...

Why doesn't DBD::Sybase's Makefile.PL find freetds?

I'm having trouble trying to install DBD::Sybase on Redhat. Perl version is 5.8.8 and it is the 64-bit version. When I set the SYBASE env variable to the freetds libs in /usr (the rpm puts libtds in /usr/lib64), it gives the following error message $ perl ./Makefile.PL Can't find any Sybase libraries in /usr/lib at ./Makefile.PL line ...

How do I verify the integrity of a Sybase dump file, without trying to load it?

Here's the scenario - a client uploads a Sybase dump file to (gzipped) to our local FTP server. We have an automated process which picks these up and then moves them to different server within the network where the database server resides. Unfortunately, this transfer is over a WAN, which for large files takes a long time, and sometimes ...

How best can I isolate my application from an unreliable database?

I have a Java SOAP data service which sits on top of a Sybase database which, for reasons out of my control, has unreliable performance. The database is part of a vendor package which has been modified by an internal team and most of the issues are caused by slow response times at certain times of the day. The SOAP service provides dat...

How do I get LongVarchar out param from SPROC in ADO.NET 2.0 with SQLAnywhere 10?

Hi All, I have sproc 'up_selfassessform_view' which has the following parameters: in ai_eqidentkey SYSKEY in ai_acidentkey SYSKEY out as_eqcomments TEXT_STRING out as_acexplanation TEXT_STRING  -  which are domain objects - SYSKEY is 'integer' and TEXT_STRING is 'long varchar'. I can call the sproc fine from iSQL using the following...

How do I conditionally create a table in Sybase (TSQL)?

OK, so Sybase (12.5.4) will let me do the following to DROP a table if it already exists: IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'a_table' AND type = 'U' ) DROP TABLE a_table GO But if I try to do the same with table creation, I always get warned that the table already exists, because it went ahead and tried...