[Database][SQLServer] SQL Server Management Studio Not Appear

Description 
        this problem appear because some of component was not correctly installed.

HowTo
       1. Run SQLServer setup.
       2. When install the component of SQLServer make sure you check all the checkbox. including "workstation component", books online and development tools".

         3. After check all box click advanced to make sure that management tools feature will be install.

             4. Now follow the step normally until you finish the installation.

             5. After install check on START > MICROSOFT SQL SERVER XXXX > SQL SERVER MANAGEMENT STUDIO

Read more

[Database][SQLServer] Backup - Full Database

Description

         Performing Backup - Full database in SQLServer .

How To.
1. Open SQLServer Management Studio
2. After you connect database , select database (highlight) that you want to backup.
3. Right click the database and select Tasks > Backup (see the picture below)

Right click on database

4. it will open a new form with many setting , here's the step :
      - Step-1 : look on the left corner , on page menu select "General"
      - Step-2 : Make sure you select right DB
      - Step-3 : Select backup type FULL
      - Step-4 : Give The sets of name (it will appear during restore database)
      - Step-5 : Set the expired to zero / 0 , it means "never expired"
      - Step-6 : Remove Current Save Path (by default the path is in current directory SQLServer).
      - Step-7 : Add new save path , don't forget add extention .BAK on the filename.
      - Step-8 : look again on the left corner , on page menu select "Options"

    for fast understanding you can see picture below

 Backup Option - General (SQLServer)

5. On page "Options" , look on the box picture below , the settings depend what you need.

    - Append to existing backup database , it usually use by the first time backup the database , if you have an existing database with same name, the data won't overwrite but will add.

    - Overwrite all exiting backup sets , it will overwrite all of data include all structure. the old data that overwrite will be lost.

   

 Backup Option - Options (SQLServer)

that's all , backup database - full on SQLServer

Read more

[Database][SQLServer] Cannot call methods on void type.

Description
     Null cannot call method, it usually typo from user.


How To
     Just fixed the query, example typo query maybe like this :

     NULL.ALIAS.FIELD

    Just Fixed to : NULL , ALIAS.FIELD

    no DOT Character  in front of NULL.

Read more

[Database][SQL Server] Log file filesize too big

Deskripsi
     i not recommend use shrink but if it urgent you can use it because it effect reduce perfomance to database.

How to 
1. run query , to get physical name of files data or log.
select name from dbname.sys.database_files where type=1

2. to use shrink use this 

Backup log dbname with truncate_only

go

DBCC shrinkfile('logicalfilename',100)

but it still not recommend use shrink , you must give a limitation of the autogrowth file data / log !!

Read more

[Database][Sql Server 2005] Determine free space

Deskripsi
     Get all Disk Space , Freespace , Filename , Filetype . Drive , Physical Path database file.

How To

Source taken from This website, just run query below :

USE MASTER

GO

CREATE TABLE #TMPFIXEDDRIVES (
  DRIVE  CHAR(1),
  MBFREE INT)

INSERT INTO #TMPFIXEDDRIVES
EXEC xp_FIXEDDRIVES

CREATE TABLE #TMPSPACEUSED (
  DBNAME    VARCHAR(50),
  FILENME   VARCHAR(50),
  SPACEUSED FLOAT)

INSERT INTO #TMPSPACEUSED
EXEC( 'sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme,
fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')

SELECT   C.DRIVE,
         CASE
           WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
           ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
         END AS DISKSPACEFREE,
         A.NAME AS DATABASENAME,
         B.NAME AS FILENAME,
         CASE B.TYPE
           WHEN 0 THEN 'DATA'
           ELSE TYPE_DESC
         END AS FILETYPE,
         CASE
           WHEN (B.SIZE * 8 / 1024.0) > 1000
           THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
           ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
         END AS FILESIZE,
         CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE,
         B.PHYSICAL_NAME
FROM     SYS.DATABASES A
         JOIN SYS.MASTER_FILES B
           ON A.DATABASE_ID = B.DATABASE_ID
         JOIN #TMPFIXEDDRIVES C
           ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE
         JOIN #TMPSPACEUSED D
           ON A.NAME = D.DBNAME
              AND B.NAME = D.FILENME
ORDER BY DISKSPACEFREE,
         SPACEFREE DESC
       
DROP TABLE #TMPFIXEDDRIVES

DROP TABLE #TMPSPACEUSED

Read more

[Database][Sql Server] Time out expired

Deskripsi
      the cause is still unknown , may be crash because large of data or the false setting or bugs. but mostly come from crash.

HowTo

Step 1 : Set Execution Time-Out
1) Selected Tools -> Options
2) Expanded the "Query Execution" node
3) Clicked on "SQL Server"
4) Set value for "Execution time-out" from 0 to 1800 (30 Minutes Query Timeout)

