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.
Saturday, May 28, 2016
Friday, May 20, 2016
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
To flag as deleted (if 2 means deleted and 5 is the id of the record):
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.
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.
Tuesday, May 3, 2016
Oracle Linked Server Issues
If you get the error message below while trying to connect to an Oracle linked server:
To Fix:
Look here for details.
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:
- 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.
- Ensure Allow Inprocss is checked
- Recreate your linked server.
Look here for details.
Subscribe to:
Posts (Atom)