Lotto Redux: Finding Winning MegaMillions Tickets

Published / by Joe Johnson

3077639_GLast year during the big PowerBall mania I had some time stuck in DC for a client to write up a quick Excel spreadsheet for checking a bulk supply of tickets. Well, now it seems the changes to MegaMillions has ballooned the jackpot up near where the November 2012 PowerBall was at and again people are lining up to buy tickets.

Following the same principles as the PowerBall sheet, this one uses a quick sort and count logic to check each of the possible ways you can win against the Megaplier and the current jackpot to provide a total winnings. Every number that matches a winning ball will highlight in green and each one that doesn’t match will highlight red. If you win one of the smaller side-games, you’ll see a dollar amount listed along column X and displays the total winnings at AA2.

Please let me know if you have any issues or questions, and feel free to disseminate this if you’d like. Just remember to credit me, thanks!

Download the Spreadsheet

Yammer and Social Integration in CRM 2011

Published / by Joe Johnson

110212_1926_MicrosoftDy4As a part of the December 2012 update to CRM 2011, which also brought us the new and improved Polaris UI experience, Microsoft has gone all-in on the social integration with Dynamics CRM. Before we were given the ability to highlight new deals and signed business using the Post entity and the Activity Feeds views built right into CRM, but with Yammer integration we can now expand CRM news and details to the entire organization. With the added integration, users also gain input and commentary from the non-sales personnel inside of their company right as a part of their CRM experience.

Even prior to their acquisition by Microsoft, a lot of large companies have adopted the Yammer platform for their internal employees to have a safe, viable social media network for sharing information and ideas that would otherwise be relegated to mass emails, or just not discussed at all. Geared toward non-public information and discussions, Yammer is a Twitter-like social media network with a lot of the collaboration and search features of Facebook and the easy to use, short text-update interface of Twitter and Sina Weibo.

In a “former life” one of my employers had flirted with Yammer for close to a year, evolving from the free application to the paid subscription for internal collaboration and sharing. To me it was a success at sharing information and giving a good look into the operations and daily activities of a nationally dispersed workforce, but I think further personality and culture issues sabotaged the system from the outset. In the end, it was deemed too expensive for them to continue based on participation rates, but a strong and healthy company with a dynamic culture could benefit greatly from an app like Yammer. It bridges the gaps between multiple locations and multiple teams, giving that face-to-face interaction that just can’t be duplicated with a once a year Christmas party.

Screen Shot 2013-03-12 at 3.12.20 PMInside of CRM, the social media integration has been a large success in my implementations of CRM 2011 for several clients and my own company. Being able to get a live, streaming update of movement and progress in various sales activities and processes helps keep us all on top of status across the board. Views, searches, and reports offer one look at the sales organization as a whole, but nothing beats the ability to go into the system and see a listing of all of a single user or team’s activities and closed or new business opportunities in one timeline stream. Call me a Facebook-addict, but it’s how we’re getting used to seeing information.

So if your organization uses CRM 2011 and not Yammer, I highly recommend piloting the software with a group of your employees just to try out the collaboration and information sharing tools. If you do use Yammer, and you want to see your social media experience taken to the next level, then make sure you’re running the latest Roll-up in CRM and enable the Yammer integration today!

Finding Winning Power Ball Tickets

Published / by Joe Johnson

It seems nowadays everyone has an office pool for the Power Ball, pooling cash into a large pot and buying enough tickets to cover the cost of them so you all share in the massive jackpot. But the pain with having a lot of tickets is the fact that there are winners other than the jackpot, smaller wins that can be buried in 10, 20, or even 100 tickets bought for a pool. So how do you properly account for tickets and check them against the smaller prizes beyond just the jackpot?

With the daunting task of having to track 33 tickets tonight against the jackpot, ensuring I didn’t throw away any smaller winnings, I did what all good nerds do…I fired up Excel! By utilizing the Conditional Formatting system inside of Excel 2010, I setup rules to compare the values of all the winning pulls and compared them against each individual result to mark it green if a winner and red if a loser. Then I did the same for the individual Power Ball number, again green highlight for winner and red for loser. Using the COUNTIF function, you can create a logical statement to add the formatting

