Get all weekends in year 2010

DECLARE @FirstDateOfYear DATETIME
SET @FirstDateOfYear = ‘2010-01-01′
SELECT DISTINCT DATEADD(d, number, @FirstDateOfYear),
CASE DATEPART(dw, DATEADD(d, number, @FirstDateOfYear))
WHEN 7 THEN ‘Saturday’
WHEN 1 THEN ‘Sunday’
ELSE ‘Work Day’
END
FROM master..spt_values
WHERE number BETWEEN 0 AND 364
AND (DATEPART(dw, DATEADD(d, number, @FirstDateOfYear)) = 1 OR DATEPART(dw, DATEADD(d, number, @FirstDateOfYear)) = 7)
ORDER BY DATEADD(d, number, @FirstDateOfYear)

This is the script I replied the article Get Date of All Weekdays or Weekends of the Year on SQLAuthority.com

Add multiple columns to a table in MySQL

ALTER TABLE customer
ADD customer_description varchar(255),
ADD established_date datetime,
ADD salesforce_id varchar(18)

It's a bit different with SQL Server, we need to use ADD for each column.

Add a column with comment and default value

Add status column to tblCustomer table with comment and default value is 'I'

ALTER TABLE tblCustomer
ADD status CHAR(1) ASCII DEFAULT 'I' COMMENT 'A: Active, I: Inactive';

Generate token procedure

CREATE PROCEDURE GenerateToken(v_email varchar(255))
BEGIN
 DECLARE i_count INT;
 SELECT COUNT(email) INTO i_count
 FROM tbToken WHERE email = v_email;
 
 IF (i_count > 0) THEN
  UPDATE tblToken
  SET token = UUID(),
    created_date = NOW()
  WHERE email = v_email;
 ELSE
  INSERT INTO tblToken(email, token, created_date)
  VALUES(v_email, UUID(), NOW());
 END IF;
END;

Generate a token

Use UUID() function to generate a token or a GUID.

SELECT UUID();

Create linked server to MySQL

Follow these steps:
- Download MySQL Connector and install it in your server
- Use ODBC to create a System DSN name, such as: SystemDSNName to connect to your MySQL db by using MySQL ODBC Driver
- Configure MSDASQL Provider
- Run this script to create linked server to your MySQL db
EXEC master.dbo.sp_addlinkedserver @server = N'YourLinkedServerName', @srvproduct=N'SystemDSNName', @provider=N'MSDASQL', @datasrc=N'YourSystemDSNName'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'YourLinkedServerName', @useself=N'False', @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL

Get MySQL version

SELECT VERSION();

Create User on local host

CREATE USER 'UserName'@'localhost'
IDENTIFIED BY 'UserPassword'

Find SQL Server logins using their names as passwords

Applied to SQL Server 2005 or later

SELECT name, name As [Pwd]
FROM sys.sql_logins
WHERE PWDCOMPARE(name, password_hash) = 1
OR PWDCOMPARE(name,password_hash,1) = 1

Using SEQUENCE in Oracle

CREATE SEQUENCE CustomerSeq
INCREMENT BY 1
START WITH 1

INSERT INTO Customer(CustomerID, CustomerName)
VALUES(CustomerSeq.NEXTVAL, 'Google Inc.')

Import data from a text file to a table

File Test.txt contain 3 rows
1,2
3,4
5,6
We can import these rows into a table by using these scripts.
CREATE TABLE dbo.tblTest(a int, b int)
GO
BULK INSERT dbo.tblTest
FROM 'D:\Temp\Test.txt'
WITH(FIELDTERMINATOR =',',
ROWTERMINATOR ='\n')

Convert male into female and vice versa

tblEmployee(EmployeeID, EmployeeName, Gender, etc.)
Gender contains two values only, 'M' represents for 'Male' and 'F' represent for 'Female'.
For some reason, after importing employee data, you found that Gender field values have been reversed and you need to convert them.
UPDATE dbo.tblEmployee
SET Gender = t2.Gender
FROM dbo.tblEmployee t1
INNER JOIN (SELECT EmployeeID,
CASE Gender
WHEN 'M' THEN 'F'
WHEN 'F' THEN 'M'
END AS Gender
FROM dbo.tblEmployee) t2 ON t1.EmployeeID = t2.EmployeeID

Get 365 days in 2010 with one query

Use undocumented spt_values in master database and DATEADD() function

SELECT DATEADD(d, number, '2010-01-01')
FROM master..spt_values
WHERE type = 'P'
AND number < 365

Truncate database log file

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE YourDBName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (YourDBName_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE YourDBName
SET RECOVERY FULL;

Top 5 DBA tasks that are a waste of time (and might be hurting your SQL Server)

Reading: Top 5 DBA tasks that are a waste of time (and might be hurting your SQL Server)

Truncate table and keep blank space

Use this script to truncate your table but keep the blank space for INSERT or UPDATE new data.
TRUNCATE TABLE YourTable REUSE STORAGE;
Default of TRUNCATE TABLE is DROP STORAGE

Delete and return deleted rows

Applied to SQL Server 2005 or later.

CREATE TABLE dbo.tblTest(a int, b int)
INSERT INTO dbo.tblTest(a,b)
SELECT 1,2
UNION SELECT 3,4
UNION SELECT 4,5

DELETE dbo.tblTest OUTPUT DELETED.*
WHERE a > 1

Checking table before dropping

Be careful when using this script in your production server!

IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'YourTable')
DROP TABLE YourTable;

Import Google map KML file into a table

Applied to SQL Server 2005 or later
Define MapKML as XML data type in table tblMap

DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'D:\GoogleMaps\YourMap.kml'
EXEC('
INSERT INTO tblMap(MapName, MapKML)
SELECT ''Your map'', xmlData
FROM
(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')

Get Oracle db version

SELECT * FROM v$version

Change User Name/Password

Change a user name to new name
ALTER LOGIN OldUserName
WITH NAME = NewUserName;

Change user password
ALTER LOGIN YourUserName
WITH PASSWORD = 'YourNewPassword';

CREATE LOGIN

The following script creates a login for a particular user and assigns a password. You use MUST_CHANGE option to require this user to change their password the first time they connect to the server.

CREATE LOGIN YourLoginName
WITH PASSWORD = 'YourLoginPassword' MUST_CHANGE

Search with CONTAINS

The following script returns all products which their name contains strings with prefixes of either "chain" or "full".
You must set up full text search for Product table.

SELECT ProductID, ProductName, Price
FROM Product
WHERE CONTAINS(ProductName, '"chain*" OR "full*"');

Search with FreeText

Search employees with their description contains 'hard' or 'working'
You must set up full text search for Employee table

SELECT EmployeeID, EmployeeName, BirthDate
FROM Employee
WHERE FREETEXT(EmployeeDescription, 'hard working');

Check database back up set capacity

Sometimes you need to check your back up files to do some maintenance actions for it, here's the script you can use.

SELECT CONVERT(VARCHAR(10), backup_start_date, 111) [Backup Date]
, backup_size/1024000 [Size In MB]
FROM msdb..backupset
WHERE database_name = 'Your DB Name'
and type = 'd'
ORDER BY backup_start_date DESC
COMPUTE SUM(backup_size/1024000)

Get database columns by using CTE

The following script uses Common Table Expression to get all columns in the database.
Applied to SQL Server 2005 or later

WITH DBColumns
AS
(
   SELECT table_name, column_name
   FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name
FROM DBColumns

Create a database snapshot

Applied to SQL Server 2005 and later

CREATE DATABASE database_snapshot_name
(NAME = source_logical_filename,
FILENAME = operating_system_file)
AS SNAPSHOT OF source_database_name;

Get current connections

Sys.dm_exec_connections - Returns information about the connections made to the instance of the SQL Server and the details of each connection.

SELECT t1.session_id
, t2.login_name
FROM sys.dm_exec_connections t1
INNER JOIN sys.dm_exec_sessions t2 ON t1.session_id = t2.session_id

Get number of sessions established by every login

Applied to SQL Server 2005 or later

SELECT login_name [Login Name]
, COUNT(session_id) [Session Count]
FROM sys.dm_exec_sessions
GROUP BY login_name

Get table data as XML

Get Customer data as XML, return CustomerID as attribute and CustomerName as value

SELECT 1 AS Tag,
NULL AS Parent,
CustomerId AS [Customers!1!CustomerId],
CompanyName AS [Customers!1]
FROM Customers
FOR XML EXPLICIT

Get free disk space on server

EXEC xp_fixeddrives

Get space used in a table

EXEC sp_spaceused 'Your table name'

Get database file path and capacity

SQL Server 2000
SELECT * FROM sysfiles

SQL Server 2005 or later
SELECT * FROM sys.sysfiles

Get all triggers in a database

Applied to SQL Server 2005 or later

SELECT  t1.name [Trigger Name],
t3.name [Table Owner],
t2.name [Table Name],
CASE WHEN t1.is_disabled = 0 THEN 'Enabled'
  ELSE 'Disabled'END Status
FROM sys.triggers t1
INNER JOIN sys.tables t2 
  ON t1.parent_id = t2.object_id
INNER JOIN sys.schemas t3
  ON t2.schema_id = t3.schema_id

Find which tables contain a column name

Use the following query to get which tables contains EmployeeID as a column in a database.

SELECT t2.name [Table Name], t1.name [Column Name]
FROM syscolumns t1
INNER JOIN sysobjects t2 ON t1.id = t2.id
WHERE t1.name = 'EmployeeID'
AND t2.xtype = 'U'

Stored procedure tip

Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.

Get table data as XML

Get data in table as XML, return each column as element instead of attribute

SELECT CustomerID, CompanyName
FROM Customers
FOR XML AUTO, ELEMENTS

Copy a table

Copy CustomerName, CustomerID from Customer table

SELECT CustomerName, CustomerID INTO CustomerCopy
FROM Customer

Copy structure of Customer table
SELECT * INTO CustomerCopy
FROM Customer
WHERE 1 = 2

Export table to HTML format

Using system stored procedure sp_makewebtask to generate HTML file from a table.

EXEC sp_makewebtask @outputfile = 'C:\Temp\Customer.HTML',
@query='SELECT CompanyName, CustomerID FROM Customer ORDER BY
CompanyName'
, @lastupdated=0,@resultstitle=' '

Export table to HTML format

SELECT
'<TABLE BORDER="1">','<TH>Company Name</TH>','<TH>Customer ID</TH>',''
UNION ALL
SELECT '<TR>','<TD>'+CompanyName+'</TD>','<TD>'+CustomerId+'</TD>','</TR>'
FROM Customer
UNION ALL
SELECT
'</TABLE>','','',''

Get database endpoints

Applied to SQL Server 2005 or later

SELECT *
FROM sys.endpoints;

SELECT *
FROM sys.http_endpoints;

Get all tables & views in database

SELECT * FROM INFORMATION_SCHEMA.TABLES

Get objects, counters, and values installed

Shows the SQL Server objects, counters, and values installed on your server.

SELECT *
FROM sys.dm_os_performance_counters

Daily tasks should do

• Run backups (full, differential, filegroup, and log).
• Check services and instances to ensure they are running.
• Check SQL Server Logs (engine and SQL Agent).
• Check Windows event logs for SQL Server or SQL Server Agent entries.
• Verify backup and maintenance steps.

Backup database with password

BACKUP DATABASE YourDB
TO DISK = 'd:\temp\YourDB.bak'
WITH
   INIT
,   PASSWORD='YourPassword'
GO

Mirror a backup

BACKUP DATABASE YourDB
TO DISK = 'd:\temp\YourDB1.bak'
MIRROR TO DISK = 'd:\temp\YourDB22.bak'
WITH
FORMAT
GO

Create a backup device

EXEC master.dbo.sp_addumpdevice @devtype = N'disk'
, @logicalname = N'MyDailyBackup'
, @physicalname = N'D:\TEMP\SQL2K5Backup.bak'
GO

Check database exists or not before delete it

Be very careful before running this query!

IF EXISTS (
  SELECT name
        FROM sys.databases
        WHERE name = N'YourDBName'
)
DROP DATABASE YourDBName
GO

Split a string into a table

CREATE FUNCTION [fnSplit]
(@sInputList VARCHAR(MAX)
, @sDelimiter VARCHAR(10) = ',')
RETURNS @List TABLE (item NVARCHAR(4000))
BEGIN
DECLARE @sItem NVARCHAR(8000)
WHILE CHARINDEX(@sDelimiter, @sInputList, 0) <> 0
BEGIN
SELECT 

@sItem = RTRIM(LTRIM(SUBSTRING(@sInputList, 1, CHARINDEX(@sDelimiter, @sInputList, 0) - 1))),
@sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList, CHARINDEX(@sDelimiter, @sInputList,0) + LEN(@sDelimiter), LEN(@sInputList))))

  IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList
RETURN
END

Get all employees have birthday this week

SELECT EmployeeID, EmployeeName, Email, BirthDate
FROM Employee
WHERE DATEDIFF(w, BirthDate, GETDATE()) = 0

Get top 10 customers by revenue

Using TOP keyword.

SELECT TOP 10 CustomerID, CustomerName, Revenue
FROM dbo.Customer
ORDER BY Revenue DESC

Get all customers established this month

SELECT CustomerID, CustomerName, EstablishedDate, Email, Address
FROM dbo.Customer
WHERE DATEDIFF(m, EstablishedDate, GETDATE()) = 0

Try/Catch in T-SQL

Applied from SQL Server 2005

BEGIN TRY
 BEGIN TRAN
 --Your T-SQL code here
 COMMIT TRAN
END TRY
BEGIN CATCH
 DECLARE @ErrorMessage NVARCHAR(4000);
 SELECT @ErrorMessage = ERROR_MESSAGE();
 ROLLBACK TRAN;
 RAISERROR(@ErrorMessage, 16, 1);
END CATCH

Using cursor

DECLARE Cur CURSOR FOR
 SELECT AccountID, AccountName
 FROM Account

OPEN Cur
FETCH FROM Cur
INTO @AccountID, @AccountName

WHILE @@FETCH_STATUS = 0
BEGIN
 --Do your tasks here
 FETCH FROM Cur
 INTO @AccountID, @AcountName
END

CLOSE Cur
DEALLOCATE Cur

A six-part series on getting the most out of SharePoint Designer 2007

As a DBA, I think you also need some knowledge about SharePoint and SharePoint Designer so that you can use it to modify your SharePoint site when necessary. You can read this article for more detail A six-part series on getting the most out of SharePoint Designer 2007.

Delete all foreign keys in a database

DECLARE @Table VARCHAR(255)
DECLARE @ForeignKey VARCHAR(255)
DECLARE @STRSQL VARCHAR(8000)

DECLARE Cur CURSOR FOR   
 select t1.name as [Table], t2.name as [ForeignKey]
 from sysobjects t1 inner join
  sysobjects t2 on t1.id = t2.parent_obj
 where t1.xtype = 'U' and t2.xtype = 'F'
OPEN Cur
FETCH FROM Cur
INTO @Table, @ForeignKey
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @STRSQL = 'ALTER TABLE ' + @Table +
   ' DROP CONSTRAINT ' + @ForeignKey
 EXECUTE(@STRSQL)

 FETCH FROM Cur
 INTO @Table, @ForeignKey
END
CLOSE Cur
DEALLOCATE Cur

Get random record

SELECT TOP 1 *
FROM YourTable
ORDER BY NEWID()

Get table script

You can use this function dbms_metadata.get_ddl to generate the create script of your table.
SELECT dbms_metadata.get_ddl('TABLE', 'Your table', 'Your owner')
FROM dual;

Active Directory Maximum Limits - Scalability

Do you know the limitation of Active Directory? If not, please read this article Active Directory Maximum Limits - Scalability

Get database status

SELECT DATABASEPROPERTYEX(db_name(), 'Status')

Return value:
ONLINE = Database is available for query.
OFFLINE = Database was explicitly taken offline.
RESTORING = Database is being restored.
RECOVERING = Database is recovering and not yet ready for queries.
SUSPECT = Database did not recover.
EMERGENCY = Database is in an emergency, read-only state. Access is restricted to sysadmin members

Scaling an Oracle database: What is the best strategy for you?

I'm reading this article Scaling an Oracle database: What is the best strategy for you?

Get recovery model for the database.

SELECT DATABASEPROPERTYEX(db_name(), 'Recovery')
Return value:
FULL = Full recovery model
BULK_LOGGED = Bulk logged model
SIMPLE = Simple recovery model
Base data type: nvarchar(128)

Get all encrypted procedures

SELECT OBJECT_NAME(id) as ObjectName
FROM sys.syscomments
WHERE encrypted = 1

Encrypt stored procedure/ view/ function

CREATE PROCEDURE YourProcedureName
WITH ENCRYPTION
AS

CREATE VIEW YourViewName
WITH ENCRYPTION
AS

CREATE FUNCTION YourFunctionName()
RETURNS INT
WITH ENCRYPTION
AS

Microsoft SharePoint Designer Team Blog : Locking Down SharePoint Designer

I'm reading Microsoft SharePoint Designer Team Blog : Locking Down SharePoint Designer

ENABLE/DISABLE

Change status of constraint or primary key in a table

ALTER TABLE DEPT
DISABLE CONSTRAINT DEPT_PRIMARY_KEY CASCADE;

Get running cursors and their queries

SELECT NVL(USERNAME,'SYSTEM PROC') USER_NAME, t2.SID,
 SQL_TEXT
FROM v$open_cursor t1, v$session t2
WHERE t2.SQL_ADDRESS = t1.ADDRESS
AND t2.SQL_HASH_VALUE = t1.HASH_VALUE
ORDER BY 1

Check which objects modified recently

SELECT OBJECT_NAME,
    OBJECT_TYPE,
       TO_CHAR(CREATED,       'DD-Mon-RR HH24:MI') CREATED_DATE,
       TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') LAST_MODIFIED_DATE,
       STATUS
FROM   USER_OBJECTS
WHERE  SYSDATE - LAST_DDL_TIME < 1000;

CREATE FUNCTION

