tim laqua dot com Thoughts and Code from Tim Laqua


Changing Server Names in Excel 2007 Embedded Workbook Connections

Ok, so you want to migrate servers, eh? Well - all your business users pry have a couple thousand Pivot Reports lying around that point to the old server. We need to point all references to the old server at the new server.

There are two places these references exist (afaik) - The My Data Sources folder (Windows XP / Excel 2007) which holds all of the odc objects for the logged in user, and the real pain - Embedded data sources in Excel Workbooks.

For the odc objects - you can just loop through the My Data Sources folder files and do a little find/replace action.

For the Excel files - we need to first find all the files, then loop through them and hunt for embedded Connections. Once we find a connection, we can just blindly replace occurrences of the old server name with the new server name. The first thing we need here is a function we can call recursively to go hunt down all the excel files - then we loop through and play with the connections (when found).


How to replace the battery in a Polar F11 Heart Rate Monitor (HRM)

Disclaimer: The user manual for the Polar F11 clearly states to take the HRM to a Polar dealer and have them replace the battery - taking the back off could void your warranty. If you wish to be 100% sure that you maintain the water-resistant seal and do not want to void your warranty - or risk breaking your HRM (certainly a possibility when you take it apart...), take the unit to an authorized Polar dealer and have them replace the battery.

Step 1: Remove the back cover (4 little screws - make sure you have the right size screwdriver or you'll strip them out). Also note that the back has a water-resistant seal - keep this clean and remember that there is always a chance that the seal may be damaged if you do this yourself.
Step 2: Remove the internal unit from the watch casing (it just pulls out).
Step 3: Get something to pry the battery latch tab with (I used another small screwdriver)
Step 4: Take whatever you used to pry the latch tab out with and use it to pry the battery up by placing the device (screwdriver in my case) in the notch above the latch (see picture).
Step 5: Go to Wal-Mart and purchase a new CR2032 watch battery (you may want to do this before hand... I didn't know what battery it used so I had to take it out first) - runs about $3.50.
Step 6: Put the battery back in and put everything back together.

The Ellipsis … stop abusing it!

I have no idea how many times in the last week I've fell prey to the errant ellipsis. You see it in an instant message and then wait for the rest of the thought like a dog awaiting a promised treat. Alas, nothing follows. Do you ask for the rest of the thought? Do you wait and try to followup next time you see them in person? Or... Even worse... do they not know what an ellipsis means? For some reason more often than not lately I have been a victim of the "I just end sentences with multiple periods" syndrome. Stop it. You can not add extra periods to imply that you are super duper done with your statement. Sure, extra exclimation points are good fun - but extra periods like... mean something - they don't indicate extra done-ness.

Now, I'm certainly not a grammar specialist, but when I see an ellipsis, I get all excited waiting for the held back nugget of drama. Then when it doesn't come I am sad. So, so sad. So please. One period means "statement over," while more than one means "more awesome stuff to come."

Tagged as: , 8 Comments

Searching for keywords in all Reporting Services (SSRS) reports

During impact analysis for any changes to existing database tables, cube dimensions, cube measures, etc, it's nice to know which reports are going to horribly break before your end-users let you know about it 😉 All of the rdl content for reports uploaded to SSRS is stored in the ReportingServices database in the Content column of the Catalog table (in binary, of course) so here's what I came up with to get the list of soon to be broken reports:

SELECT [Path], ContentText
SELECT [Path], CAST(CAST([content]AS varbinary(8000)) AS VARCHAR(8000)) AS [ContentText]
FROM [catalog] cat WITH(nolock)
) a
WHERE ContentText LIKE '%ColumnName%' 
	OR ContentText LIKE '%columnname%' 
	OR ContentText LIKE '%MeasureName%' 
	OR ContentText LIKE '%AttributeName%' 
	OR ContentText LIKE '%etc...%'