Sometimes you may wish to filter an sql only if that would give you a record. For example if you have a table that holds several telephone numbers for a customer. The table has a flag "preferred” (True or false) that specifies a users preferred contact number. Now in the SQL we wish to create a list of users complete with telephone numbers. Here is where we hit a snag, due to input errors/business logic bugs. A number of users have been allowed to enter several contact numbers without selecting a preferred one. Therefore we can’t simply filter preferred numbers as this would lead to some customers not having a number were a number does actually exist.

The solution I propose is two fold.

Firstly, We need to know are preferred numbers available for a user. To do this we first make an sql statement that provides this info. In my example lets select all entries grouped by customer id where preferred.
(i.e.

Select CustomerID as CustomerID2 from TelephoneDetails where preferred group by CustomerID

. )

Secondly, To use a xnor logical clause. To create this we need a clause in our sql that states ( (A and B) or ( !A and !B)). Clause A will be simply is the telephone number preferred, clause b will be are preferred numbers available for this customer from above. To apply clause b we must first join the table above to the standard table. To allow non-preferred rows to be returned we need to use an outer join.

Our Sql becomes:

Select * from TelephoneDetails as a left outer join (
Select CustomerID as CustomerID2,  from TelephoneDetails where preferred group by CustomerID
) as b on a.CustomerID = b.CustomerID2
where (( a.Preffered = 1 and b.CustomerID2 is not null) or (a.Preffered = 0 and b.CustomerID2 is null))

This will now return a customers preffered telephone if set, and other telephone if not.

Given Data

CustomerID	Number	Prefered
1	1251	true
1	1255	false
2	1235	false

Standard Filter would give

CustomerID	Number	Prefered
1	1251	true

but our sql returns

CustomerID	Number	Prefered
1	1251	true
2	1235	false

Recently due to transposition errors ( I typed it in wrong,) i had to unlock the SA password on one of our servers (in my defence i didn't even know this database existed.)

Good thing with having an all Microsoft solution is that local admins ,if set up correctly, have access to sql servers as sysadmin.

To enable this behaviour though you must add the local admin to the user group SQLServer2008MSSQLUser$<servername>$<instance>. Which should already exist. A word of warning when I say local admin I mean local admin, not domain users with local admin rights I mean <servername>\<Administrator UserName>.

Now to log in using windows authentication using the local admin you must run the server in "single user mode.” To do this stop the SQL Server service. Add -m as a startup parameter than start the service.

You will now be able to login into to the sql server using enterprise manager and windows authentication. Once logged in unlock the SA user (you will need to change its password to unlock,). Once SA is unlocked restart the sql service without the -m parameter and log on as SA.

I would recommended at this point making a backup SA user and storing its details securely.

After using the set oracle_sid method I had a few problems with connections in sqlplus. In that even though I would specify one database I would get the one set in oracle_sid. For this reason I would suggest it is good practice to run the following code after each connection just to check your are connected to the right database.

select name from v$database;

Tested in 10g and 11g.