How to set SQL Server Database Option

Dear Reader,...
Sometimes we need to set SQL Server database option because there is error related on it based on purpose. In this article, we will discuss about how to SQL Server Database Option.

For the first time, you need to query the options of database as below

SELECT
    database_id,
    name, 
    is_ansi_nulls_on,
    is_ansi_padding_on,
    is_ansi_warnings_on,
    is_arithabort_on,
    is_concat_null_yields_null_on,
    is_numeric_roundabort_on,
    is_quoted_identifier_on
FROM sys.databases
where name='[database_name]'

You will get the result of every options that queried. To change the value of options, simply execute the script below

ALTER DATABASE [database_name] SET ANSI_NULLS ON;
GO
ALTER DATABASE [database_name] SET ANSI_PADDING ON;
GO
ALTER DATABASE [database_name] SET ANSI_WARNINGS ON;
GO
ALTER DATABASE [database_name] SET ARITHABORT ON;
GO
ALTER DATABASE [database_name] SET QUOTED_IDENTIFIER ON;
GO
ALTER DATABASE [database_name] SET CONCAT_NULL_YIELDS_NULL ON;
GO
ALTER DATABASE [database_name] SET NUMERIC_ROUNDABORT OFF;
GO

How to query data with date in mssql

It come from simple question. what is the syntax for query data with date/time filter in ms access?.

Here we will show you how to do that. It simply using query like example below :

SELECT SNo
FROM Staff
WHERE Format([ComplaintDate],"yyyy-mm-dd")>='2014-08-10'

How to create database link in oracle

What is database link?

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. 

How to create a database link in Oracle? 

To create database link you must specify the database link name, the remote user to connect to, the password for the remote user and the TNS service name for the database link connection. Below are the command to create database link in oracle.

create public database link mylink connect to remote_username identified by mypassword using 'tns_service_name';

How to access object in database link

To access object in database link you can use @link_name as query below :

SELECT * FROM employees@local;

for more information about oracle database link, you can find in oracle docs page. 

 

How to resolve error TNS:listener does not currently know of service requested in connect descriptor

This time we would like to look at one of problem in oracle, especially “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”.

The vast majority of the time, this error results from an incorrectly specified connect descriptor in the tnsnames.ora file. Let’s look at a very typical example then diagnose and fix it. After that we will dig in to how the listener comes to know of a service name.

For the first we need to analyze why the error occured by starting listener.

[oracle@vmlnx01 admin]$ lsnrctl start
 
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 16-MAR-2011 18:44:49
 
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
 
Starting /ora00/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Log messages written to /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                16-MAR-2011 18:44:49
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
The listener supports no services
The command completed successfully

It said that The listener supports no services. It means that the listener doesn't have any associates service. So the solution is we need to add a service to listener.ora as example below :

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME=vlnxora1)
      (ORACLE_HOME = /ora00/app/oracle/product/10.2.0/db_1)
      (SID_NAME = vlnxora1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01.vmdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

Restart listener by command lsnrctl stop followed by lsnrctl start. Now you can try login using your oracle user e.g : sqlplus scott@vlnxora1

If you get this problem in your oracle client installation, please make sure that your service name is configured correctly. To check that, do the following steps :

1. Edit tnsnames.ora
2. Make sure that service name in connect descriptor is same with oracle database service name.
3. To check oracle database service name, login to your oracle database server and execute the query below : 

   sqlplus / as sysdba
   select value from v$parameter where name='service_names';
   show parameter service_names;

4. To change oracle service name you can use alter

   ALTER SYSTEM SET SERVICE_NAMES='application_a.your.domain','application_b.your.domain' SCOPE=BOTH;

How to reconfigure oracle dbconsole

How to reconfigure oracle dbconsole

If the IP address and/or hostname changes for a system running Oracle, we need to reconfigure oracle dbconsole correctly.
This post will discuss about  How to reconfigure oracle dbconsole :

1. drop oracle dbconsole repository
   cd $ORACLE_HOME/bin ./emca -deconfig dbcontrol db -repos drop
2. recreate oracle dbconsole repository
   ./emca -config dbcontrol db -repos create
3. shutdown oracle database
   sqlplus sys@blabla as sysdba
   sql > shutdown immediate
4. startup oracle database
   sql > startup

How to resolve too many declarations of 'TO_CHAR' match this call

Have you ever got message
"ORA-06550: line 3, column 9:
PLS-00307: too many declarations of 'TO_CHAR' match this call
ORA-06550: line 3, column 2:
PL/SQL: SQL Statement ignored" ? 

If so, it is the right place for you :). This post will discuss about "How to resolve too many declarations of 'TO_CHAR' match this call" in oracle.

The problem is that your pl/sql is selecting something like '0' and returning a string. The result is that you are attempting to perform a to_char function on a char variable.

we can solve it by either remove the single quotes from the inline view

DECLARE

CURSOR c1 IS
SELECT TO_CHAR(NVL(a.foo,'0'),'999')
FROM
(SELECT 0 foo FROM dual) a;
BEGIN
NULL;