Step 2 : Set Transaction Time-Out
1) Selected Tools -> Options
2) Expanded the "Designer" node
3) Clicked on "Table and Database Designer"
4) Uncheck Override Connection String Time-Out Value
5) Check again Override Connection String Time-Out Value
6) Set value for "Transaction time-out after" from 30 to 1800 (30 Minutes Query Timeout)

Step 3 : Restart SQL Server to apply settings (just Stop and Start Again)

Step 4 : Test you table by viewing your data. if success then done , but if error still exist go to step 5

Step 5 : Open your Database , expand and find the table which problematic , left click and find REFRESH tools near summary tag. if still exist go to step 6.

Step 6 : i don't know .....

Read more

[Database][Oracle] Operator Query

Deskripsi
      Untuk mengetahui selak beluk query pada database oracle. Content Summarize from Oracle.com


* Level Priority (semakin kecil semakin didahulukan)

level 1	Deskripsi Unary + - arithmetic operators, PRIOR operator
2	* / arithmetic operators
3	Binary + - arithmetic operators, || character operators
4	All comparison operators
5	NOT logical operator
6	AND logical operator
7	OR logical operator

** Arithmetic Operators

Operator	Description	Example
+ (unary)	Makes operand positive	SELECT +3 FROM DUAL;
- (unary)	Negates operand	SELECT -4 FROM DUAL;
/	Division (numbers and dates)	SELECT SAL / 10 FROM EMP;
*	Multiplication	SELECT SAL * 5 FROM EMP;
+	Addition (numbers and dates)	SELECT SAL + 200 FROM EMP;
-	Subtraction (numbers and dates)	SELECT SAL - 100 FROM EMP;

** Character Operator

Operator	Description	Example
||	Concatenates character strings	SELECT 'Your name: ' || ENAME FROM EMP;

** Comparison Operator

Comparison operators are used in conditions that compare one expression with another. The result of a comparison can be TRUE, FALSE, or UNKNOWN.

Operator	Description	Example
=	Equality test.	SELECT ENAME "Employee" FROM EMP WHERE SAL = 1500;
!=, ^=, <>	Inequality test.	SELECT ENAME FROM EMP WHERE SAL ^= 5000;
>	Greater than test.	SELECT ENAME "Employee", JOB "Title" FROM EMP WHERE SAL > 3000;
<	Less than test.	SELECT * FROM PRICE WHERE MINPRICE < 30;
>=	Greater than or equal to test.	SELECT * FROM PRICE WHERE MINPRICE >= 20;
<=	Less than or equal to test.	SELECT ENAME FROM EMP WHERE SAL <= 1500;
IN	"Equivalent to any member of" test. Equivalent to "= ANY".	SELECT * FROM EMP WHERE ENAME IN ('SMITH', 'WARD');
ANY/ SOME	Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluates to FALSE if the query returns no rows.	SELECT * FROM DEPT WHERE LOC = SOME ('NEW YORK','DALLAS');
NOT IN	Equivalent to "!= ANY". Evaluates to FALSE if any member of the set is NULL.	SELECT * FROM DEPT WHERE LOC NOT IN ('NEW YORK', 'DALLAS');
ALL	Compares a value with every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, or >=. Evaluates to TRUE if the query returns no rows.	SELECT * FROM emp WHERE sal >= ALL (1400, 3000);
[NOT] BETWEEN x and y	[Not] greater than or equal to x and less than or equal to y.	SELECT ENAME, JOB FROM EMP WHERE SAL BETWEEN 3000 AND 5000;
EXISTS	TRUE if a sub-query returns at least one row.	SELECT * FROM EMP WHERE EXISTS (SELECT ENAME FROM EMP WHERE MGR IS NULL);
x [NOT] LIKE y[ESCAPE z]	TRUE if x does [not] match the pattern y. Within y, the character "%" matches any string of zero or more characters except null. The character "_" matches any single character. Any character following ESCAPE is interpretted litteraly, useful when y contains a percent (%) or underscore (_).	SELECT * FROM EMP WHERE ENAME LIKE '%E%';
IS [NOT] NULL	Tests for nulls. This is the only operator that should be used to test for nulls.	SELECT * FROM EMP WHERE COMM IS NOT NULL AND SAL > 1500;

   


