PL/SQL Reserved Words

ALL ARRAY AUTHID ALTER AS AVG AND ASC ANY AT BEGIN BOOLEAN BETWEEN BULK BINARY_INTEGER BY BODY CASE CLOSE COMMENT CONSTANT CURSOR CHAR CLUSTER COMMIT CREATE CHAR_BASE COALESCE COMPRESS CURRENT CHECK COLLECT CONNECT CURRVAL DATE DEFAULT DO DAY DELETE DROP DECLARE DESC DECIMAL DISTINCT ELSE EXCLUSIVE EXTENDS ELSIF EXECUTE EXTRACT END EXISTS EXCEPTION EXIT FALSE FORALL FETCH FROM FLOAT FUNCTION FOR GOTO GROUP HAVING HEAP HOUR IF INDICATOR NTERSECT ISOLATION IMMEDIATE INSERT INTERVAL IN INTEGER INTO INDEX INTERFACE IS JAVA LEVEL LONG LIKE LOOP LIMITED LOCK MAX MLSLABEL MIN MOD MINUS MODE MINUTE MONTH NATURAL NOCOPY NUMBER NATURALN NOT NUMBER_BASE NEW NOWAIT NEXTVAL NULL OCIROWID OPEN ORDER OF OPERATOR ORGANIZATION ON OPTION OTHERS OPAQUE OR OUT PACKAGE POSITIVE PRIVATE PARTITION POSITIVEN PROCEDURE PCTFREE PRAGMA PUBLIC PLS_INTEGER PRIOR RAISE RECORD REVERSE ROWNUM RANGE REF ROLLBACK ROWTYPE RAW RELEASE ROW REAL RETURN ROWID SAVEPOINT SET SQL STDDEV SYNONYM SECOND SHARE SQLCODE SUBTYPE SYSDATE SELECT SMALLINT SQLERRM SUCCESSFUL SEPERATE SPACE START SUM TABLE TIMEZONE_REGION TO THEN TIMEZONE_ABBR TRIGGER TIME TIMEZONE_MINUTE TRUE TIMESTAMP TIMEZONE_HOUR TYPE UID USE UNION USER UNIQUE UPDATE VALIDATE VARIANCE VALUES VIEW VARCHAR VARCHAR2 WHEN WITH WHENEVER WORK WHERE WRITE WHILE YEAR  ZONE

How to copy table in Oracle and SQL Server

Assume that you have a table tblCustomers, you want to copy this table (schema and data) to test data on it.
You can use this script:

CREATE TABLE tblCustomersCopy AS SELECT * FROM tblCustomers
Note that if you use this script in SQL Server, you can meet an error like that: Incorrect syntax near the keyword 'AS'.
To copy table in SQL Server, you can use this script:
SELECT * INTO tblCustomersCopy FROM tblCustomers

Test your new table:
Oracle:
SELECT * FROM tblCustomerCopy WHERE RowNum <>
SQL Server
SELECT TOP 10 * FROM tblCustomerCopy

DROP TABLE tblCustomersCopy

Select all tasks based on each project in EPM

SELECT t2.PROJ_NAME,
 t1.TASK_NAME,
 t1.TASK_EARLY_FINISH,
 t1.TASK_LATE_START,
 t1.TASK_STOP_DATE,
 t1.TASK_RESUME_DATE,
 t1.TASK_OUTLINE_LEVEL,
 t1.TASK_DUR,
 t1.TASK_DUR_FMT,
 t1.TASK_ACT_DUR,
 t1.TASK_REM_DUR,
 t1.TASK_START_DATE,
 t1.TASK_FINISH_DATE,
 t1.TASK_ACT_START,
 t1.TASK_ACT_FINISH,
 t1.TASK_CONSTRAINT_DATE,
 t1.TASK_EARLY_START,
 t1.TASK_LATE_FINISH,
 t1.TASK_WORK,
 t1.TASK_ACT_WORK,
 t1.TASK_REM_WORK,
 t1.TASK_COST,
 t1.TASK_WBS,
 t1.TASK_SUMMARY_PROGRESS_DATE,
 t1.CREATED_DATE,
 t1.MOD_DATE
FROM dbo.MSP_TASKS t1
 INNER JOIN dbo.MSP_PROJECTS t2 ON t1.PROJ_UID = t2.PROJ_UID

Select all tasks in EPM

SELECT TASK_NAME, 
  TASK_EARLY_FINISH, 
  TASK_LATE_START, 
  TASK_STOP_DATE, 
  TASK_RESUME_DATE, 
  TASK_OUTLINE_LEVEL, 
  TASK_DUR,
  TASK_DUR_FMT,
  TASK_ACT_DUR,
  TASK_REM_DUR,
  TASK_START_DATE,
  TASK_FINISH_DATE,
  TASK_ACT_START,
  TASK_ACT_FINISH,
  TASK_CONSTRAINT_DATE, 
  TASK_EARLY_START,
  TASK_LATE_FINISH,
  TASK_WORK,
  TASK_ACT_WORK,
  TASK_REM_WORK,
  TASK_COST,
  TASK_WBS,
  TASK_SUMMARY_PROGRESS_DATE,
  CREATED_DATE,
  MOD_DATE
FROM dbo.MSP_TASKS

Select all projects in EPM

