Using SQL Server Management Studio to set up an Oracle Linked Server.

When setting up a linked server to Oracle from a SQL Express 2005 instance using the OraOLEDB.Oracle OLE DB Provider you need to be able to change the “AllowInProcess” property to true from the provider properties. Or it doesn’t work.

Problem… when connecting to a SQL Express 2005 instance there is no properties dialog available for the provider.

Solution:

EXEC sp_MSset_oledb_prop N’OraOLEDB.Oracle’, N’AllowInProcess’, 1

in the master database.

I keep deleting this then needing it again, so here it is!  It needs an update to discuss snapshot isolation ‘though.

Thanks to Andy Grout for working this stuff out with me…

All isolation levels affect the way in which you read data. They also affect the way others can update data because the type and duration of the locks differ.

Read Uncommitted

• Takes out no locks when issuing SELECT statements
• So you can read other transactions’ uncommitted data i.e. dirty reads

Read Committed

• Prevents dirty reads
• Takes out a shared lock when issuing a SELECT statement (this prevents updates)
• If another transaction has an exclusive lock your SELECT will block until the other completes
• Releases the shared lock when the SELECT completes, not when the tranaction completes
• So the same SELECT later on in the same transaction might return different data i.e. non-repeatable reads

Repeatable Read

• Prevents dirty reads and non-repeatable reads
• Takes out shared locks when issuing SELECT statements and doesn’t release them until the transaction ends
• So prevents any data you read from changing via updates or deletes
• But doesn’t prevent new rows from being inserted into other pages
• So your SELECT statement might still return more rows the second time you execute it i.e. phantom rows

Serializable

• Prevents dirty reads, non-repeatable reads and phantom rows
• In SQL 2000 and SQL 2005 takes out key-range locks, a special type of shared lock which prevents inserts into a range of values
• Ensures a SELECT statement always returns the same results within a transaction