Tag: Sybase

What is the difference between Stored Procedure and Trigger in SQL

In this article, we will see the difference between Stored Procedure and Trigger in SQL. Following are the main differences between Stored Procedure and Trigger:

Continue reading What is the difference between Stored Procedure and Trigger in SQL

Stored Procedure Trigger
Store Procedures are invoked explicitly Triggers are invoked implicitly
Stored procedure is a user defined piece of code which may return a value (making it a function) that is invoked by calling it explicitly. A trigger is a stored procedure that runs automatically when various events happen (eg update, insert, delete).

Difference between Stored procedure and Function in SQL

Following are the main differences between Stored procedure and Function in SQL.

Continue reading Difference between Stored procedure and Function in SQL

Stored Procedure Function
Stored procedure may or may not return values A Function must return a value
Stored procedures can have input/output parameters Functions can have only input parameters
Stored Procedure may take 0 to n input parameters For a Function it is mandatory to take one input parameter

What is the difference between union and join

Following are the differences between union and join in relational database engines.
A union returns the results of two different queries as a single recordset and A join allows you to relate similar data in different tables.

UNION JOIN
UNION puts lines from queries after each other, JOIN makes a cartesian product and subsets it
Union Operation is combined result of the Vertical Aggregate of the rows JOIN Operation is combined result of the Horizontal Aggregate of the Columns
Union combines rows JOIN links the rows
Performed only on two similar structures Performed only on two dissimilar structures also
A union returns the results of two different queries as a single recordset. A join allows you to relate similar data in different tables.

Visual depiction of a join. Table A and B’s columns are combined into a single result:

Continue reading What is the difference between union and join

How to count the number of emails per domain in PostgreSQL MySQL Oracle

In this article, we will see how to count the number of emails per domain, we can use the following query in SQL Server or PostgreSQL or MySQL or Oracle or any RDBMS:

To count the number of emails per domain in PostgreSQL MySQL Oracle:

SELECT 
    SUBSTRING(
        email, 
        CHARINDEX('@', email)+1, 
        LEN(email)-CHARINDEX('@', email)
    ) domain_name,
    COUNT(email) emails_count
FROM 
    sales.customers
GROUP BY
    SUBSTRING(
            email, 
            CHARINDEX('@', email)+1, 
            LEN(email)-CHARINDEX('@', email)
        );

Continue reading How to count the number of emails per domain in PostgreSQL MySQL Oracle

How to uninstall sybase in linux

In this article, we will see how to uninstall Sybase or ASE in Linux with console mode. Uninstall can be all features or selected features. Before uninstall, please take backup of data. Following command is used to uninstall Sybase from Linux servers.

  ./uninstall -console

Steps to uninstall:

1. Goto sap/sybuninstall/ASESuite directory and run the following command:
Continue reading How to uninstall sybase in linux

Sybase revoke permission

In this article, we are going to see how to revoke permissions from Sybase user on a table(s).

Sybase Revoke Syntax:

revoke [grant option for] 
	{all [privileges] | permission_list} 
	on {table_name [(column_list)] 
		| view_name [(column_list)] 
		| stored_procedure_name | function_name
		| keyname}
	[with {pred_name | {all |no} predicates}]
	from {public | name_list | role_list}
	[cascade]
	[granted by grantor]

Continue reading Sybase revoke permission

Sybase Ad-hoc updates to system catalogs not enabled

I have received this when tried to bcp in of the system table ‘sysusers’ in Sybase.

Server Message: LOCALHOST - Msg 10321, Level 14, State 1:
Ad-hoc updates to system catalogs not enabled. A user with System Security Officer (SSO) role must reconfigure system to allow this.

CTLIB Message:  - L1/O3/S0/N14/0/0:
blk_init(): blk layer: CT library error: Failed when CT_Lib routine ct_results() called.
blk_init failed.
bcp copy in failed

Reason:
This occurs when you try to directly modify an Adaptive Server system table without first using sp_configure to set the allow updates configuration parameter.

Solution: Continue reading Sybase Ad-hoc updates to system catalogs not enabled

Sybase database refresh step by step

In this article, we are going to see step by step procedure of Sybase database refresh.

Steps to perform refresh:

1. Prerequisites.
2. Backup and SCP
3. Load
4. Verification

Source Information:

Source Database Servername: LOCALHOST(Sybase server name)
IP: 192.168.152.130
Source Database name: r2schools

Target Information:

Tartget Database Servername: DEV01(Sybase server name)
IP: 192.168.152.135
Target Database name: r2dev
Continue reading Sybase database refresh step by step

Show all permission for a table to user in Sybase

In this article, we are going to get list of permissions granted to the user on a table in Sybase. We can get permissions on a table to user by using the stored procedure sp_helprotect.

Syntax:

sp_helprotect table_name, user_name

Continue reading Show all permission for a table to user in Sybase

How to check blocking Sybase

In this article, we have divided into 5 steps:

1. Blocking Definition
2. Query to check blocking in Sybase
3. Get the showplan(query plan) in Sybase
4. Get the SQLText of Sybase process ID.
5. How to kill the Sybase process id which is causing blocking.

1. Blocking Definition:

“Blocking” means that one connection is holding a lock on a resource when another connection wants to read or write to it.
Continue reading How to check blocking Sybase