END;

or
convert a.foo to a number via the to_number function:

DECLARE

CURSOR c1 IS
SELECT TO_CHAR(NVL(TO_NUMBER(a.foo),'0'),'999')
FROM
(SELECT '0' foo FROM dual) a;
BEGIN
NULL;

END;

That's all from us, hope this help.

How to create tablespace in oracle

There are some types of tablespace in oracle. This post will discuss about how to create tablespace in oracle using syntax.

1. Permanent Tablespace
   
   create tablespace ts_test
   logging
   datafile '/datafiles/ts_test.dbf'
   size 32m
   autoextend on
   next 32m maxsize 2048m
   extent management local;
2. Temporary Tablespace
   
   create temporary tablespace temp_test
   tempfile '/datafiles/temp_test.dbf'
   size 32m
   autoextend on
   next 32m maxsize 2048m
   extent management local;
3. Undo Tablespace
   
   create undo tablespace ts_undo
   datafile '/datafiles/undo.dbf'
   size 100M;
4. More than one datafile
   
   create tablespace ts_test
   datafile '/datafiles/test_01.dbf' size 4M autoextend off,
   '/datafiles/test_02.dbf' size 4M autoextend off,
   '/datafiles/test_03.dbf' size 4M autoextend off
   logging
   extent management local;

How to resolve error Msg 468 : Cannot resolve collation conflict in SQL Server

If you've ever worked with SQL Server you've probably encountered an error like this: 

Msg 468, Level 16, State 9, Line 1
Cannot resolve collation conflict between 'Latin1_General_CI_AS' and 'SQL_Latin1_General_CP1_CI_AS' in equal to operation.

This error is cause by having different collation settings for the columns in the comparison. SQL Server cannot reliably resolve how the columns should be compared to determine if they meet your conditions. For example, when you compare two columns that are both case-sensitive (Latin1_General_CS_AS) then 'AA' = 'AA', but 'Aa' != 'AA'. On a case-insensitive collation 'Aa' = 'AA'.
Where code pages change, or the case or accent sensitivity changes, SQL Server may require you to specify which collation is to be used during comparison.

In this case the error was caused by a comparison of two columns in a join clause where the source tables were from databases with different collations.
Resolving the error is as simple as adding the COLLATE codepage keyword after the column. You can figure out which is the offending column and change its collation, or change both columns to the same collation.

-- Example COLLATE Fix for a JOIN condition
SELECT
   TableA.col1,
   TableB.col2
FROM TableA
INNER JOIN TableB
ON TableA.col3  COLLATE Latin1_General_CI_AS = TableB.col3 COLLATE Latin1_General_CI_AS

-- Example COLLATE Fix for a WHERE condition
SELECT
   TableA.col1
FROM DatabaseA.dbo.TableA, dbo.TableB
WHERE TableA.col3 COLLATE Latin1_General_CI_AS = Max(TableB.col2)


-- Example COLLATE Fix for a WHERE IN condition
SELECT
   TableA.col1
FROM TableA
WHERE TableA.col3 COLLATE Latin1_General_CI_AS IN (SELECT TableB.col1 FROM TableB)

You can check the collation setting for a column using the sp_help TableName command.

How to convert date to char in oracle

In every dbms, conversion is something that almost always happen. In oracle, if we want to convert date to char we can use to_char() function. otherwise, if we want to convert char to date we can use to_date() function.
Below is the sample to_char() and to_date() usage in oracle :

select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual; select to_date('29/03/2012 08:18:45', 'DD/MM/YYYY HH24:MI:SS') from dual;

The first result will be the current date time of your database server
The second result will be '29/03/2012 08:18:45'.

How to locate string in AdoQuery

Sometime for some reasons, we need to locate some values from adoquery result. Below is the function to do that :

if AdoQuery.Locate('NAME', 'jhon', [loPartialKey, loCaseSensitive]) then
Showmessage('Data is found')
else
Showmessage('Data not found');

If you want to locate in more than 1 field you can use ';' as code below :

AdoQuery.Locate('Name;Address',VarArrayOf(['jhon','Arizona']),[]);

The last parameter is optional. You can leave it blank of fill it with 'loPartialKey' or 'loCaseSensitive' as your need.

mssql_connect(): unable to connect to server in xampp

xampp

Have you ever get such error when trying to access mssql server in another server except localhost with php using xampp??..
If so you need to this step below :

1. download ntwdblib.dll.zip
2. extract that file
3. copy ntwdlib.dll to both folders 'xampp/php' and 'xampp/apache/bin'
4. restart your apache web server

How to resolve #Error Code : 1235 in Mysql

error:1235 mysql

Have you ever got '#Error Code : 1235 This version of MySQL doesn't yet support multiple triggers with the same action time and event for one table'??. If so, this post will discuss about it.
This error:1235 occurs because of executing trigger update in the same table of trigger. For example, as in this code below :

