SQL Server with Mr. Denny


April 17, 2013  2:00 PM

Advertising != Engaging

Denny Cherry Denny Cherry Profile: Denny Cherry

Some people just really don’t get social media. Recently a twitter account which belongs to a SQL Server training company who based on their website is a legit company. In what I can assume was supposed to be an advertising campaign they completely ruined their social media standing, and in 7 minutes managed to get their twitter account suspended. Grant Fritchey summed up what they did perfectly in this tweet.

Their plan was apparently to message a lot of people (I counted about 70 including myself) with a link to their website. Apparently a lot of the people who received the message reported them as a spammer because within about 7 minutes of the first tweet their Twitter account was suspended thus ending their social media campaign.

I’m guessing that the goal of the tweets was to drive some business to their training program, but that has managed to backfire.

Lots of people make this mistake, and it’s a pretty easy one. Engaging with people that might become customers is a great thing, however engaging with those people doesn’t have to mean advertising and shoving your website down their throat. If people want to find your website they will. You don’t need to shove it at them. This is the digital version of the flier in the (snail mail) mailbox that everyone just throws away. But in this case the people getting the flier have a way to stop the flier from being delivered ever again. You’ve been blocked, and probably reported for SPAM which has gotten the account suspended and you’ll have to beg Twitter to let you have your account back.

Hopefully they have figured out that this isn’t the approach to take, and hopefully if you were planning on doing this you’ve changed your mind.

Remember Advertising <> Engaging and the goal of social media is to engage not to advertise.

Denny

April 12, 2013  5:03 PM

Recommended reading from mrdenny for April 12, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


April 11, 2013  2:11 AM

Using your US Phone in Europe Without Going Broke

Denny Cherry Denny Cherry Profile: Denny Cherry

There are lots of horror stories of people going to Europe on vacation or on business and coming home to massive cell phone bills. Even if you know that you are going to be paying a lot it’s really easy to end up with a few hundred dollar international roaming cell phone bill. There are thankfully some ways around dealing with this. How easy and expensive this all is really depends on who your US cell phone carrier is.

If you are on AT&T or T-Mobile things are actually pretty easy. The first step is to make sure that your cell phone supports all the frequencies that you need. If you have a quad band phone you are done with this step. Most of the newer 3G and 4G phones are quad band.  I recommend an Android phone, as that’s what I’ve got so I know that this all works perfectly.

The next thing you need to do is have AT&T or T-Mobile unlock your phone for you. They’ll probably charge you a few bucks, but pay it.

If you are on Verizon (which is my US provider) or Sprint you are pretty hosed and you’ll need to pick up an unlocked GSM phone. Google sells phones directly which are unlocked, which is where I bought one from.  If you are going to Europe a lot this makes sense.  If not you can pickup a used phone from eBay that’s either unlocked or easily unlockable pretty easy.  You can either unlock the phone here, or you can unlock it at most cell phone shops in Europe for a few dollars.  I recommend an Android phone, as that’s what I’ve got so I know that this all works perfectly.

The next step is to get a Google Voice account (this needs to be done while you are in the US).  Right now Google Voice is free (that may change, who knows).  Setup an account, you can use a separate number if you’d like, which is what I’ve done.

The next step is to get an app on your phone called GrooveIP.  This will let you connect to Google Voice without using the voice network in Europe.  Now this app is only available for Android phones.  There is an app called Talkatone which I haven’t tried which is available for Android and iPhone.  Talkatone routes the calls through Talkatone’s servers, while GrooveIP routes calls directly from Google Voice to your phone via Google Chat.

If you want people to be able to send you text messages you’ll need to install the Google Voice app.  This app is free from iTunes and from the Google Play store.

Once this is all setup and working while you are at home in the US you are good to go.

When you get to Europe your first stop should be to the shops at the airport after you clear customs and immigration.  Tell the person behind the counter that you need a SIM card and a top up voucher.  The back of the SIM card box will probably tell you which top up voucher you need to purchase to get the unlimited data plan.  If they don’t offer an unlimited data plan go with something which has a large data usage.  Cell phone calls going over the data network are about 1.2 Megs per minute.  So a Gig plan will handle about 900 minutes of voice calls.  Install the SIM card, if this is your US phone don’t loose your US SIM card as you’ll need that when you get home.  If you don’t know how much top up credit you need, ask the guy behind the counter.  He probably knows as people ask him all day long.