Create a function to get account name by account id.

CREATE OR REPLACE FUNCTION fnGetAccountNameByID(vACCOUNT_ID IN NUMBER)
RETURN VARCHAR2 IS vACCOUNT_NAME VARCHAR2(255);
BEGIN
   vACCOUNT_NAME := '';
   SELECT ACCOUNT_NAME INTO vACCOUNT_NAME
   FROM ACCOUNT
   WHERE ACCOUNT_ID = vACCOUNT_ID;
   
   RETURN vACCOUNT_NAME;
   
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END fnGetAccountNameByID;
/

TOADSoft.com - Home of the World's #1 Tool for Database Application Developers and DBAs!

When working with dbs, I think you should have some useful tools to be able to accelerate your db tasks. I often use Toad tools in this site
TOADSoft.com - Home of the World's #1 Tool for Database Application Developers and DBAs!
At the moment, I am using Toad for Oracle & Toad for MySQL

Oracle PL/SQL Version Controlling

--Firstly, create a table to store change history

CREATE TABLE DBChangeHistory AS 
SELECT SYSDATE CHANGE_DATE, ALL_SOURCE.*
FROM   ALL_SOURCE 
WHERE 1=2;

--Secondly, create a trigger to store the code when it's changed
--You can modify this code to store Version number
CREATE OR REPLACE TRIGGER tgTrackDBChange
AFTER CREATE ON SCOTT.SCHEMA
DECLARE
BEGIN
  IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
                           'PACKAGE',   'PACKAGE BODY',
                           'TYPE',      'TYPE BODY')
  THEN     
     INSERT INTO DBChangeHistory
     SELECT sysdate, all_source.* 
  FROM ALL_SOURCE
     WHERE  TYPE = ORA_DICT_OBJ_TYPE
  AND  NAME = ORA_DICT_OBJ_NAME;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
       --Raise error here;
END;

Microsoft Releases SQL Azure Database CTP

Microsoft Releases SQL Azure Database CTP

Enterprise App Developers Use Insecure Data - InternetNews.com

Enterprise App Developers Use Insecure Data - InternetNews.com

Business Analysts: Role Changes Require New Skill Sets

I have read this article Business Analysts: Role Changes Require New Skill Sets on CIO.com and would like to share with you.
I think some DBAs are also business analysts like me.

Find a text inside stored procedure, trigger, view, etc.

Sometimes, you need to find a specific text in your stored procedures, this script may be useful to help you.

