Jayson's Blog

[Update 02/11]  Please see my new post for more details concerning this utility, sorry for all the ping-ponging of posts concerning the migration tool.  The end result will be worth it.  [ /update]

Ok I’ll admit it, my previous post about migrating from .Text to Community Server was a bit arduous/tedious/hacky, so I’ve taken the time to write a proper managed console application that (attempts) to take the some of the pains out of the migration, namely not having to manually update each of the 9 scripts in the Transform Data Tasks (by auto-generating the DTS package for you...so yes, it still uses DTS, I wouldn't have it any other way).  You can download the console application here.  Before running the application, make sure you properly configure the DotTextCS.exe.config file; I’ve commented each key with a brief description, though it should be quite straightforward.  In a future release (soon), I’ll try to get rid of having to manually find your BlogID, UserID PostAuthor, SectionID, and SettingsID…but for now that remains the only manual process.  Once again, to get those values you will need to do the following:

• For the BlogID setting, look in the blog_Content table in your .Text database.
• For the other settings, they can all be gathered by looking at the cs_posts table in your Community Server database.

You should start with a fresh install of the Community Server database, then set up your blog(s) and make a test comment (so that the UserID (etc) information is logged to the cs_posts table for harvesting).  After configuring the .config file, run the console application, simply hit ‘y’ to indicate you want to create the package, and then run the package from Enterprise Manager (or you can execute the package directly from the console app, there is a key called ExecutePackage in the .config file…setting this to true sidesteps having to go to Enterprise Manager, though you might get an error or two…they can be ignored).  You will also need to execute the following SQL statements to tidy everything up, I’ll just quote my previous post:

If you have linked back to your own blog, execute the following SQL statement in Query Analyzer to change all the links to your new URL (using your URL’s of course, I’ve left mine in for reference):

DECLARE @originalURL varchar(100)
DECLARE @newURL varchar(100)
SET @originalURL = 'http://jaysonknight.com/blog'
SET @newURL = 'http://jaysonknight.com/cs/blogs/jayblog'
UPDATE    cs_Posts
SET       Body = REPLACE(CAST(Body AS nvarchar(4000)), @originalURL, @newURL),
       FormattedBody = REPLACE(CAST(FormattedBody AS nvarchar(4000)), @originalURL, @newURL)

If you have posts over 4000 chars long, you should be writing novels for a living instead of doing this.  If anyone knows of a work-around for the 4000 char limit, please share.

If you would like to update your hit statistics, use the following SQL statement (replace the db names with the .Text db you’re migrating from):

UPDATE    cs_Posts
SET       TotalViews = [your_db]..blog_EntryViewCount.WebCount
FROM    [your_db]..blog_EntryViewCount
WHERE    cs_Posts.PostID = [your_db]..blog_EntryViewCount.EntryID

UPDATE    cs_Posts
SET       AggViews = [your_db]..blog_EntryViewCount.AggCount
FROM    [your_db]..blog_EntryViewCount
WHERE    cs_Posts.PostID = [your_db]..blog_EntryViewCount.EntryID

I’ll get these dumped in the utility in a future release.  The same previous caveats apply, namely links/post categories don’t sort properly…also, your post categories will also show up as link categories, just delete them using the CS admin page…I haven’t figured out the column mapping for those yet.  I have tested this application on numerous (fresh) Community Server databases and am batting 100%, but let me know how it works out.  If you want the source code, ping me and I’ll happily provide it, and I’ll post it to my site once I get it cleaned up (and when it has all the functionality I want).  This is a huge improvement over my previous solution IMO…but maybe that’s just me.  Good luck, and let me know how it goes.

Forget everything I said earlier about SQL Server 2005 Management Studio (herein referred to as MS from now on); it’s slow, bloated, and just all around pathetic.  It seemed great at first, but after having done some heavy lifting with it lately it just seems…well, geared for idiots.  It’s just really been dumbed down to the point looking like Access (no offense to the Access folks).  Getting anything done takes an insane amount of clicks, and it just feels soooo slow compared to Enterprise Manager/Query Analyzer…very simple tasks take forever to finish up (for example, backing up a database…MS makes Enterprise Manager seem like a Ferrari).  I never thought I’d say this, but I actually miss QA at this point.

The latest thing that made me go wtf was the following:  I needed to load up some 40 SQL statements for execution earlier today…normally I would just load them into a new Visual Studio database project, select them all and run them…works beautifully.  Unfortunately VS2003 can’t connect/communicate with SQL2005 (Microsoft, if you’re reading this (and I know you do sometimes), please back port this into VS2003, not all of us will be using VS2005 strictly on our SQL2005 databases), so I had to recreate my database project in VS2005 and trying to add all the SQL files to the project threw an error (the generic “parameter not supported” error if anyone is keeping score) which was a real bummer.

So I selected all of the .sql files and opened them…instead of getting one MS instance with 40 tabs, wouldn’t you know that I got…you guessed it, 40 instances of MS, and each instance of course challenged me for authorization credentials so I couldn’t just right click the grouping on the task bar and close the entire group…I had to go through one by one and click connect (or cancel), then close the window.  Next attempt:  Open a single instance of MS and open the files from within the instance.  Un-bloody-f’ing real…they do indeed open up in individual tabs now, but I again get prompted for authorization creds for each file, and only 6 tabs are displayed below the menu bar.  It took a few seconds to find the very uintuitively placed icon that lets you see the rest of the windows, albeit in list form and not tabs.  Come on guys…after opening up tons of tabs in VS, you are given the very pleasant option of scrolling to the left/right to see hidden tabs and just all around easier to use.

So then after clicking the connect button 40 more times I start the task of running each statement.  First tab: I select the database I need from the dropdown, run the statements, go to the next tab…and the database selection is reset to “Master”…I have dozens of databases so doing this on each tab simply isn’t feasible, so I go off searching for a setting that allows me to specify the database to use for my MS session.  Oh wait…there isn’t one.  At this point I’m ready to fire up osql (where you can specify command switches which are session wide) and write my own batch file that does this for me.  Thinking that I’m probably not the only person on the planet who needs to run an entire folder of .sql files, I Googled around and found this great little utility that does it for me, which does exactly what I want it do on the first try.

While I’m griping, why doesn’t double clicking most objects (tables/sprocs/etc) in MS object explorer do anything…like maybe open the object and let me do something with it?  If I double click a table, it should either open in design view, or show me the data in it…doubling clicking a sproc should show me the sql statements, right?  No, you have to right click the object and choose from one of the menu commands now…no defaults.  And also gone is the “TOP” option when you open a table…if it has a gazillion rows in it, you’re gonna get all gazillion of them (well, you can click stop at anytime, but that’s still pretty pathetic)…if I’m in Enterprise Manager/Mgmt Studio, chances are good I’m just needing some quick and dirty data to look at, so give me the option.  It is very much worth mentioning that I usually do stuff like this from raw SQL statements in QA, but if I just need something quickly I’ll use EM/MS.  At first I really liked the integration of QA/EM into one tool, but now I’m absolutely craving separation again.  QA was quick, easy to use, and just all around fantastic compared to the query windows in MS.

Ok, enough ranting…but feel free to chime in, and if you know of any other SQL2005 rant posts out there please let me know as I’d very much like to see what other people have to say about it.  Cheers.

[Update]  The folks at Google must really like me...they've given me some more invites, 6 in all.  Keep the requests coming!

[Update]  Down to 1 invite.

[Update]  They gave me a couple more, spent one on the g/f...so I now have 4.  Geeediton!

[Update]  3 left.  Get 'em while they're hot!

First 6 comments on this post gets 'em (comments only please, and make sure you leave a way for me to get in touch with you).  Shaweeet!  I'll keep giving them away as I get them, so if you don't get one this round, keep your fingers crossed.

Feel free to link to this post as well.

Exciting news on the .Text to Community Server migration script; Robert McLaws came across my previous post and tapped me on the shoulder to beef up the engine for multi-blog migration scenarios so he could wrap a slick WinForms GUI wizard around it, so I’ve done just that.  From the prototypes he’s sent me, it looks very promising…who doesn’t like a sexy GUI over a command line interface, right?  The architecture of the migration engine itself is quite simple; pass it an array of .Text BlogID’s (from the blog_Config table), and it dynamically builds a corresponding DTS package for each of the BlogID’s.  The packages can either be saved to the server, or executed directly from the engine itself. 

There is only one thing that can be said about DTS…it’s f’ing fast.  I’m also equally impressed with how easy it is to dynamically generate DTS packages from code (though figuring out all the table/column mappings was the bulk of the work, and another story altogether).  I’ve posted my bits for the migration engine here (core engine is in DotTextCS.Engine.dll), though the frontend is a console app, connection properties need to be set via a standard .config file, and each user + blog needs to be manually configured via the CS admin pages before executing the utility.  See my comments here for further instructions.  Robert’s wizard will alleviate all of the manual-ness, so unless you’re feeling industrious (or have a trivial number of blogs to migrate), wait for the 1.0 release, hopefully to be posted within a couple of days, so stay tuned.

I’m barely even coherent at this late of an hour due to lack of sleep over the past week (stressed to the max due to all this bullshit), but I have finally figured out what happened.  A keylogger was installed on my machine, specifically this one (that link is safe to click on).  For a very humorous read, have a look at their “remote installation guide” (again, safe to click on) which explains in detail how to remotely install this software.  The wording is unreal…this was obviously a package written for hackers.

Long story short, if the scammer/hacker is careful enough and can get you to click on a rogue link, the software can be installed without you knowing, which in hindsight has to be what happened.  Who knows how it got on my machine, but after running several keylogger removal tools I finally tracked it down to this exact program.

I wouldn’t have even thought to start searching for a keylogger on my machine…I met a buddy of mine Sunday evening for a drink and mentioned everything that was going on; his first response was “did you get keyloggered?”  Of course my reply was “no way, I have a/v software, am behind a firewall (2 actually, one software and one hardware), and I run anti-spyware scans on a weekly basis, etc.”  But he got me to thinking, so I figured what the hell, I’ll check it out.  And as I stated above that’s when I found this very nasty package.

So here are the facts:

• It was installed about a month ago, which is right around the time I did my last eBay auction (coincidence?…perhaps, but who knows).
• It has the potential to log everything I did on my machine for the last month depending on how it was configured (I have of course zapped it and reformatted all of the machines on my network, and have changed all of my passwords…too little too late though).
• Whoever was monitoring it has A) my social security number (it’s my login ID to my online banking site…when I signed up years ago that was the only option they offered for an ID, and I have since learned that they’ve changed this requirement but didn’t fucking enforce it, i.e. I was never notified that I could change it) B) all of my bank account numbers as I use them to pay some of my bills online C) numerous credit card/check card numbers as again I use these to pay various other bills/order stuff/etc D) drivers license number E) account numbers/passwords to all of my utility companies…in other words, everything.
• Who knows what else…I’m deliriously tired and will post more later.

Norton A/V missed it.  Spybot missed it.  It’s that f’ing stealthy.  According to what I’ve read, the installation program can detect these programs and bypass them, which is completely ridiculous IMO, but apparently it worked.  What’s funny (if you can even say that) is that I did indeed take screenshots of the detection process, but being the brilliant person I am stored them on my drive…before formatting my machine.  Great job J.

You always think that it can’t/won’t happen to you.  It’s surreal, and I’m still reeling…not even beginning to think that I am now an identity theft statistic, along with how much time it will take to get this all sorted out.  Hell, it might not even be related to eBay.  I’m starting to think about attachments I clicked on in emails from friends (email addresses are easily spoofed, and given the amount of info that is available about people on the internet, wouldn’t be too hard to figure out email addresses that would be familiar to the “victim” and then spoof them).  Who knows.

Updates to come as they present themselves.  All of the involved parties are being less than useful at this point though.  Imagine that.  This absolutely sucks.

Sidenote:  Sorry for all of the negativity as of late.  I hope to get back to regular posting soon.