Monday, June 29, 2009

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'));

Friday, June 26, 2009

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;

Wednesday, June 24, 2009

Salesforce: Tour the Sample Applications

Wednesday, June 17, 2009

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

Wednesday, June 10, 2009

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);

Tuesday, June 9, 2009

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';

Tuesday, June 2, 2009