Solution-ish: CF10 SQL Connection Timeout After VPN Disconnect

When doing CF 9&10 development locally I would constantly run into a SQL connection timeout issue where any page that required a database connection would throw the following error.

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.

My initial searches for solutions to get right back to work weren't fruitful and the only thing that worked for me was a full machine reboot. Sometime last week I got fed up with doing full reboots and spent a little more time digging. Before I had attempted to restart the usual suspect services: CF, CF ODBC, SQL, etc. but it wasn't until I read an article that mentioned DHCP that I thought to restart the DHCP Client service. Bingo! Maybe there's a more elegant solution or I'm missing some simple configuration option somewhere as other people who work similarly don't have this timeout issue. However, if you run into this issue there's a good chance that you just need to cycle the DHCP Client service anytime you disconnect from the VPN.

SQL: Adding and populating an order column in an existing table

Found this post in my drafts folder. I don't remember doing this, but I might as well put it out there.

I'm sure I'm going the long way around the tree here, but recently I had to add an OrderId column to a table where I was going to implement ordering that hadn't been there before. In my brain I was sure I could just temporarily change the column to an identity column so that SQL could do the initial ordering for me. I'd then remove the identity from the column and allow my users to change the OrderId. SSIS didn't agree. Maybe it was the lack of sleep or caffeine. So, here's how I ended automating this process. If this was SQL 2005+, I'd be able to use some nifty functions, but in SQL 2000 they don't exist.

UPDATE [mytable] set orderid = rank
FROM (

SELECT t1.RecId, t1.CategoryId, t1.RecName, COUNT(*) AS rank
FROM [mytable] t1, [mytable] t2
where t1.CategoryId = t2.CategoryId and t1.RecName <= t2.RecName
GROUP BY t1.CategoryId, t1.RecId, t1.RecName

)
o where [mytable].RecId = o.RecId