Make sure that you read the instructions on the SIM card (or have someone translate them for you if they aren’t in English) as to how to activate the unlimited plan if that’s an option.  The unlimited plans usually don’t activate themselves and you’ll burn through a LOT of money on a pay as you go plan.  Usually it involves sending a text message or making a free phone call to activate.

At that point you can launch GrooveIP or Talkatone and make calls to the US for free.  This works because GrooveIP and Talkatone send your call over the data network instead of the voice network like a normal phone call.  Now if you need to call your hotel or a phone number within the country that you are currently in you’ll need to use the phones normal dialer so that you aren’t using Google Voice to make that international call.

If you aren’t on an unlimited plan be sure to keep an eye on your data usage through the phones normal metering feature to make sure you don’t run out of data service, and swing by just about any supermarket to purchase a top up card for the phone service.

Keep in mind that if you are jumping between countries while in Europe (which is really easy to do) you’ll need a different SIM card and plan for each country as the pre-paid services don’t have international roaming on them.  If you are doing this I’d recommend writing the country that the SIM card goes to on the SIM card so that you can try and use it again in the future.  Some of the phone providers lock out the cards about 180 days without use and some don’t.

Hopefully this helps on your travels.

Denny


April 5, 2013  5:18 PM

Recommended reading from mrdenny for April 05, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


April 3, 2013  2:00 PM

How Many SQL Server ERRORLOG Files Do I Keep?

Denny Cherry Denny Cherry Profile: Denny Cherry

When working with SQL Servers there are lots of settings that people like to tweak to make their SQL Server run smoother.  One setting that I really like tweaking is the number of ERRORLOG files that are kept.  By default SQL Server keeps the last 6 ERRORLOG files around deleting the last one each time that the instance is restarted (or sp_cycle_errorlog is called).  But if I need to dig back a really long time 6 files may not be enough, especially if you cycle the ERRORLOG every day or week.

Because of this I will typically change the number of ERRORLOG files which are kept on the server to 20, maybe more (if the log will be cycled ever day).

You can change this setting from within Management Studio by right clicking on the “SQL Server Logs” folder in the object explorer and clicking “Configure”.  Just change the setting and click OK.  The system accepts any value from 6 to 99.  You can also script this using xp_instance_regwrite system stored procedure.

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 20
GO

Hopefully this comes in handy.
Denny


March 29, 2013  5:04 PM

Recommended reading from mrdenny for March 29, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


March 27, 2013  2:00 PM

Using Event Viewer Logging for SSIS Performance Trouble Shooting

Denny Cherry Denny Cherry Profile: Denny Cherry

So while working with a client recently I was going some performance tuning on SSIS for them.  While SSIS isn’t exactly my normal workload, the problem wasn’t really in SSIS, but just normal SQL Statements being called from SSIS.

While attacking this problem, the first thing that I did was look into the SSIS package and see what I had gotten myself into.  There were hundreds of objects within SSIS, most of them T-SQL Scripts of Data Pump Tasks.  So simply going through them one by one wasn’t going to be all that helpful.  The client didn’t have any logging to SQL Server or to a text file setup, but they did have the SSIS package setup to log to the Windows event log.

Opening the Application log greated me with one event for the start of each task, and one for the completion of each task.  Not exactly the most useful information ever, but better than nothing, so I’ll take what I can get.  The first thing that I did was export the Application Event log to a CSV file and download it to my desktop so that I could do the processing on my local machine.  The CSV file has a few hundred thousand lines, so I needed to figure out what I had via scripting.  The first problem that I had was that for each SSIS event there were multiple lines within the csv when opened in Excel which you can see below.

Thankfully this made the rows pretty unique looking, so an Excel Macro was able to clean this up pretty easily.  Before I got started I added some column headers into the excel sheet.  The default columns from Event Viewer are Date, Time, Source, Severity, Category, EventID, User, Computer, Description.  This covered columns A through I.  After that I put columns J through P as being Operator, SourceName, SourceID, Execution, StartTime, EndTime and DataCode as these are the specific items that SSIS is logging that you can see above.  Then it was time to bust out a little VBA and flatten out all this data.  The macro shown below looped through the data and flattened everything out so that all the data was on a single line.  Then is went through and removed all the white lines.  This macro took about 3 hours to run.