** Logical Operator

Operator	Description	Example
NOT	Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.	SELECT * FROM EMP WHERE NOT (job IS NULL)SELECT * FROM EMP WHERE NOT (sal BETWEEN 1000 AND 2000)
AND	Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE; otherwise returns UNKNOWN.	SELECT * FROM EMP WHERE job='CLERK' AND deptno=10
OR	Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise, returns UNKNOWN.	SELECT * FROM emp WHERE job='CLERK' OR deptno=10

** Set Operator

Operator	Description	Example
UNION	Returns all distinct rows selected by either query.	SELECT * FROM(SELECT ENAME FROM EMP WHERE JOB = 'CLERK' UNION SELECT ENAME FROM EMP WHERE JOB = 'ANALYST');
UNION ALL	Returns all rows selected by either query, including all duplicates.	SELECT * FROM(SELECT SAL FROM EMP WHERE JOB = 'CLERK' UNION SELECT SAL FROM EMP WHERE JOB = 'ANALYST');
INTERSECT and INTERSECT ALL	Returns all distinct rows selected by both queries.	SELECT * FROM orders_list1INTERSECT SELECT * FROM orders_list2
MINUS	Returns all distinct rows selected by the first query but not the second.	SELECT * FROM (SELECT SAL FROM EMP WHERE JOB = 'PRESIDENT'MINUS SELECT SAL FROM EMP WHERE JOB = 'MANAGER');

** Other Operator

Operator	Description	Example
(+)	Indicates that the preceding column is the outer join column in a join.	SELECT ENAME, DNAME FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO (+);
PRIOR	Evaluates the following expression for the parent row of the current row in a hierarchical, or tree-structured query. In such a query, you must use this operator in the CONNECT BY clause to define the relationship between the parent and child rows.	SELECT EMPNO, ENAME, MGR FROM EMP CONNECT BY PRIOR EMPNO = MGR;

Read more

[Database][Oracle] Union with Order By

Deskripsi
      sometimes we get confused at the time of the union query with sort order and always ended with "invalid identifier" , where's the problem ?? the problem oracle doesn't know about the field_name. what the solution ?? to identify the field union when using order by just use column index. 

Howto
case :
1. i have the table with different column format, but there's 2 same field and i want to union
2. i want order by y and z column

Column table A : x,y,z
Column table B : s,y,z

the solution
select *
from
(
select x,y,z
from A
union all -- or union
selecy null,y,z -- null means fake column
from B
) data --aliases
order by 2,3 ;
-- 2 and 3 is index column from left : x(1) , y(2) , z(3) .


if you want still use field_name , add it with alias (recommended)
select *
from
(
select x as x_one,
         y as y_two,
         z as z_three
from A
union all -- or union
selecy null,y,z -- null means fake column
from B
) data --aliases
order by data.y_two,data.z_three ;

Read more

[Database][Mysql/SQL Server/Oracle] Output Query format Date

Deskripsi
      Berikut adalah cara dalam mendapatkan format output date dari 3 database yakni mysql , oracle , dan Sqlserver, menyebalkan memang jika berhubungan dengan date pada saat query ~_~!.

Howto

Fundamental Datetime
     Pada dasarnya penyimpanan format date pada database normalnya memiliki format :
 - 'YYYY/MM/DD HH:MM:SS AM/PM' => Oracle
-  'MM/DD/YYYY HH:MM:SS AM/PM' => SQL Server
-  'YYYY/MM/DD HH:MM:SS'              => Mysql

nah bagaimana cara narik datanya berdasarkan date ?? berikut adalah contoh query dari masing-masing database.

1. Oracle
    to_date ('10/05/2013','dd/mm/yyyy');
 
example : select *
               from table
              where creation_date > to_date ('10/05/2013','dd/mm/yyyy');


