Category Archives: Sybase

Sybase SQL and Tips and Tricks.

Call a Stored Procedure

Heres an example of where you might call a Stored Proc that takes a defined date as it’s input and does something with it. Start an iSQL session and …

go
declare @my_date datetime
select @my_date=CONVERT (datetime,’Jan 18 2011 00:00:000′)
exec myStoredProc @my_date
go

DefnCopy

Copies definitions for specified views, rules, defaults, triggers, or procedures from a database to an operating system file or from an operating system file to a database

defncopy -SmyDatabaseServer -UmyUser –PmyPassword out myStoredProc.out myDatabase myStoredProc

more info on defncopy (12.5)

You’d want to do this to perhaps drop the Stored Proc, alter it in an editor (say vim) and reload it.

CT-LIBRARY error

Occasionally you might get this error when running sql queries (especially against a “new” database)

CT-LIBRARY error:
ct_connect(): directory service layer: internal directory control layer error:

Which means that there is no details on how to connect to your database (like a tns_names.ora thing).
Short term, you can add a new interfaces file to your profile and use the –I iSQL parameter to override whats in $SYBASE/interfaces.

DBSERV01
  master tcp ether DBSERV01.systems.uk 10099
  query tcp ether DBSERV01.systems.uk 10099

isql -UmyUser -SmyDatabaseServer -DmyDatabase –PmyPassword -I/home/users/kieran/tempIfaceFile

But you’ll be wanting to get your Unix admin to update the Inferaces file to provide a permanent link.

iSQL

iSQL is the Sybase equivalent of Oracle’s SQL+ I guess. It’s handy to know how to use it as you may now have a nice Sybase GUI front end to use.

From unix try
isql -UmyUser -SmyDatabaseServer -DmyDatabase –PmyPassword
set nocount on
go
Now simply type your sql followed by “go”

This is a handy example of how to read a file of sql commands and spool the result to an output file.

isql -UmyUser -SmyDatabaseServer -DmyDatabase –PmyPassword -i imput.sql -o result.txt -w600