Deleting a duplicate record

Recently a friend of mine asked me about deleting a duplicate record from a table without deleting both records. Having been in a situation I drafted up a sample script and emailed it to him.

DECLARE @tbl1 TABLE
(
   id int,
   color varchar(50)
)
INSERT INTO @tbl1 (id, color) 
VALUES (1,'blue')
,(1,'blue')
,(2,'red')
,(3,'green')
,(4,'yellow')
,(5,'orange');

First off let’s see all the rows in the table.

SELECT * FROM @tbl1;

Now let’s list just the duplicate records

SELECT
   color
   ,COUNT(color)
FROM @tbl1
GROUP BY color
HAVING (COUNT(color) > 1);

Now let’s look closer at these duplicate records to see if there is any uniqueness

SELECT * FROM @tbl1 WHERE (color = 'blue');

As you can see there is nothing unique about either of the records. So how can you delete just one as opposed to deleting both? You could try the following but that is similar to the query above and it will target both records.

DELETE FROM @tbl1 WHERE (color = 'blue');

Well the quickest way to accomplish would be to do the following and use the SET ROWCOUNT statement.

SET ROWCOUNT 1;
DELETE FROM @tbl1 WHERE (color = 'blue');

--REVERT ROWCOUNT
SET ROWCOUNT 0;

-- LIST ALL ROWS
SELECT * FROM @tbl1;

Replaced Fuel Filter on the Ford F-150

Since I got the truck back in business, I’ve been driving it regularly. Over the last two weeks I noticed the vehicle seemed to have a hard time getting fuel. I suspected an issue with the fuel supply but wasn’t 100% sure. Every now and then when I came to a stop the truck seemed like it wanted to stall. As I continued to drive, every now and then it seemed like it was not getting the fuel despite the fact I did not let up on the gas pedal.

I did some research on google and a lot of threads recommended changing the fuel filter. I did additional searches to get an idea of how to replace the filter and could not believe how easy it is to access and replace. The local repair shop quoted me about $75 to replace it. I bought the part at AutoZone for $9.74 which is a savings of $65.26 had I taken it to the shop.

After everything was all said and done the test drive was very smooth. There was no sense of stalling when I came to a stop and as I was driving I did not feel any hesitation. The best part is that the truck starts up right away and I no longer need to keep my foot on the gas pedal to keep it running to warm it up. All this time I thought it was just an issue with the engine being cold. You live and learn!

Working with Sketch3

First off let me start by saying that this is a very easy to use and affordable editor. I actually learned about Sketch3 (developed by Bohemiancoding.com) from attending courses by BitFountain.io. I signed up for a few iOS development courses and the Sketch3 design courses. I started dabbling with it to get familiar with the interface and ran into a snag.

I was able to correct it by deleting the affected portion of the image then I copied the left-top portion of the image, flipped it and put it in place of the deleted one. I sent a tweet to both @Bohemiancoding and @Khari to see if they could shed some light on what I did wrong. Hopefully it will be something silly and not an actual bug in the software.

I figured I did something wrong so I stepped through the entire process again and suspect I had a mishap when I joined some of the images together. Turns out I did and scaling is working as it should.

Replaced the alternator in my 1990 Ford F-150

I’ve had this 1990 Ford F-150 since April 2014 and I’ve known there was an issue with either the battery, alternator or there was a parasitic draw that would kill the battery within 18-24 hours. I haven’t had the time to really look into it until now. I’ve done some research to get a few ideas of what to check for. I had a hunch it was the alternator after I replaced the battery and the next morning it wouldn’t even crank.

The reason that sparked this adventure was that my 2007 Nissan Frontier wouldn’t start either. I figured since I needed to head over to Auto Zone, I better make good use of my time and kill two birds with one trip. I took both batteries over and they tested them both. The battery from my Nissan turned out to be bad but the Ford’s test results were positive, so I left the battery with them overnight to fully charge it.

The afternoon of (Super Bowl XLIX) I mustered up the energy to tackle the alternator. Mind you, I am no mechanic but I managed to remove the alternator with ease. Of course I took a lot of photos to document the connections, bolts, etc… to make sure things go back the way they were removed. I also documented the socket sizes to remove the guess-work for the next time I have to perform this task. The tension and bottom bolt for the alternator used a 5/8 socket and the top bolt for the alternator used a 9/16 socket. Then there was the 8 socket for the battery brace.

The entire process including the removal of the alternator, the trip to Auto Zone to pick up the battery (left overnight to charge), purchasing and installing the new alternator took less than an hour. It cost me a grand total of $95.29 just for the part. I got a quote from a few local auto shops and it would have cost me around $375 – $440. So I saved myself around $279 – $344. A little elbow grease, a little research mixed with willingness and a can-do attitude equals big savings.

Saving for an emergency

“Creative Commons Money on Money” by 401(k) 2012 is licensed under CC BY-SA 2.0