2. SQL Server
   CONVERT(type,field or date,code_format)

here's the code_format (source from w3schools)

Value (century yy)	Value (century yyyy)	Input/Output	Standard
-	0 or 100	mon dd yyyy hh:miAM (or PM)	Default
1	101	mm/dd/yy	USA
2	102	yy.mm.dd	ANSI
3	103	dd/mm/yy	British/French
4	104	dd.mm.yy	German
5	105	dd-mm-yy	Italian
6	106	dd mon yy
7	107	Mon dd, yy
8	108	hh:mm:ss
-	9 or 109	mon dd yyyy hh:mi:ss:mmmAM (or PM)	Default+millisec
10	110	mm-dd-yy	USA
11	111	yy/mm/dd	Japan
12	112	yymmdd	ISO
-	13 or 113	dd mon yyyy hh:mi:ss:mmm (24h)
14	114	hh:mi:ss:mmm (24h)
-	20 or 120	yyyy-mm-dd hh:mi:ss (24h)
-	21 or 121	yyyy-mm-dd hh:mi:ss.mmm (24h)
-	126	yyyy-mm-ddThh:mi:ss.mmm (no spaces)	ISO8601
-	130	dd mon yyyy hh:mi:ss:mmmAM	Hijiri
-	131	dd/mm/yy hh:mi:ss:mmmAM	Hijiri

Example:
             select  CONVERT(VARCHAR,create_date,103) as create_date
    from MS_CLIENT_H
             where create_date > '2013-02-01 14:26:40'

3. Mysql
    Date_Format(date,format)
 
here's the code_format (source from w3schools)

Format	Description
%a	Abbreviated weekday name
%b	Abbreviated month name
%c	Month, numeric
%D	Day of month with English suffix
%d	Day of month, numeric (00-31)
%e	Day of month, numeric (0-31)
%f	Microseconds
%H	Hour (00-23)
%h	Hour (01-12)
%I	Hour (01-12)
%i	Minutes, numeric (00-59)
%j	Day of year (001-366)
%k	Hour (0-23)
%l	Hour (1-12)
%M	Month name
%m	Month, numeric (00-12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss AM or PM)
%S	Seconds (00-59)
%s	Seconds (00-59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00-53) where Sunday is the first day of week
%u	Week (00-53) where Monday is the first day of week
%V	Week (01-53) where Sunday is the first day of week, used with %X
%v	Week (01-53) where Monday is the first day of week, used with %x
%W	Weekday name
%w	Day of the week (0=Sunday, 6=Saturday)
%X	Year of the week where Sunday is the first day of week, four digits, used with %V
%x	Year of the week where Monday is the first day of week, four digits, used with %v
%Y	Year, four digits
%y	Year, two digits

Example :
     SELECT datecolumn
     FROM `user`
     WHERE datecolumn = date_format('23-05-2013','%d-%m-%y')



Hope this helps :D

Read more

[Database][Oracle] Toad not shown hidden record !

Deskripsi
      Kejadian ini terjadi ketika ada hidden record tapi tidak mampu ditampilkan padahal sudah menjalankan syntax seperti di halaman INI dan ketika dicoba di komputer lain yang sudah diinstal toad tapi BISA, aneh kan ? itu kenapa ? apa perlu diinstal ulang ?

Howto
penyebabnya ada di bagian registry
1. Buka registry dan cari Binary Data dengan nama NLS_LANG, klo mau cepat gunakan fitur find (CTRL + F )
2. Jika sudah ketemu pastikan valuenya "AMERICAN_AMERICA.WE8ISO8859P1" (tanpa tanda kutip).
3. Lakukan pencarian kembali dengan pencet F3 dan samakan semua valuenya sampe semua selesai.
4. jika masih tidak bisa berarti penyebabnya bukan di bagian registry.
5. terus gmn dengan value yang sudah keubah ?? instal ulank bro oracle clientnya *kaburrr

Read more

[Database][Mysql/SQLServer/Oracle] Limit Query Record

Deskripsi
     Me-limit atau membatasi jumlah keluaran record yang diquery.

How To

// mysql

select col from tabel limit 10;

// Oracle

// Oracle has a system attribute ROWNUM for each record returned.

select col from tabel where rownum <= 10;

// Microsoft SQL

select top 10 col from tabel;

Read more