SELECT PROJ_NAME 
  PROJ_PROP_AUTHOR,
  PROJ_PROP_COMPANY,
  PROJ_INFO_CURRENT_DATE,
  PROJ_OPT_DEF_FINISH_TIME,
  PROJ_OPT_DEF_START_TIME,
  PROJ_INFO_FINISH_DATE,
  PROJ_LAST_SAVED,
  PROJ_CREATION_DATE,
  PROJ_INFO_START_DATE,
  PROJ_INFO_STATUS_DATE,
  PROJ_PROP_TITLE,
  PROJ_CHECKOUTDATE,
  WPROJ_DESCRIPTION,
  WPROJ_LAST_PUB,
  WPROJ_STS_SUBWEB_NAME,
  PROJ_SESSION_DESCRIPTION,
  PROJ_PROP_MANAGER
FROM dbo.MSP_PROJECTS

Salesforce Object Query Language (SOQL)

SELECT fieldList FROM objectType
[WHERE conditionExpression]
[ORDER BY fieldName ASC | DESC ? NULLS FIRST | LAST ?]
LIMIT ?

Examples:

1. SELECT Id, Name, BillingCity FROM Account
2. SELECT count() FROM Contact
3. SELECT Contact.Firstname, Contact.Account.Name FROM Contact
4. SELECT Account.Name, (SELECT Contact.LastName FROM Account.Contacts)
FROM Account
5. SELECT Name FROM Account WHERE Name like 'A%'
6. SELECT Id FROM Contact WHERE Name like 'A%' AND
MailingCity='California'
7. SELECT Id FROM Case WHERE Contact.Lastname = null
8. SELECT AccountId, FirstName, lastname FROM Contact WHERE lastname LIKE 'appl_%'
9. SELECT Name FROM ACCOUNT WHERE BillingState IN ('California', 'New York')
10. SELECT Name FROM ACCOUNT WHERE BillingState NOT IN ('California', 'New York')
11. SELECT Id, Name FROM Account
WHERE Id IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Lost')
12. SELECT Id
FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Opportunity WHERE IsClosed = false)
13. SELECT Id, Name
FROM Account
WHERE Id IN (SELECT AccountId FROM Contact WHERE LastName LIKE 'apple%')
 AND Id IN (SELECT AccountId FROM Opportunity WHERE isClosed = false)
14. SELECT Id, (SELECT Id from OpportunityLineItems)
FROM Opportunity
WHERE Id IN
 (SELECT OpportunityId FROM OpportunityLineItem WHERE totalPrice > 10000)
15. etc.

Escape sequence:

Sequence	Meaning
\n	New line
\r	Carriage return
\t	Tab
\b	Bell
\f	Form feed
\"	One double-quote character
\'	One single-quote character
\\	Backslash

Example with escape sequence:
SELECT Id FROM Account WHERE Name LIKE 'Bob\'s BBQ'

More information about query with SOQL, read here

Note that NULL operator is: '= null' or '!= null', NOT 'IS NULL' or 'IS NOT NULL'

Source: salesforce.com

How to query to linked server by using SQL Server

Assume that you have a linked server MyERP which connects to an Oracle database.
To be able to access data in this database in SQL Server, you can use OPENQUERY
Syntax for OPENQUERY:
OPENQUERY(MyERP,'Your query')

Some examples:
1. Select all employees in table tblEmployee:
SELECT *
FROM OPENQUERY(MyERP,'SELECT * FROM tblEmployee');

2. Update tblEmployee
UPDATE OPENQUERY(MyERP, 'SELECT EmployeeName FROM tblEmployee WHERE EmployeeID = 1')
SET EmployeeName = 'Barak Obama'

3. Delete tblEmployee:
DELETE OPENQUERY(MyERP, 'SELECT EmployeeName FROM tblEmployee WHERE EmployeeID = 1')

4. Insert to tblEmployee:
INSERT OPENQUERY(MyERP, 'SELECT EmployeeName FROM tblEmployee')
VALUES('Barak Obama')

For more information, click here

How to identify your SQL Server version and edition

For SQL Server 2005:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

For SQL Server 2000:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

For SQL Server 7.0
SELECT @@VERSION

For more information: read here

Quick check all servers running or not

As a DBA, you may manage many servers. If you manage them remotely, you may want to know if they are open or not.
You may use command line ping yourserver to check. However, you are managing 4 server and are lazy to input every time you need to check.
Just create a bat file to check it for you.
For example, you are managing server1, server2, server3, and server4. Create a .bat file with the following content:
C:\Windows\cmd.exe
ping server1
ping server2
ping server3
ping server4
echo Press any key to exit...
pause

Only run this .bat file every time you want to check these servers running or not.

How to cancel the 'stubborn' job/queue in Project Server

When working with Project Server, sometimes you meet a failed process or failed job in Manage Queue Jobs. Its job state is 'Getting Queued'. You tried to cancel it but it's still there. In this case, you cannot force check-in the related project.
To 'kill' it and allow other jobs to run, what you need to do is enable an additional option in order to cancel the jobs.
- Login to PWA as an Administrator
- Going to Server Settings - Manage Queue.
- On the Manage Queue Jobs page, expand the Advanced Options
- Enable the "Cancel jobs getting enqueued."
- Select the particular item in the queue that's stuck and click the Cancel Jobs button.
This should cancel the blocked job and then allow the other correlated/related jobs to go through.