Emergencies happen when you least expect them so it’s better to plan ahead and tuck away money into an emergency fund to be better prepared. Like Dave Ramsey said, “Once you have your emergency fund in place you tend to stop having emergencies.” So the goal is to prepare and build up your emergency fund to accommodate at least three to six months worth of your expenses.

To help keep me aligned with my goals I opted for an online interest checking account. I considered an money market account (MMA) but those are limited to six withdrawals. I don’t expect to have more than six emergencies arise in the course of a month but this gives me peace of mind knowing that there is no limitation. In the event I do have more than six emergencies I know I won’t incur any penalties for additional withdrawals.

I setup two automatic drafts to pull a specified amount from my primary checking to my online checking account. I carefully scheduled the drafts around my the time I receive my direct deposits. At the end of the month once I have gone through the budget I manually transfer another said amount to my online checking account. The automatic drafts are more of “out of sight out of mind”. My employer doesn’t have the option to split my direct deposits into two transaction otherwise I would have opted for that route. I also have it setup that any bonuses will direct deposit to the online checking. The same goes for any tax returns.

I have the online account for two specific purposes. One obviously is for the emergency fund and two a short term savings. The savings is to tackle my $55K Sallie Mae note which I anticipate paying off by July of 2017. With the money I save from paying off my school note I’ll roll that into the contributions I make to my online checking account and build up the funds to pay off my mortgage which should be approximately three years later. The interest generated isn’t significant but some is better than nothing and every bit helps.

They key is to stay motivated so I have written down the emergency fund total on a dry erase board along with the date that in which it should be fully funded. This hangs near my bed so I see it every day. I drafted a spreadsheet with dates, savings contribution amounts, rolling totals and the target dates which is accessible on my smartphone. I use a lot of methods to help keep me focused and they serve as daily reminders of the bigger goals ahead of me.

Taxes have been filed

I’m one of those people who likes to address tax season as soon as I have all my ducks in a row. I don’t like to let it linger to far into February if I can help it but by the end of January is my primary target. Thank goodness that a lot of companies have switched to paperless delivery. This makes being organized that much better. Being on a first name basis with your tax accountant doesn’t hurt either. As soon as I had all the documents, receipts, spreadsheets, etc… I merely emailed them to Rita and by the end of the day on Friday all I needed to do was to show up and sign.

I switched up the way I deal with our withholding. Before, like many others, I would contribute the max which would later yield a sizable tax return without bearing interest. Being a bit more smart about money in general and knowing the power of compound interest I aim to break even at the end of the year. A small return is better than having to pay so I work with my account to make sure my tax goals are aligned before the end of the year. This makes more money available on a monthly basis which allows me to take advantage of interest benefits from other investments. I see it as some interest made is better than none at all.

Back with a new agenda

It has been a while. Many things have changed. Priorities, interest, life, focus. I was so caught up in the technical field that I let a lot of other interesting and rather important topics slide by. I’ve since undergone a rebalance and decided to expand my horizon a bit more.

I’m still a SQL Server DBA but I’ve also been neck-deep in family, finances, aquaponics, aquaculture, micro-farming, health, fitness, food, real estate, ios development while trying to keep up with taxes and investments. It’s pretty exhausting at the end of the day but it feels more complete.

Since my last post I’ve moved on from food industry to the health industry and it’s been an interesting learning experience. Very high paced, privacy intensive but I’m doing more than typical database administration. I’ve done a bit more with SSIS development and it’s been a great experience that I wish to continue.

With the use of YNAB I’ve been hitting the budget, reviewing our spending and making life changes to align us with financial freedom. A lot of eye-opening discoveries once you have the big picture of where your money is going every month.

My personal studies on investing leads to late nights but gives me more of a better understanding when it comes time to rebalancing. Something I never did before. This gives me more control over what I am investing in. Got to build a better nest egg.

Thanks to BitFountain, GangplankHQ and the iOS community I’ve been learning iOS development and have a few projects underway to better me in my journey. Nothing fancy but realistic applications. Hoping to have something in the apple store soon.

I’ve kicked up my gardening skills a bit. Implemented a good size aquaponics system that will perpetually feed a family of four. With aquaponics I am able to raise fish (for consumption) that provide a fertilizer for my fruits and veggies. Many dishes have been served with organic goodies from the aquaponics system.

Our little micro-farm now has five free-range, egg laying hens that are a year old. We collect 4-5 eggs a day from these gals. Our seven pecan trees produce about 200 lbs every year and our orange tree is cranking. We will be converting our old stored into a 12’x22′ greenhouse. This will hold our new aquaponics system which will be twice as large which will incorporate a fish farming setup.

Lots of things happening so it’s never a dull moment.

Consolidate multiple records into a single row

I worked on a restore script that had to consume values from the network share and produce a restore statement. This database happened to be striped to eight files. I needed a way to construct the restore statement into a single row and I was able to achieve the desired results using the following syntax.

DECLARE @temp TABLE
(
	id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
	,colors VARCHAR(30)
)

INSERT INTO @temp (colors)

VALUES ('Red')
,('Blue')
,('Green')
,('Yellow')
,('Brown');


SELECT colors

FROM @temp


SELECT 'Colors' = 'RESTORE ' + 
(SELECT colors + ';'

FROM @temp 

FOR XML PATH (''), type).value('.', 'varchar(max)')

Here’s the end results.

Associating SSRS Scriptions to SQL Jobs

I’ve dealt with a lot of scenarios where I needed to troubleshoot and test a reporting services subscription simply because it failed. Most of the issues stem from the owner permissions, typos when it comes to the email addresses or because the person left the company and didn’t remove their subscriptions. Knowing where to look and how to associate the rather ugly naming convention of the sql jobs in reference to the subscriptions can eat up some valuable time. So let me save you some time by sharing a script I have used on numerous occasions.

The script joins a few ReportServer specific tables to sys.objects. I cannot recall if I had tested this against SQL 2005 but I do know it works well with SQL 2008 and it would be safe to say that it works with 2008 R2 as well. I am pretty certain it should execute without a hiccup on a 2005 instance. I haven’t tested against 2012 as of yet, but when I do I’ll definitely update this post.

To bread and butter. Here’s the script. The following will return all SQL Jobs associated to SQL Server Reporting Services subscriptions.

SELECT
	cmd='EXEC msdb.dbo.sp_start_job @job_name= ''' + sj.[name] + ''';'
	,ReportName=c.[name]
	,ReportPath=c.[Path]
	,LoginName=u.[UserName]
	,SQLJobCreateDate=sj.[date_created]
FROM msdb.dbo.sysjobs sj
	JOIN [ReportServer].[dbo].[ReportSchedule] AS rs (NOLOCK) ON sj.[name] = CAST(rs.[ScheduleID] AS NVARCHAR(128))
	JOIN [ReportServer].[dbo].[Catalog] c (NOLOCK) ON rs.[ReportID] = c.[ItemID]
	JOIN [ReportServer].[dbo].[Users] u (NOLOCK) ON c.[CreatedByID] = u.[UserID]
ORDER BY sj.[date_created] DESC

I like to know what was created today so I run the following:

SELECT
	cmd='EXEC msdb.dbo.sp_start_job @job_name= ''' + sj.[name] + ''';'
	,ReportName=c.[name]
	,ReportPath=c.[Path]
	,LoginName=u.[UserName]
	,SQLJobCreateDate=sj.[date_created]
FROM msdb.dbo.sysjobs sj
	JOIN [ReportServer].[dbo].[ReportSchedule] AS rs (NOLOCK) ON sj.[name] = CAST(rs.[ScheduleID] AS NVARCHAR(128))
	JOIN [ReportServer].[dbo].[Catalog] c (NOLOCK) ON rs.[ReportID] = c.[ItemID]
	JOIN [ReportServer].[dbo].[Users] u (NOLOCK) ON c.[CreatedByID] = u.[UserID]
WHERE (CONVERT(VARCHAR(10),sj.[date_created],101) = CONVERT(VARCHAR(10),GETDATE(),101))
ORDER BY sj.[date_created] DESC

The cmd column provides me the command needed to kick off the subscription. Typically most of the subscriptions are email based so if it hits my inbox I know I am good!

Intermittent Lockups with SSMS 2012

First off this post should have been pressed long ago (5/31/2012), but for whatever reason I merely saved it to draft rather than publish it. So without further ado…

Not long ago I started to encounter very intermittent lockups with SSMS 2012. I searched the web hi and low to see if anyone else had encountered this very problem, but I ended up with nothing remotely close. So I decided to turn to twitter and posted my issue to the sql community using the #sqlhelp hashtag.

has anyone experienced random unresponsiveness with SSMS 2012 to a point where you have to kill the process? #sqlhelp #sql

— Samson J. Loo (@ayesamson) May 24, 2012

It wasn’t long until I received a tweet from J. Verheul (‏@DevJef | Blog)

@ayesamson: Yes. Are you running into this issue constantly, or just once? Problem might come from shared DLL’s with VS2010…

— J. Verheul (@DevJef) May 24, 2012

And that’s how our glorious conversation began… Then @DevJef mentioned he reinstalled SP1 for VS2010 and that cleared up his problem.

@ayesamson: I had the same issue. I actually fixed it by applying VS210 SP1 again. This might help you as well! #SQLHelp

— J. Verheul (@DevJef) May 24, 2012

So I followed suit and applied SP1 for VS2010 and I am happy to report that I am no longer experiencing random lockups with SSMS 2012! Thank you @DevJef and thank you sql community for always being there!

Please visit Jef’s post (#SQLHelp – SQL 2012 Management Studio Freezes). His insight helped me tremendously and save me a lot of frustration and headaches!