Thursday, December 25, 2008

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

Monday, December 22, 2008

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

Thursday, December 18, 2008

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

Monday, December 15, 2008

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.

Thursday, December 11, 2008

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.

Friday, November 28, 2008

How to encrypt your stored procedure, view, function, etc in SQL Server

Sometimes you don't want others to know the source code in your stored procedures, functions, views, etc. in SQL Server.
In this case, you can include WITH ENCRYPTION followed by CREATE PROCEDURE, CREATE VIEW, CREATE FUNCTION, etc. like that:
CREATE PROCEDURE ProcedureName
WITH ENCRYPTION

CREATE VIEW ViewName
WITH ENCRYPTION

CREATE PROCEDURE FunctionName
WITH ENCRYPTION