Sybase ADDICT

Wonder how to display or delete duplicate records ? Sometimes, under certain circumstances duplicate data may occur. Then it should be deleted.

In this example, we are going to create a dummy table with an identity column as a unique key, and with two varchar columns. Then we are going to insert data and duplicates manually. The script for the test comes as follow:

create table dummytable(
keycolumn   numeric(5,0) identity,
column1     varchar(10),
column2     varchar(10)
)
go

insert dummytable(column1,column2) values("AAA","AAA")
go 10
(1 row affected)
10 xacts:

insert dummytable(column1,column2) values("AAA","BBB")
go
(1 row affected)

insert dummytable(column1,column2) values("BBB","BBB")
go
(1 row affected)

insert dummytable(column1,column2) values("BBB","AAA")
go
(1 row affected)

Read more…

Sybase announced a new version of its Replication Server product from its TechWave conference. Among its features is high volume adaptive replication technology that will slash the time it takes to replicate large data loads, Sybase said.
See more here

Yes, with Sybase ASE you can get a stored procedure result as rows in a table. The trick is to use proxy tables with an optional clause to the "create existing table" statement to tell ASE the remote object is actually a stored procedure, not a table.

This requires CIS to be enabled on your ASE:

1> sp_configure "enable cis",1
2> go

Make sure to add a local server to the sysservers system table by adding:

1> sp_addserver loopback, null, @@servername
2> go

Columns list and datatypes must match the description of the procedure’s result set. Consider it when you create your proxy table, otherwise you’ll get errors:

1> use tempdb
2> go
1> create existing table DBDATABASE(
2> name varchar(35),
3> db_size varchar(20),
4> owner varchar(20),
5> dbid int,
6> created varchar(35),
7> status varchar(255)
8> )
9> external procedure at "loopback.sybsystemprocs.dbo.sp_helpdb"
10> go

Then you can freely query your proxy table to display the stored procedure result set:

1> select * from DBDATABASE
2> go

Or store the result in a table for further processing later on:

1> select * into #temptable from DBDATABASE
2> go

‘group by’ behaviour has changed in ASE v15.x and now follows the ANSI standard (previous ASE versions did not). Indeed the ANSI standard does not sort the output of a ‘group by’ without an ‘order by’. Thanks to traceflag 450, you can revert back this behaviour like it was with previous ASE versions.

1°) At dataserver startup:

Start ASE with -T450 added to the startup script to enable the traceflag for all client sessions.

2°) At session level you can issue:

1> dbcc traceon(450)
2> go

or

1> set switch on 450 with override
2> go

Note: “set switch” is the replacement command for dbcc traceon.