Null Coalescing Operator

Remember this operator from your first Perl class back in 1994?  Well, if you don't it's a good one for your back pocket.  It works in Perl, Bash, C#, F#, Objective-C, Swift, and even SQL.  It is great for a quick way to do a Null comparison:

[C#]

string strNewValue = strRequestValue ?? "Default";

In this case, if strRequestValue is null, "Default" is assigned to strNewValue.

In SQL Server, it looks like this:

[SQL]

ISNULL(strRequestValue, 'Default')

Neither of these formats are super readable unless you are used to seeing them.  In the case of the SQL example, it looks very close to the IS NULL comparison, but whatever => the SQL team at MSFT sometimes does things like that.

It might be you want to do this instead:

[C#]

string strNewValue = isNullOrEmpty(strRequestValue) ? "Default", strRequestValue;

but I never found this to be very readable either.

I think the real power of Null Coalescing Operators is when it's used to create an object instance like this:

[C#]

private IList<Foo> _foo;
public IList<Foo> ListOfFoo
             { get { return _foo ?? (_foo = new List<Foo>()); } }

Boom!  You're off an running!

(Props go to @wilbursullivan who showed me this)

How to Fix Orphaned SQL Server Users

No, not this kind of Orphan -- too
bad we can't fix this issues as easily!

Just a quick entry this time.  However, it happens all the time: you restore a database to a new server and can't login to the database with the original usernames.  It happens because the usernames have become "Orphaned Users."

It's now easy to fix, as of SQL 2000 sp3.  It used to be REALLY hard before.

First find all your orphaned users, by running this command:

EXEC sp_change_users_login 'Report'

It will yield something like this:

jobean 0x2BB8F8255C002E47B232FB331B1698BA

From this, I know 'jobean' is an orphaned user.

So, run this command to fix it:

EXEC sp_change_users_login 'Auto_Fix', 'jobean'

The row for user 'jobean' will be fixed by updating its login link to a login already in existence.

The number of orphaned users fixed by updating users was 1.

The number of orphaned users fixed by adding new logins and then updating users was 0.

This hooks up the jobean user of the database to the jobean of the database server.  Did this fail (probably because you don't have a jobean user in the database server?)  Then issue this command to make a new user and wire it up to the jobean orphaned user.

EXEC sp_change_users_login 'Auto_Fix', 'jobean', 'login', 'password'

[Where login and password are those you want assigned to the new user]

Easy as can be.

Logging IIS POSTS

Have you ever needed to see the raw POSTS coming into your IIS-hosted web site, but don't want to change code to write it to a file?  Well, there is an easy way:

• Bring up IIS Manager
• Find the Site you want to log, and click on it
• Click the IIS icon, "Failed Request Tracing Rules".  Even though it says failed requests, it also will do successful requests.

• Select "Add..." in the right menu
• Select "All Content (*)" and "Next"

• Then put "200" in the "Status Codes" box.  You could add a range to get all requests: "200-999"

• Then press "Next",  "Finish"

A new folder under your default logs root will be built called "FailedReqLogFiles".   You will see it get filled with XML files that exhaustively log every aspect of all posts.

Remember - Don't leave it on long or it will blast your storage with tens of thousands of files.

IIS Web Deploy ERROR_DESTINATION_NOT_REACHABLE

This is a common error with setting up Web Deploy.  What happens is you install Web Deploy from Microsoft's online link, check that it's a running service, then find it only yields a ERROR_DESTINATION_NOT_REACHABLE error.

Of course, you troubleshoot pinging the remote site.  That's fine.  Then you try telnet to see if the port is available.  It turns out it isn't.  A search of the web yields this (with it's good, but not obvious, clue):

ERROR_DESTINATION_NOT_REACHABLE

Diagnosis: Web Management Service or Remote Agent is not installed or unreachable on the remote computer.

Resolution: Verify that Remote Agent Service or Web Management Service are started on the remote computer, depending on which one you are connecting to. You can do a "net start wmsvc & net start msdepsvc" on the remote computer to ensure these services are started. Also, ensure that a firewall is not interrupting communications with the destination.

The problem is that Web Deploy installs itself, but it doesn't install the Web Management Service.  It also doesn't warn you that it's not available.

So, to install WMS, go to Server => Roles,  then Web Server (IIS) => Management Tools => Managment Service.  Install it...

And Voila!  You're all set!

Restoring SQL Server Database: Exclusive access could not be obtained because the database is in use

Here is the scenario - you are in a rush to restore a down SQL database.  Then you get the dreaded:

Exclusive access could not be obtained because 
the database is in use.

Don't panic.  You can do two things.

Option 1

If you are using the SQL Manager Tool, you select "Options" from the side panel.  On there you will find a checkbox to go into single-user mode for the Restore.  This will do the trick.

If this option is grayed out, cancel the restore operation, then bring it up again.  Check this box first before doing anything else.  It should just work.

Option 2

If you are using the command line, use this command:

USE master
GO
ALTER DATABASE DATABASE_NAME
SET SINGLE_USER

Now do your restore as normal.

Afterward

Don't forget to make sure you are in multi-user mode!

USE master
GO
ALTER DATABASE DATABASE_NAME
SET MULTI_USER

Finding a Dropped Table

I recently needed to find a dumped table that was somewhere in backups, but where??? Obviously, the db stores this in the transaction logs, but its really hard to find if you don't know where to look.

A great query for finding this dropped table is this:

SELECT * FROM fn_dblog (NULL, NULL), (SELECT [Transaction ID] AS [tid] FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] LIKE '%DROPOBJ%') [fd] WHERE [Transaction ID] = [fd].[tid];

This will return back everything you need to get this table off of your backup:

[Current LSN], 3 [Operation], 4 [Context], 5 [Transaction ID], 6 [Description]

Reset Cisco PIX 501 to Factory Settings

I recently had to reset my Cisco PIX 501 to it's factory settings. I had trouble getting the 501's web interface to work. I think more modern web browsers are blocking its access. So, I had to revert to the terminal connection. It wasn't hard, but directions are rather limited, so I decided to write this post.

Steps to Reset to Factory Settings

This is the older version of this cable.  Cisco
now has a newer version, but this is what came with the 501

1. Find the Console Cable (Blue cable with serial connection dongle). This was my biggest problem!
2. Plug the serial side into a old desktop computer, and RJ side into the 501 terminal port
3. In Windows, bring up Hyperterminal.
4. Use the default Hyperterminal settings, 9600, 8, N, 1
5. Once the Cisco shows up within Hyperterminal, go into the configure terminal mode, by typing:
   
   en
   (then enter your password)
   
   Once you get authenicated, you should see the pound sign show up at the command prompt, like this:
   
   CISCOPIX#
6. To get into configuration mode, type:
   
   conf t
7. Finally, to start the process, type:
   
   configure factory-default
8. Wait for the unit to reboot and you should be set with a factory-set 501.