DELIMITER $$
DROP TRIGGER IF EXISTS bfr_upd_billing$$
CREATE TRIGGER bfr_upd_billing
BEFORE UPDATE ON billing FOR EACH ROW
BEGIN
    update billing set flg_sync = '0' where billing_no = NEW.BILLING_NO;  
END$$
DELIMITER ;

In that code we will get #error:1235 because we want to update a field in table 'billing', whereas we create trigger on that table.
To resolve this error, we need to fix this code like below :

DELIMITER $$
DROP TRIGGER IF EXISTS bfr_upd_billing$$
CREATE TRIGGER bfr_upd_billing
BEFORE UPDATE ON billing FOR EACH ROW
BEGIN
  SET NEW.FLG_SYNC='0';  
END$$
DELIMITER ;

Well, actually we need not to run update again on trigger body.

How to create trigger on mysql

mysql

Trigger is a procedure that automatically run when the trigger was invoked. For example: deleting data on a table will trigger a trigger to delete data on other tables. Below is an example of using triggers in MySQL database :

delimiter $$ 
create trigger auto_insert_test2
before insert on test for each row
begin
  insert into test2 (test_code, test_name) values (NEW.test_code,NEW.test_name);
end$$
 
create trigger auto_update_test2
before update on test for each row
begin
  update test2 set test_name=NEW.test_name where test_code=NEW.test_code;
end$$
 
create trigger auto_delete_test2
before delete on test for each row
begin
delete from test2 where test_code=OLD.test_code;
delete from trans where trans_test_code=OLD.test_code;
end$$
delimiter ;

In this code, it's assumed that we have 3 tables : test, test2 and trans.

How to update antivirus ess 4

Here is the steps to update antivirus database Eset Smart Security (ESS) version 4 :

1. Open ESS Window
2. Press F5, Advanced Setup window will appear
3. Click update in the left menu pane
4. Click button edit on Update server in the right pane
5. Insert directory where offline update has been extracted
6. Click button Add
7. Press button OK

Now you can update your antivirus using offline updater

Get value from trigger in mssql

This code below will show you about how to get value from trigger being executed :

CREATE TRIGGER aft_upd_mst
ON dbo.[table_name]
AFTER UPDATE
AS
DECLARE @ID int, @newValue nvarchar(30), @oldValue nvarchar(30)
IF (SELECT ID FROM inserted) <> (SELECT ID FROM deleted)
RAISERROR ('You are not allowed to change primary key field', 10,1)
ELSE
BEGIN
--set local variables
SET @ID = (SELECT ID FROM inserted)
SET @newValue = (SELECT [field_name] FROM inserted)
SET @oldName = (SELECT [field_name] FROM deleted)

--write to table
UPDATE [table_name] SET [field_name] = @newValue WHERE ID = @ID
-- write to archive
INSERT Log (type, ID, newValue, oldValue) VALUES('UPDATE', @ID, @newValue, @oldValue)
END
GO

Notes
Change [field_name] and [table_name] with your own
Inserted is same with new in oracle trigger
Deleted is same with old in oracle trigger

How to update some records in mssql

In certain conditions, we want to update some records, not all data. Suppose we want to update the first 100 data from a query. How to do it in MSSQL??.
The following query should be executed:

update top (100) table1 set field1 = 1

Notes
Make sure you replace "table1" with the actual table name.
And replace the field1, with your column names.

How to export query result in oracle

In may times, we often want to export the query results in the oracle into a text file with a specific format. In this blog we will discuss about how to export query results in the oracle into a text file.
To do so run the following script:

set head off 
spool c:\myoracle.txt
select field1||', '||field2||', '||field3 from my_table;
spool off
set head on

Notes :
This script will create a comma delimited file named: c:\myoracle.txt
Make sure you replace "my_table" with the actual table name.
And replace the field1, field2, with your column names.

How to trim on mssql

Trim is a standard function which is used by many programming languages to remove the space character in a string. For example ' Hello world ', with trim function, it will be 'Hello world'.

In SQL Server, trim function is not provided by default. We need to do a certain trick to remove the spaces. we can use LTRIM and RTRIM function together.

Here's an example to remove spaces in SQL Server:

select LTRIM(RTRIM(' Hello world '))

How to grant all user object privilege

In oracle, each user/schema has some objects. They can be tables, views, procedures, functions or packages. By default a user can not access other objects owned by other user, unless after the owner gives that user some objects privileges.

Granting objects privileges to other user in oracle can by done one by one. If a user has 100 objects for example, he must run a script 100 times to give the whole object. But there are tricks he can do to overcome it.

To give permissions all objects owned by a schema, perform the following steps:

1. login as object owner
2. run script below
   

   begin
   for i in (select object_name from user_objects where object_type in ('TABLE','VIEW'))
   LOOP
   execute immediate 'grant select on '|| i.object_name||' to bb';
   end loop;
   end;
   /
   

Microsoft SQL Server 2008 R2 Ebook

Microsoft released a new ebook about Microsoft SQL Server 2008, written by Ross Mistry and Stacia Misner. This free ebook contains 10 chapters and 216 pages and we can download the PDF format here