Sub CleanUpData()
Dim Row As Long
Dim DataRow As Long
Row = 32768
Do While Range("A" & Row).Value = ""
If Range("B" & Row).Value = "" Then
DataRow = Row
Range("A" & Row).Select
Else
If Left(Range("A" & Row).Value, 4) = " Mes" Then
Range("A" & Row).Value = ""
End If
If Left(Range("A" & Row).Value, 4) = " Ope" Then
Range("J" & DataRow).Value = Range("A" & Row).Value
Range("A" & Row).Value = ""
End If
If Left(Range("A" & Row).Value, 9) = " Source N" Then
Range("K" & DataRow).Value = Range("A" & Row).Value
Range("A" & Row).Value = ""
End If
If Left(Range("A" & Row).Value, 10) = " Source ID" Then
Range("L" & DataRow).Value = Range("A" & Row).Value
Range("A" & Row).Value = ""
End If
If Left(Range("A" & Row).Value, 4) = " Exe" Then
Range("M" & DataRow).Value = Range("A" & Row).Value
Range("A" & Row).Value = ""
End If
If Left(Range("A" & Row).Value, 4) = " Sta" Then
Range("N" & DataRow).Value = Range("A" & Row).Value
Range("A" & Row).Value = ""
End If
If Left(Range("A" & Row).Value, 4) = " End" Then
Range("O" & DataRow).Value = Range("A" & Row).Value
Range("A" & Row).Value = ""
End If
If Left(Range("A" & Row).Value, 4) = " Dat" Then
Range("P" & DataRow).Value = Range("A" & Row).Value
Range("A" & Row).Value = ""
End If
End If
Row = Row + 1
Loop

Do While Row 2
If Range(“A” & Row).Value = “” Then
Range(Row & “:” & Row).Select
Selection.Delete
End If
Row = Row – 1
Loop
End Sub

Once that was done I needed to set the start times for each event on the same line as the end time for each event. That way I wasn’t scrolling through looking for stuff while trying to do my data analysis. This I was also able to do with a little Macro. In this case I started on row 14 as that was the first entry that had SSIS data. You may need to change the EndRow=14 line to what ever line your data starts on. This puts the start time and the end time into Columns R and S, but only on the line which is the OnPostExecute line.

Sub FindRunTimes()
Dim EndRow As Long
Dim StartRow As Long
Dim SourceName As String
EndRow = 14
Do While Range("A" & EndRow).Value = ""
If Range("I" & EndRow).Value = " Event Name: OnPostExecute" Then
StartRow = EndRow + 1
Do Until Range("K" & EndRow).Value = Range("K" & StartRow).Value And Range("I" & StartRow).Value = " Event Name: OnPreExecute"
If Range("I" & StartRow).Value = "" Then
MsgBox "Ran out of rows to process for ending row " & EndRow
Exit Sub
End If
StartRow = StartRow + 1
Loop
Range("S" & EndRow).Value = Range("B" & EndRow).Value
Range("R" & EndRow).Value = Range("B" & StartRow).Value
'Range("T" & EndRow).Value = DateDiff("mi", Range("A" & StartRow).Value & " " & Range("B" & StartRow).Value, Range("A" & EndRow).Value & " " & Range("B" & EndRow).Value)
Range("R" & EndRow).Select
End If
EndRow = EndRow + 1
Loop
End Sub

Once that Macro was finished I needed to figure out which of the statements was taking to long to run. Putting a formula into the column Q marked the rows that I needed to look into a little more. In this case I was using anything with a run time of 20 minutes or longer as needing to be looked at.

=IF(HOUR(R595)=HOUR(S595)*60+MINUTE(S595),"", "X"), IF(HOUR(R595)*60+MINUTE(R595)+19>=HOUR(S595)*60+MINUTE(S595)+1440,"", "X"))

Once that formula was in there all the rows that were SSIS objects that took a long time to run had an X next to them. Now I still needed to ignore the rows which were containers, but those were pretty easy to figure out from the names of the objects as most of them had container in them (in the SourceName column which was column K of my Worksheet). At this point I was able to move back to the SSIS package and start digging through the package finding the objects which had long runtimes and getting those looked at.

The whole reason I went through this process instead of just adding logging to the SSIS package was for a few different reasons.

  • I didn’t want to make ANY changes to production if I didn’t have to. This SSIS package loads a production data warehouse which the business uses to run their company, so making changes to it wasn’t something I wanted to try.
  • If I had simply put logging into place I would have had to have waited another day to being looking at the problems as the ETL process only runs once a day.
  • The SSIS package takes 8+ hours to run, so sitting around waiting for it to run wasn’t exactly the best use of my time, or the clients money.

If you get stuck in this same sort of problem, hopefully this approach will help you out.
Denny


March 22, 2013  8:04 PM

Recommended reading from mrdenny for March 22, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


March 20, 2013  2:00 PM

Keeping Your Speaking Commitments

Denny Cherry Denny Cherry Profile: Denny Cherry

Many event organizers are way too polite to publicly admit that this problem exists as badly as it does.  But a problem has been showing up way to much recently.  The problem which I speak of is session speakers not showing up for their sessions, and the even bigger problem of speakers not showing up to an event at all.  Both of these happen on occasion, things happen.  But if they do happen, call someone, send an email, do something do try and let them know in advance that you aren’t going to be there.  Especially if you aren’t going to be going to the event at all.

If you aren’t going to be attending the event which you’ve been picked as a speaker for, you probably know that you aren’t going to be attending at least a few days in advance, at least the day before as that’s probably when your flight would have been.  You owe it to the event team and to the attendees to tell the event team that you aren’t going to be able to make it and that they should fill your spot with another speaker.

If you don’t tell the event team that you aren’t going to be there, you look like a putz.

In the last year there have been a lot of instances of speakers just not showing up for SQL Saturday events.  It has gotten bad enough that some of these speakers are starting to get a reputation as being people that won’t show up to present their session.  That means that these speakers have skipped their sessions several times, at least.  Just because a SQL Saturday is a free conference doesn’t mean that you can skip on your sessions.  Yes as a speaker you aren’t getting paid to attend, and it’s probably costing you money out of your pocket for airfare, hotel, rental car, etc.  But you knew that this was the case before you submitted, so just because it turned out to be more than you were expecting you need to either figure something out, or give the team as much notice as possible that you can’t make it.

If you have found yourself in this situation I implore you to stop submitting to the SQL Saturday’s unless you are 100% sure that you’ll be able to attend.  It isn’t fair to the event team that’s putting together the event to have to stress out over you not being there.  It isn’t fair to the event team that has to deal with upset attendees that the session that they wanted to attend isn’t happening (if you think that people don’t complain when a session at a free event doesn’t happen as scheduled you are very wrong).  And most importantly it isn’t fair to the session attendees who expected to see a session but aren’t able to because you didn’t show up.

If the event team knows in advance, even a day in advance that you aren’t going to be there they can find a replacement.  The speakers who are there will have all their other slide decks with them.  Odds are there’s another speaker there who can fill in.  If the session that you are scheduled to present doesn’t get presented then you are letting down the people who have cleared a day from their schedule to see you present that session.  You are letting down the event team who have trusted you to give the presentation.  If the event team didn’t trust you they would have given the spot to someone else.

A perfect example happened at SQL Saturday Nashville in 2012.  One of the speakers had to back out at the last minute due to a family emergency.  They told the organizers, and then organizers were able to find another speaker who had a session they could present on a similar topic.  The schedule didn’t need to be changed, the attendees weren’t upset.  Just instead of a woman presenting they got a man.  But they still got the information that they were looking for, and after all that is the most important thing when it comes to these events.

Sadly there isn’t really a great way to fix this, except for speakers to ensure that they present the sessions that they have committed to present.  As speakers if we don’t shape up about this the event organizers are going to start tracking this (I know that some events already do) which means that those speakers that don’t make it to their sessions aren’t going to get picked and it’ll probably end up being impossible to get off that list.  Which will lead to not being accepted for larger and bigger conferences.  As speakers if we don’t police ourselves and ensure that this doesn’t happen the people that run these events that we like speaking at will find a way to police us.  And I’m sure that it won’t be very fun.

Denny


March 15, 2013  5:19 PM

Recommended reading from mrdenny for March 15, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: