The Miracle Of SQL

SQL or the Structured Query Language, first appeared in 1974. The two blokes credited with writing it, Donald D. Chamberlin and Raymond F. Boyce got that remit so right that it stands today as a tower of software genius. It has been around for 42 years and is still the undisputed defacto standard for managing a relational data store. 42 years and counting! In the software industry that is eternal.

One of the precious few good things to ever come out of IBM, it went ANSI in 1986, 30 years ago and since then has only seen minor revisions. Of course there are issues, forgetting to specifically define the date, time and datetime standards arguably being one, but forgivable considering the enormity of the gift. Since 1986 there have been many diversions from the definition by many different vendors but the core is largely unchanged.

To Don and Ray, Thankyou.

Solving the unique name on delete issue

A common requirement for data management is to have a table that contains a name that must be unique within a table. If rows cannot be physically deleted from the table, a status field is usually used to indicate that the record has been deleted. If another row is added with the same name, an error will be generated because the new row fails the unique constraint on the index.

To solve this issue, add another field called (say) DeletionId that defaults to zero and populate that field with the id of the record when the record is flagged as deleted. Include this field in the unique index.

For example, assume the following table

create table MyTable
( 
   MyTableId int identity(1,1) not null
  ,MyTableName varchar(100) not null 
  ,StatusId smallint not null 
  ,DeletionId int not null constraint DF_MyTable_DeletionId default 0 
  ,etc... 
  ,constraint PK_MyTable primary key (MyTableId) 
  ,constraint UK_MyTable_MyTableName Unique (MyTableName, DeletionId) 
) 

To flag as deleted (if 2 means deleted and 5 is the id of the record):

update MyTableName set StatusId = 2, DeletionFlag = MyTableId where MyTableId = 5

By using the DeletionId it is possible to have many deleted records in the table with the same name but only one record in the table that is not deleted with the name. That is, the undeleted name is unique.

Oracle Linked Server Issues

If you get the error message below while trying to connect to an Oracle linked server:

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider “OraOLEDB.Oracle” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider “OraOLEDB.Oracle” for linked server “(null)”.

...you may need to set the "Allow Inprocess" setting on the provider to enabled.

To Fix:

1. Go to Server Objects\Linked Servers\Providers folder in SQL Server Management Studio on the Sql Server that has the linked server, locate the Oracle Provider (OraOLEDB.Oracle) and right click to Properties.
2. Ensure Allow Inprocss is checked
3. Recreate your linked server.

Also, make sure you have restarted the SQL Server service after you have installed the Oracle OleDB driver.

Look here for details.