SELECT DISTINCT t2.name [Object Name],
    CASE t2.type
        WHEN'U' THEN 'User Table'
        WHEN'S' THEN 'System Table'
        WHEN 'V' THEN 'View'
        WHEN 'TR' THEN 'Trigger'
        WHEN 'P' THEN 'Stored Procedure'
        WHEN 'C' THEN 'Constraint - Check'
        WHEN 'D' THEN 'Default'
        WHEN 'K' THEN 'Primary Key'
        WHEN 'F' THEN 'Foreign Key'
        WHEN 'L' THEN 'Log'
        WHEN 'R' THEN 'Rule'
        WHEN 'RF' THEN 'Replication Filter stp'
        ELSE 'Other: ''' + t2.type+ ''''
    END [Object Type],
 t2.crdate [Created Date]
FROM syscomments t1
    INNER JOIN sysobjects t2 ON t1.id = t2.id
WHERE t1.[text] LIKE '%Your text need searching%'

Find a column in database

SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM ALL_TAB_COLS
WHERE COLUMN_NAME = 'Your column name you want to find'
It's simple but very useful for any DBAs.

Read RSS feed by using SQL Server stored procedure

If you want to read RSS Feed by using SQL Server stored procedure, you can use CLR stored procedure as described below:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Text;
using System.Xml.XPath;

public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void spReadRSSFeed(string strURL)
  {
      XPathDocument doc = new XPathDocument(strURL);
      XPathNavigator nav = doc.CreateNavigator();
      XPathNodeIterator i = nav.Select("//item");
      SqlMetaData[] rss_results = new SqlMetaData[4];
      rss_results[0] = new SqlMetaData("Title", SqlDbType.NVarChar, 250);
      rss_results[1] = new SqlMetaData("Publication Date",
          SqlDbType.DateTime);
      rss_results[2] = new SqlMetaData("Description",
          SqlDbType.NVarChar, 2000);
      rss_results[3] = new SqlMetaData("Link", SqlDbType.NVarChar, 1000);
      SqlDataRecord record = new SqlDataRecord(rss_results);
      SqlPipe sqlpipe = SqlContext.Pipe;
      sqlpipe.SendResultsStart(record);
      while (i.MoveNext())
      {
          record.SetString(0, (string)
              i.Current.Evaluate("string(title[1]/text())"));
          record.SetDateTime(1, DateTime.Parse((string)
              i.Current.Evaluate("string(pubDate[1]/text())")));
          record.SetString(2, (string)
              i.Current.Evaluate("string(description[1]/text())"));
          record.SetString(3, (string)
              i.Current.Evaluate("string(link[1]/text())"));
          sqlpipe.SendResultsRow(record);
      }
      sqlpipe.SendResultsEnd();
  }
};

After building to an assembly named RSSReader, for example, you can deploy it to your SQL Server like this:

CREATE ASSEMBLY [RSSReader]
AUTHORIZATION [dbo]
FROM 'Path to your RSSReader.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
-- Note that you have to use EXTERNAL_ACCESS because we need to access Internet
GO
CREATE PROCEDURE [dbo].[spReadRSSFeed]
@URL [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [RSSReader].[StoredProcedures].[spReadRSSFeed];

-- Running for testing
spReadRSSFeed 'http://msdn.microsoft.com/sql/rss.xml'

-- Remove them
DROP PROCEDURE spReadRSSFeed
DROP ASSEMBLY RSSReader

SQL Server Tip

Backup your databases every day and make sure that backup job was made successfully and saved to secured places.

Comment on table/column

Description on a table
COMMENT ON TABLE ACCOUNT IS 'Account Information'
Description of a column in table
COMMENT ON COLUMN ACCOUNT.ACCOUNT_NAME IS 'Name of Account'
Drop description, let it blank
COMMENT ON COLUMN ACCOUNT.ACCOUNT_NAME IS ''

Get all column comments of a table

SELECT *
FROM ALL_COL_COMMENTS
WHERE TABLE_NAME = 'YourTableName'

Delete a table

Be careful to use this query
DROP TABLE YourTable;
Drop a table and related references
DROP TABLE YourTable CASCADE CONSTRAINT;

Get source code/script of a view

You can use this query to get the script of your view quickly.

SELECT TEXT
FROM all_views
WHERE VIEW_NAME = 'Your View Name'

Get objects created/modified recently

SELECT OBJECT_NAME,
TO_CHAR(CREATED,       'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
STATUS
FROM   USER_OBJECTS
WHERE  SYSDATE - LAST_DDL_TIME < 7

SQL Sever queries take most I/O

SELECT TOP 100
    (total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
    (total_logical_reads + total_logical_writes) AS total_IO,
    qs.execution_count AS execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2,
         (CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS indivudual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_IO DESC;

Queries taking longest elapsed time

Sometimes, you need to determine which queries take a lot of time to run. In this case, you can use this query to check.

SELECT TOP 100 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
  qs.total_elapsed_time / 1000000.0 AS total_seconds,
  qs.execution_count,
  SUBSTRING (qt.text,qs.statement_start_offset/2,
 (CASE WHEN qs.statement_end_offset = -1
          THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
  o.name AS object_name,
  DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
  LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC;

Rename a table

Rename EMP to EMPLOYEE:
RENAME EMP TO EMPLOYEE;

Check active session

SELECT *
FROM v$session
WHERE STATUS = 'ACTIVE'
AND OSUSER = 'Your OSUSER'

10 Things Small Businesses Must do to Protect & Secure Data

In this podcast, Donna Childs of Prepared Small Business, discusses some best practices to help small businesses keep their information secure, protected, and well-managed.
Read here
Source: Symantec Corporation

SQL Server 2008 and using Transact SQL (TSQL)

How To Become a Database Administrator

Greater with subquery

Find employees have salary greater than maximum salary in 'IT' department

SELECT ENAME, JOB, HIREDATE
FROM EMP
WHERE SAL > (SELECT MAX(SAL)
          FROM EMP
          WHERE DEPTNO =
                 (SELECT DEPTNO
                 FROM DEPT
                 WHERE DNAME = 'IT'));

Create SQL Linked Server to Access Database

Sometimes, you need to query Access database directly in SQL Server. In that case, you can create a linked server between SQL Server and Access by using this query.

EXEC sp_addlinkedserver
@server = 'AccessNorwind',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'D:\AccessDatabase\NWind.mdb'

After creating, you can run query like this:
SELECT *
FROM AccessNorwind...Orders

SQL Server 2005 Connection String

Assume that your SQL Server Name is SQL2K5. You allowed your database SQL2K5DB to be in mixed mode: SQL Server and Windows Authentication. In SQL Server Authentication mode, you use user name ABC and Password 123456.

One of the connection strings below you can use in your code with .NET Framework

If you want to use SQL Server Authentication, you can use:

• Data Source=SQL2K5;Initial Catalog=SQL2K5DB;User Id=ABC;Password=123456;

• Server=SQL2K5;Database=SQL2K5DB;User ID=ABC;Password=123456;Trusted_Connection=False;

If you want to use Windows Authentication, you can use:

• Data Source=SQL2K5;Initial Catalog=SQL2K5DB;Integrated Security=SSPI;
  

• Server=SQL2K5;Database=SQL2K5DB;Trusted_Connection=True;

Salesforce: Tour the Sample Applications

Microsoft kills its Oracle data provider for ADO.NET

Click here to read the article.
Maybe you should modify your source code from now on when there're any changes in .NET Framework.
Source: Redmond Developer News

Execute Oracle stored procedure

Sometimes, you need to run your Oracle stored procedure in programming language code, such as C#. You can use this query to run:

string strSQL = "BEGIN YourStoredProcedure(Param1, Param2, ...); END;";
ExecuteOracleSQL(OracleConnectionString, strSQL);

Check table permissions

Use this script to verify that the users have enough permissions on your table.
The privileges may be: SELECT, INSERT, UPDATE, DELETE

SELECT *
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = 'Your table name';

Don't do that with SharePoint

Read this article on SearchWinNT to know more:
Don't do that with SharePoint

Create table with primary key

Create ACCOUNT table with PRIMARY KEY named ACCOUNT_PKEY on ACCOUNT_ID column

CREATE TABLE ACCOUNT
(
 ACCOUNT_ID NUMBER(6) CONSTRAINT ACCOUNT_PKEY PRIMARY KEY,
 ACCOUNT_NAME VARCHAR2(255),
 BILLING_COUNTRY VARCHAR(50),
 BILLING_CITY VARCHAR(50),
 ...
)

Compare more than one value

Get employees have minimum salary in their departments.

SELECT ENAME, JOB, DEPTNO, SAL
FROM EMP
WHERE (SAL,DEPTNO) IN (SELECT MIN(SAL), DEPTNO
                    FROM EMP
                    GROUP BY DEPTNO)

Get all constraints and types

List all tables with their constraints and constraint types.

SELECT OWNER,
       TABLE_NAME,
       CONSTRAINT_NAME,
       DECODE(CONSTRAINT_TYPE, 'C','Check',
                               'P','Primary Key',
                               'U','Unique',
                               'R','Foreign Key',
                               'V','With Check Option') TYPE,
       STATUS
FROM dba_constraints
ORDER BY OWNER, TABLE_NAME, CONSTRAINT_NAME

Get objects modified recently

Get database objects that are modified in last 7 days.

SELECT OWNER,
 OBJECT_NAME,
 OBJECT_TYPE,
 TO_CHAR(LAST_DDL_TIME,'YYYY/MM/DD HH24:MI:SS') LAST_MODIFIED_DATE,
 TO_CHAR(CREATED,'YYYY/MM/DD HH24:MI:SS') CREATED_DATE,
 STATUS
FROM dba_objects
WHERE  (SYSDATE - LAST_DDL_TIME) < 7

Self join

You have to use alias for self join.
List all accounts with their parent account.

SELECT t1.ACCOUNT_NAME, t2.ACCOUNT_NAME AS PARENT_ACCOUNT
FROM ACCOUNT t1, ACCOUNT t2
WHERE t1.PARENT_ACCOUNT_ID = t2.ACCOUNT_ID(+)
Use outer join here to list accounts without parents.

Outer join

List all accounts and their contacts, show contact with NULL value if an account has no contacts.
SELECT t1.ACCOUNT_NAME, t2.CONTACT_NAME
FROM ACCOUNT t1, CONTACT t2
WHERE t1.ACCOUNT_ID = t2.ACCOUNT_ID(+)

In SQL Server:
SELECT t1.ACCOUNT_NAME, t2.CONTACT_NAME
FROM ACCOUNT t1 LEFT JOIN
CONTACT t2 ON t1.ACCOUNT_ID = t2.ACCOUNT_ID

DUAL Table

System table of owner SYS, used to select one value from system function, user-defined function which return scalar value
SELECT SYSDATE FROM DUAL;

SYSDATE
---------
02-MAY-09

SELECT SYSDATE FROM ACCOUNT;
SYSDATE
---------
02-MAY-09
02-MAY-09
.......

SELECT fnGetAccountNameByAccountID(100000) FROM DUAL;
fnGetAccountNameByAccountID is a user-defined function to return scalar value (Account Name) based on AccountID parameter.

CREATE TABLE

The following query is used to create Employee table:

CREATE TABLE EMP
(
  EMPNO NUMBER(4) NOT NULL,
  ENAME VARCHAR2(50),
  JOB VARCHAR2(50),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2) NOT NULL
)

Get table information

There's a lot of ways to get table information.
To me, this is a simple way I often use.
DESCRIBE TABLE_NAME
OR
DESC TABLE_NAME

SQL Server:
sp_help Table_Name

Get datafile information

SELECT  FILE_NAME,
 d.TABLESPACE_NAME,
 d.BYTES datafile_size,
 NVL(SUM(e.BYTES),0) bytes_used,
 ROUND(NVL(SUM(e.BYTES),0) / (d.BYTES), 4) * 100 percent_used,
 d.BYTES - NVL(SUM(e.BYTES),0) bytes_free
FROM  DBA_EXTENTS e, DBA_DATA_FILES d
WHERE d.FILE_ID = e.FILE_ID (+)
GROUP BY FILE_NAME,d.TABLESPACE_NAME, d.FILE_ID, d.BYTES, STATUS
ORDER BY d.TABLESPACE_NAME,d.FILE_ID

EXISTS

Find accounts without contact

SELECT *
FROM ACCOUNT A
WHERE NOT EXISTS (SELECT 1
                FROM CONTACT C
                WHERE C.ACCOUNT_ID = A.ACCOUNT_ID)
You can also query like that:
SELECT *
FROM ACCOUNT A
WHERE A.ACCOUNT_ID NOT IN 
        (SELECT DISTINCT ACCOUNT_ID
         FROM CONTACT)

  

INTERSECT

Get the same records exists in two tables.
I never use this in my job.

SELECT CITY
FROM ACCOUNT
INTERSECT
SELECT CITY
FROM CONTACT

Get database triggers

SELECT TABLE_OWNER,
TABLE_NAME,
TRIGGER_NAME,
TRIGGER_TYPE,
TRIGGERING_EVENT,
STATUS
FROM dba_triggers
ORDER BY TABLE_NAME, TRIGGER_NAME

Get table space information

SELECT TABLESPACE_NAME,
 INITIAL_EXTENT,
 NEXT_EXTENT,
 MIN_EXTENTS,
 MAX_EXTENTS,
 PCT_INCREASE,
 STATUS,
 CONTENTS
FROM  dba_tablespaces
ORDER  BY TABLESPACE_NAME

Create mutiples linked servers

When you need to work with multiple databases from Oracle, for example: 100 databases with name like that DB01, DB02, ..., DB100. All of them use the same user name and password to query data such as scott/tiger. Your job is to connect them from SQL Server. First, you need to create 100 linked servers. It takes much time to create 100 linked servers like that by using create linked server screen.
You can do this by this script.

DECLARE @LINK_NAME VARCHAR(10)
DECLARE @SERVER_NAME VARCHAR(50)
DECLARE @COUNT INT
SET @COUNT = 1;
WHILE @COUNT < 101
BEGIN
SET @LINK_NAME = 'DB' + RIGHT('00' + CAST(@COUNT AS VARCHAR),2);
SET @SERVER_NAME = @LINK_NAME + '{Some name}';
EXEC master.dbo.sp_addlinkedserver @server = @LINK_NAME
, @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=@SERVER_NAME;
EXEC sp_addlinkedsrvlogin @LINK_NAME, 'false', 'domain\user', 'scott', 'tiger';

SET @COUNT = @COUNT + 1;
END

Get database information

SELECT  NAME,
       CREATED,
       LOG_MODE,
       CHECKPOINT_CHANGE#,
       ARCHIVE_CHANGE#
FROM     v$database

SQL Server: sp_databases

Get all user defined sequences

SELECT SEQUENCE_OWNER,
SEQUENCE_NAME,
MIN_VALUE,
MAX_VALUE,
INCREMENT_BY,
CYCLE_FLAG,
ORDER_FLAG,
CACHE_SIZE,
LAST_NUMBER
FROM dba_sequences
WHERE SEQUENCE_OWNER NOT IN ('SYS','SYSTEM')
ORDER BY SEQUENCE_OWNER,SEQUENCE_NAME

Get CPU Usage by Session

SELECT NVL(ss.USERNAME,'ORACLE PROC') UserName,
se.SID,
VALUE CPUUsage
FROM v$session ss,
v$sesstat se,
v$statname sn
WHERE se.STATISTIC# = sn.STATISTIC#
AND NAME LIKE '%CPU used by this session%'
AND se.SID = ss.SID
ORDER BY VALUE DESC

Get DB Version

SELECT *
FROM v$version;

SELECT *
FROM product_component_version;

SQL Server:
SELECT @@VERSION;

Get all DB objects

Get all DB objects including: table, view, stored procedure, function
SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE, OWNER
FROM all_objects
WHERE OBJECT_TYPE IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION')
ORDER BY OBJECT_TYPE, OBJECT_NAME

If you want to exclude system objects:
SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE, OWNER
FROM all_objects
WHERE OBJECT_TYPE IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION')
AND OWNER NOT IN ('SYS', 'SYSTEM')
ORDER BY OBJECT_TYPE, OBJECT_NAME

Get columns of a table

SELECT *
FROM all_tab_cols
WHERE TABLE_NAME = 'Your table name'
AND OWNER = 'Your owner name'

all_tab_cols is a system object in Oracle DB.

SQL Server:
using this is a simple way: sp_help 'Your table name'

Online database

I think there's a lot of reasons you want to create an online database. To me, sometimes I want my applications to be able to run everywhere as an SaaS (Software as a Service) with an Internet connection.
Some of online databases I have worked on: Salesforce.com, dabble.com, and Google Spreadsheet
Google spreadsheet is free as you knew but you need some skills about Google code such as Google API, Google Visualization, etc to be able to work on it.
dabble.com is also free if you make your dabase public as Common Creative License => I am using this way.
Salesforce.com is not free but you can test it with 20MB data when you register a free developer account.

UNION & UNION ALL

CONTACT DATA:
ACCOUNT_ID | CONTACT_NAME| JOB_TITLE
---------------------------------------------------
10000000001 | ABC | CEO
10000000001 | DEF | CIO
10000000002 | OPQ | CEO
10000000002 | XYZ | HR

SELECT JOB_TITLE
FROM CONTACT WHERE ACCOUNT_ID = 10000000001
UNION
SELECT JOB_TITLE
FROM CONTACT WHERE ACCOUNT_ID = 10000000002

Result:
JOB_TITLE
------------
CEO
CIO
HR

SELECT JOB_TITLE
FROM CONTACT WHERE ACCOUNT_ID = 10000000001
UNION ALL
SELECT JOB_TITLE
FROM CONTACT WHERE ACCOUNT_ID = 10000000002

Result:
JOB_TITLE
-------------
CEO
CIO
CEO
HR

HAVING

SELECT SALES_REGION, AVG(REVENUE)
FROM ACCOUNT
GROUP BY SALES_REGION
HAVING AVG(REVENUE) > 20000

List all SALES_REGION has average revenue greater than 20000

Note that HAVING is condition for group.
This query is NOT valid:
SELECT SALES_REGION, AVG(REVENUE)
FROM ACCOUNT
WHERE AVG(REVENUE) > 20000
GROUP BY SALES_REGION

Equi Join

List all accounts with their contacts

SELECT ACCOUNT_NAME, CONTACT_NAME
FROM ACCOUNT, CONTACT
WHERE ACCOUNT.ACCOUNT_ID = CONTACT.ACCOUNT_ID

SQL Server: can use INNER JOIN like that

SELECT ACCOUNT.ACCOUNT_NAME, CONTACT.CONTACT_NAME
FROM ACCOUNT INNER JOIN CONTACT ON ACCOUNT.ACCOUNT_ID = CONTACT.ACCOUNT_ID

Convert to number

SELECT TO_NUMBER(ZIP_CODE)
FROM ACCOUNT;

SQL Server:
SELECT CAST(ZIP_CODE AS INT)
FROM ACCOUNT;
You can also use CONVERT function in SQL Server

DECODE

SELECT ACCOUNT_NAME, BILLING_ADDRESS,
DECODE(COUNTRY_CODE, 'US', 'United States', 'VN', 'Vietnam', 'Undefined')
FROM ACCOUNT;

Explanation:
IF COUNTRY_CODE = 'US' THEN Return 'United States'
ELSE IF COUNTRY_CODE = 'VN' THEN Return 'Vietnam'
ELSE Return 'Undefined

SQL Server:
CASE COUNTRY_CODE WHEN 'US' THEN 'United States'
WHEN 'VN' THEN 'Vietnam'
ELSE 'Undefined'
END;

SYSDATE

SELECT SYSDATE FROM DUAL;

Return current date in server.
SYSDATE
---------
10-APR-09
SQL Server: SELECT GETDATE();

Math Functions

• ROUND(col/value,n)
• TRUNC(col/value,n)
• CEIL(col/value)
• FLOOR(col/value)
• POWER(col/value,n)
• EXP(n)
• SQRT(col/value)
• SIGN(col/value)
• ABS(col/value)
• MOD(value1,value2)
• LOG(m,n)
• SIN(n)
• SINH(n)
• TAN(n)
• TANH(n)
• COS(n)
• COSH(n)
  

String Functions

• LTRIM(col/value[, 'char(s)'])
• RTRIM(col/value[,'char(s)'])
• LENGTH(col/value)
• TRANSLATE(col/value,from,to)
• REPLACE(col/value,string,replacement_string)
  

String Functions

• LOWER(col/value)
• UPPER(col/value)
• INITCAP(col/value)
• CONCAT(col1/value1, col2/value2)
• LPAD(col/value,n[, 'string'])
• RPAD(col/value,n[,'string'])
• SUBSTR(col/value, pos[,n])
• INSTR(col/value,'string')
  
• INSTR(col/value, 'string',pos,n)

WHERE

SELECT EMP_ID, EMP_NAME
FROM EMPLOYEE
WHERE SAL > 10000;

Operators:
=, >, >=, <, <=, BETWEEN ... AND, IN (List), LIKE, IS NULL, IS NOT NULL, !=, <>, NOT BETWEEN .. AND, NOT IN, NOT LIKE, AND, OR

LIKE wildcards: %, _
IN with numbers: IN (123, 456, 789, 678)
IN with string: IN ('ABC', 'DEF', 'GHI', 'CDE')

ORDER BY

Default: ASC

SELECT ACCOUNT_ID, ACCOUNT_NAME
FROM ACCOUNT
ORDER BY ACCOUNT_ID;

SELECT ACCOUNT_ID, ACCOUNT_NAME
FROM ACCOUNT
ORDER BY ACCOUNT_ID DESC;

NVL

Convert NULL value

SELECT EMPLOYEE_NAME, SALARY*12 + NVL(COMM, 0) ANNUAL_SALARY
FROM EMPLOYEE;

SELECT NVL(LastModifiedDate, '2009-01-01')
FROM ACCOUNT;

SELECT NVL(REVENUE, 0)
FROM ACCOUNT;

SELECT NVL(BILLING_ADDRESS, 'DBA')
FROM ACCOUNT;

SQL Server: ISNULL(BILLING_ADDRESS, 'DBA');

Concatenation Operator

Without literal
SELECT EMPNO || ENAME EMPLOYEE
FROM EMP;

With literal
SELECT EMPNO || '-' || ENAME EMPLOYEE
FROM EMP;

SQL Server: + operator
SELECT EMPNO + '-' + ENAME EMPLOYEE
FROM EMP;

SELECT

List department number, employee name, and manager number in EMP table:

SELECT DEPTNO, ENAME, MGR
FROM EMP;

With Alias
SELECT ENAME, SAL*12 ANNSAL, COMM
FROM EMP;

SELECT * FROM EMP;

Get project issues on EPM

This is the stored procedure I am using to get issues information on my EPM

CREATE PROCEDURE [dbo].[spLoadProjectIssues]
@ProjectName NVARCHAR(255) = NULL,
@FromDate DATETIME = NULL,
@ToDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT t1.Title, t1.AssignedToResource, t1.DueDate, t1.Status, t1.CreatedDate
FROM ProjectServer_Reporting..MSP_WssIssue t1
  INNER JOIN MSP_PROJECTS t2 ON t1.ProjectUID = t2.PROJ_UID
WHERE  t2.PROJ_NAME LIKE @ProjectName
  AND (t1.CreatedDate BETWEEN @FromDate AND @ToDate)
END

Get project name and team members in EPM

SELECT t1.PROJ_UID
, t1.PROJ_NAME
, t3.RES_NAME
, CASE WHEN t1.WRES_UID = t3.RES_UID THEN 1 ELSE 0 END AS IS_PM
FROM MSP_PROJECTS t1
INNER JOIN MSP_PROJECT_RESOURCES t2 ON t1.PROJ_UID = t2.PROJ_UID
INNER JOIN MSP_RESOURCES t3 ON t2.RES_UID = t3.RES_UID
ORDER BY t1.PROJ_NAME

Yahoo! Query Language

Try YQL if you'd like to compare with pure query languages in SQL Server, Oracle, MySQL, etc.
That's very necessary if you want to develop an application by using web services.
Use this link https://developer.yahoo.com/yql/console to run sample YQL

Get all lookup tables and their values on EPM

SELECT t1.LT_UID, t1.LT_NAME, t2.LT_VALUE_TEXT, t2.LT_VALUE_SORT_INDEX
FROM MSP_LOOKUP_TABLES t1 INNER JOIN
MSP_LOOKUP_TABLE_VALUES t2 ON t1.LT_UID = t2.LT_UID
ORDER BY t1.LT_UID, t2.LT_VALUE_SORT_INDEX

Important tables in EPM

The following tables are important tables used in Enterprise Project Management you may need to query or build your custom reports

1. MSP_PROJECTS: contains all projects
2. MSP_RESOURCES: contains all resources
3. MSP_TASKS: contains all tasks for all projects
4. MSP_ASSIGNMENTS: which tasks a resource are working on
   

Code-blocks are not allowed in this file

Read here to solve your issues.

How to change title for ASPNET page in SharePoint Designer

I don't know why SharePoint Designer does not allow to change page title when I create an ASP.NET page from Master Page.
To be able to change the title, I have to use Javascript like that in asp:Content tag

<script type="text/javascript" >
document.title = 'My Title';
</script>

Change the default images on the home page

Read here

What Can Database Developers and DBAs Do About SharePoint?

If you are a DBA and are using it. Do you like it or dislike it?
Read here
Source: Redmond Developer News

LEFT JOIN in Oracle and SQL Server

You have two tables:
tblEmployee(EmployeeID, EmployeeName, DepartmentID, ...) contains all employee information
tblDepartment(DepartmentID, DepartmentName) contains all departments in your company
Each employee belongs to one department. Sometimes, new employees don't have related department. You want to display all employees with their related departments. If someone does not have any department, display null
In this case, you have to use LEFT JOIN to query your data
In SQL Server:
SELECT t1.EmployeeID, t1.EmployeeName, t2.DepartmentName
FROM tblEmployee t1 LEFT JOIN tblDepartment t2 ON t1.DepartmentID = t2.DepartmentID

In Oracle:
SELECT t1.EmployeeID, t1.EmployeeName, t2.DepartmentName
FROM tblEmployee t1, tblDepartment t2
WHERE t1.DepartmentID = t2.DepartmentID(+)

Query with DateTime field in Salesforce

If you have to work with DateTime field in Salesforce, note these differences:

1. Don't use quote in DateTime value, it's different with SQL Server or Oracle
For example:
SELECT Id FROM Account WHERE CreatedDate > 2005-10-08
In SQL Server, you must use:
SELECT Id FROM Account WHERE CreatedDate > '2005-10-08'

2. Use DateTime literals: YESTERDAY, TODAY, TOMORROW, LAST_WEEK, THIS_WEEK, NEXT_WEEK, etc.
For example:
SELECT Id FROM Account WHERE CreatedDate = YESTERDAY
In SQL Server, you may use:
SELECT Id FROM Account WHERE DateDiff(d, CreatedDate, GETDATE()) = 1

3. DateTime in Salesforce is UTC format, therefore you have to convert your datetime value to UTC format in your query which has comparision between date
For example:
In C# code, you have a control DateTimePicker dtpDateTime, you want to build a dynamic query based on this field. In this case, you can convert like that:
string strSQL = "SELECT Id FROM Account WHERE CreatedDate > " + dtpDateTime.value.ToString("yyyy-MM-ddThh:mm:ss.sssZ");

For more information, check here

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