But that still leaves me to check each of the 33 lines individually for a winner, something that would have kept me up half the night. So again using the COUNTIF statement and some hidden fields, I can count the number of matching pulls in the ticket and if the count matches one of the additional jackpots, I can spot it! A few more nested IF statements later, and it gives me a pretty results column that tells me just how much money I lost.

For the record, it was $66 in tickets that netted me $12 in winnings, a grand prize loss of $54, woo hoo!

To make this easier for everyone, I’ve extended the spreadsheet to 50 tickets and pumped in some dummy data so you can play around with the sheet. If you distribute it, please give credit where credit is due, but otherwise enjoy and good luck on the lotto! Hopefully Saturday it’ll be up to $650 Million and I’ll win! I promise $10,000 to anyone that comments and shares if I win the jackpot, up to 500 people!

Download the Spreadsheet

Outlook Social Connector for Outlook 2010

Published / by Joe Johnson / 2 Comments on Outlook Social Connector for Outlook 2010

This week I upgraded my desktop to Windows 7 and Office 2010. My laptop has been running both for about 2 months, but I finally broke down and installed it on the desktop. Don’t think it was because I have a problem with 7 or 2010. On the contrary, I love them both…they’ve reignited a passion for PC computing that I was lacking in the post-Vista world. No, it was merely the huge pain it takes to rebuild your PC exactly the way you like it after wiping the OS. Four days later and I’m just getting settled in.

But one thing that I let fall to the side in my months of Office 2010 use was the Outlook Social Connector. It’s this handy little tool that will interface your Outlook connections with your Facebook and LinkedIn connections (and MySpace and others, depending on who makes a plugin and if you install it). When I redid my desktop I chose 32-bit Office to support the LinkedIn plugin and gave it a twirl. What I didn’t expect was how creepy it might become.

What the Social Connector does is aggregate all of the Social Media data about a person for you right inside of the Outlook interface. The new People Pane in Outlook is one place where it does this. The People Pane is a section at the bottom of the email preview screen that shows all of the relevant prior email, appointments, and chats with all of the recipients or senders of an email message. So when my business partner sends me an email, I can see his past emails and our past meetings all in one handy place; pretty cool! The Social Connector adds in LinkedIn and Facebook information streams to the People Pane, including…their picture.

Hello There!
When I was pricing out a POS hardware solution for a client this week, I spoke to Dell on their cool OptiPlex 160 line of mini PCs. The sales rep kindly sent me some links via email to go over the features, which was a huge help. But when I opened the People Pane, I saw it not only found his picture from Facebook, it also found his (unprotected) update stream, his LinkedIn profile, and placed all of it right into my Inbox.

Needless to say, it was a bit creepy to find this all here at my fingertips without the sender of the email knowing. After a little digging, I found at least three dozen others on various mailing lists and discussion groups where I’m a member who had wide open Facebook accounts identified by the Social Connector. Even when the account is protected, it shows me their profile picture. Sometimes even several profile pictures if they have a Facebook and a LinkedIn account with profile images designated.

While I don’t think I’ll uninstall this tool from Microsoft, I think maybe limiting the display to only my friends and connections on the various networks might be a good upgrade. Then again, maybe people should learn to secure their accounts so only friends see that information. Or we can follow the Zuckerberg model…I mean, you don’t have anything to hide, do you?

Photoshop: A First Experience

Published / by Joe Johnson

I’m an old school Dreamweaver user. I’ve been using it since the Macromedia days, starting out with version 2 in high school. Because it always tightly integrated into their suite of applications, I never had need to dust off Photoshop because I had Fireworks. It always served its purpose, but when Adobe bought Macromedia back in 2005 I figured my time was up. Adobe couldn’t possible maintain two separate image editors in their lineup, I thought, so I set out to try learning Photoshop so I wouldn’t be stuck using MS Paint when the other boot dropped.

Fast forward 5 years, and I still haven’t learned Photoshop and Adobe still hasn’t canned Fireworks. I guess I really wasn’t the only person out there using Fireworks, after all, despite the fact that I’ve never found another Fireworks user in my life. Still, it fits a niche and I guess Adobe is happy to fill it. Still, I’m drawn to Photoshop, if only for the truly amazing designs I’ve seen from people who use it. So, today I flipped open Photoshop and tried to design a simple one-page site for our email marketing service.

While I can appreciate the similarities between Fireworks and Photoshop in their CS5 iterations, they are only cosmetically similar. When it comes down to the actual operation, I was so lost it wasn’t funny. Eventually I figured out how the layers system works and managed to get the text tool to behave, even got a little fancy with the formatting, but it still wasn’t what I was expecting. Everyone who has scoffed at me using Fireworks has expounded endlessly on how wonderful Photoshop is and how easy it is to use. But, in my experience, I’ll stick with Fireworks for page design. The web slices and integration with Dreamweaver just makes it too good to pass up.

The result is here, and overall I’m pleased. I think it turned out well for my first shot at web design using Photoshop. Let me know what you think!

Rename a Server Core Install or Hyper-V Host Computer

Published / by Joe Johnson

I recently needed to rename a Hyper-V server (which is essentially a server core install of Windows Server 2008 R2). It took so doing, but I found the command.

Netdom renamecomputer oldname /newname:newname /userd:someuser /passwordd:*

Replace the computer names with valid ones and a domain admin user, and the command will prompt for your password. That’s it, reboot and you’re set!

Remove Exchange 2007 Public Folder Database Forcibly

Published / by Joe Johnson / 4 Comments on Remove Exchange 2007 Public Folder Database Forcibly

I’ve been beating my head against the wall for a week on trying to forcibly remove a corrupted Exchange 2007 install on one of my servers. The AD Configuration tree became corrupted and a restore did not fix it, so I rebuilt a new server and migrated the mailboxes there. Problem solved! Except, now how do I remove the old server…when I try to uninstall, I get warned that Public Folder database still has replicas. Well, the only way to get rid of that is to replicate them to another server, but that’s not possible with this crippled server.

Solution? Delete the database from the configuration partition of Active Directory manually. To do so, you need to launch an ADSI edit session in the MMC and add the ADSI snap-in. This requires the Server 2003 Resource Kit Tools or the Remote Server Admin features for AD in Server 2008 and Server 2008 R2. I usually go to a domain controller to do this, as it has the ADSI plugin already installed.

Once connected, expand the tree to your server in question. You’ll find the storage group with your public folder store listed:

There, you will find the store in the right-hand pane:

All set! I successfully uninstalled Exchange 2007 from this server as soon as that was done. Since it contained no vaild public folders, I didn’t care if any data was lost. But be careful, it will ruin your public folder stores if you have not successfully and verifiably replicated the public folders to your new server.

Anonymous LiveMeeting in OCS 2007 R2

Published / by Joe Johnson / 2 Comments on Anonymous LiveMeeting in OCS 2007 R2

This one drove me nuts for four days before I figured out what was going on. A client is deploying OCS 2007 R2 in their environment and ditching WebEx and GoToMeeting, as well as the other conferencing and collaboration features of the product.

However, the main goal of getting users outside of the organization to access meetings was just making me rip my hair out. No matter what I did, each time I tried to join a meeting it would pop up and demand credentials on the client’s domain. All three of my external test machines did the same thing and I could not, for the life of me, figure out why.

A quick search did reveal some permissions issues, and I corrected them according to KB 2018725, and they are running Windows Server 2008 Standard x64. After setting the permissions, though, I still had issues.

Finally, I managed to find a Microsoft forum post that explained it all. When I was testing the external access with the MOC client, I was logging in to the client’s network as my domain user. That was pre-populating the user information in LiveMeeting with my SIP URI information on the client’s domain. Deleting the account info instantly fixed the issue on all three test boxes. In addition, three new user machines that had never before connected to OCS were able to connect to a LiveMeeting session without any errors or prompts.

Good luck!

Upload SQL Backup File via FTPS

Published / by Joe Johnson

Recently, a client was looking to backup the primary database for one of their acquisitions to their home office. Because of security concerns, they can’t combine the users on the office network with their corporate network until their team can fully integrate the systems, but a full, regular off-site backup is needed ASAP. Since the local staff doesn’t include any technical people, instead we chose to utilize FTPS to push the file offsite after the backup.

Requirements:
– cURL (I used the 32-bit version of 7.20)
– OpenSSL DLLs (libeay32.dll and ssleay32.dll)
– An SSL-enabled FTP server (we use IIS 7 with a commercial certificate)

Steps:
To setup this job, I installed the cURL executable into the c:\windows\system32 folder of the remote SQL machine. I then copied the two DLLs above into the c:\windows folder. It took a bit of trial and error to figure out the DLL path, but this one worked like a charm.

Once the install was completed, I went in and added a job to the system that backs up the database to local media, overwriting any existing backups and verifying the media when complete. Then, it will launch a batch file (code below) to FTP the file to our corporate office. Here’s the job:

USE [msdb]
GO
/****** Object:  Job [CorpBackup]    Script Date: 03/05/2010 12:13:51 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 03/05/2010 12:13:51 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback</p>
<p>END</p>
<p>DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'CorpBackup',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=3,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'No description available.',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'SUBSIDIARY\jjohnson',
  @notify_email_operator_name=N'Corp IT', @job_id = @jobId OUTPUT
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [Backup The Database]    Script Date: 03/05/2010 12:13:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email protected], @step_name=N'Backup The Database',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=3,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=N'BACKUP DATABASE [Subsidiary_Engine_SQL] TO  DISK = N''K:\Corp\remote.bak'' WITH NOFORMAT, INIT,  NAME = N''Subsidiary_Engine_SQL-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''Subsidiary_Engine_SQL'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''Subsidiary_Engine_SQL'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''Subsidiary_Engine_SQL'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N''K:\Corp\remote.bak'' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO
',
  @database_name=N'master',
  @flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
/****** Object:  Step [FTP To Corp]    Script Date: 03/05/2010 12:13:52 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email protected], @step_name=N'FTP To Corp',
  @step_id=2,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'CmdExec',
  @command=N'c:\scripts\backup.bat',
  @flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @[email protected], @name=N'Weekly',
  @enabled=1,
  @freq_type=8,
  @freq_interval=33,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=1,
  @active_start_date=20100305,
  @active_end_date=99991231,
  @active_start_time=234500,
  @active_end_time=235959
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:

This job, if you look closer, will email our operator account and alert us to the success or failure of the job. We only need the backup about once a week, so we schedule the job for Friday night. When the job is complete, we can move the backup file off to a better location manually, just as a way to confirm we have it.

The batch script, c:\scripts\backup.bat, contains the command to upload the file to our FTPS server. One of the reasons I put curl.exe in the c:\windows\system32 folder is because it’s a cheap and easy way to circumvent the need for a full path. As an old colleague used to say, “It’s not that I don’t mind work, I’m just efficiently lazy.” Here’s the code for the script (munged, obviously, since it has our account password and FTPS site):

curl -T K:\Corp\remote.bak --ftp-ssl -k -u remotebackup:[email protected]! ftp://ftps.corpdomain.com/

Comclustion:
And that’s it, the file is uploaded automatically per the schedule we set in the SQL job. I get an email if it works or if it fails, and we have managed to get a backup regularly that doesn’t rely on non-technical users handling backup media or controlling off-site rotations. Use the script, the job, and hopefully it will help!

Path to SQLCMD

Published / by Joe Johnson / 1 Comment on Path to SQLCMD

At a major client we use SQL Server 2008 Enterprise Edition on Windows Server 2008 Enterprise Edition, all 64-bit. After 20 minutes of searching, I couldn’t find the actual path to sqlcmd.exe for a script I am writing. Tired of relying on Windows Search to find the executable path, I’m making sure I do my part to ensure it is widely known to those people who don’t seem to know (despite Microsoft and most of the MVPs seeming to think it’s a given to know the path without ever using it).

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE