Category Archives: Uncategorized

Check Those Settings

Recently, I was tasked with “enhancing” a third party application.  This third party application (TPA) outputs a bunch of files to a file share in a way that makes sense to the application, but makes no sense to a human. 

The Task

Make a copy of these files in a new location that makes sense to humans.

The Rules of Engagement

  • Do not modify any of the existing files or file structures created by the TPA.
  • Do not modify any of the TPA database objects.
  • Do not add any objects to the TPA database.

First thing that popped into my head was, “I can do that in PowerShell in less than 5 minutes.”  Kind of like the old game show Name That Tune, my confidence level was high.  Little did I know what was in store for me.  Less than 5 minutes turned into more than 5 hours.

In order to make the files and file structure make sense to a human, I had to use a stored procedure from the TPA to decode some bits.  Easy enough in PowerShell, just use my favorite command-let from dbatools.io, invoke-sqlcmd2.  But wait, one minor detail, I am not allowed to install any snap-ins or any other tools on the server where this would run.  In fact, I can’t even use the latest version of PowerShell, I am stuck with using PowerShell 2.0 <sigh>.

After I dusted off my PowerShell 2.0 documentation, I got my script written and started testing.  I processed several folders and their files before I received the following error while running my PowerShell script:

Invoke-Sqlcmd : String or binary data would be truncated.
The statement has been terminated.
At line:127 char:36
+ … MyResults = Invoke-Sqlcmd -ServerInstance $ServerName `
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Interesting.  I added some Write-Host statements for troubleshooting and found the offending entry.  Like any good programmer, I tested my stored procedure call in SQL Server Management Studio (SSMS) to make sure it really was a SQL Server error and guess what?  It worked just fine!  No errors what so ever.  WTH?!  This is where my tunnel vision sets in.  If it works in SSMS but not in PowerShell, then PowerShell must be the problem, right?  Well, sort of.

After repeatedly running the same piece of code and expecting different results (yes, like I said, tunnel vision), I threw my hands up and quit for the day.  I had restless dreams that night.  I was being chased by a giant SQLString Truncator (a very rare dinosaur from esoteric era).  I woke with a start at 4:30am, I had to be missing something.  All I can say is, thank goodness for Twitter and #SQLHelp.  I tweeted my issue and got immediate responses from some very smart folks, but nothing that resolved my issue, until I read between the lines of a tweet from Robert Davis:

SQLSolderTweet

That’s when the light bulb went on and Robert sent his follow up tweet:

SQLSolderTweet2

I copied all my settings from SSMS and added them to my PowerShell script.  One by one I commented them out until I was left with just one.  Low and behold that SQLString Truncator was really one of those pesky ARITHABORT Biters.

Lessons Learned

  • As soon as that tunnel vision kicks in, you need to stop what you are doing and take a break.
  • Ask for help, don’t keep beating your head against the wall.
  • Most importantly, don’t forget about your settings.  They can make all the difference in the world.
  • ARITHABORT Biters are much harder to catch in the wild than SQLString Truncators.

Triad Developers Conference – My Debut

I did it!  I did my first “real world” presentation this morning at the Triad Developers Conference in Winston-Salem.  What I mean by “real world” is not a PASS audience.  These were total strangers off the street that I didn’t know, well, there were some familiar faces and even a friend or two, but for the most part total and complete strangers that had varying backgrounds, not all technical in nature.

The feedback I received was very positive and even helpful, so I can make this presentation even better when I present it in Richmond, VA next weekend at SQL Saturday #610.

Huge thank you goes out to the organizers, volunteers and sponsors who made this event happen.  And a special thank you goes out to Doug Purnell (Blog | Twitter) for recommending me in the first place.

Speaking at Triad Developer’s Conference

I am honored to have been recommended by a colleague and selected as a speaker for the Triad Developer’s Conference in Winston-Salem on March 10, 2017.

The Triad Developer’s Conference is a low cost one day learning event put on by a myriad of local user groups in the Piedmont Triad area of North Carolina.  I attended the inaugural conference two years ago and it was fantastic.  So excited to be a speaker this year.

If you are in the Winston-Salem area on Friday, March 10, 2017, stop and say “Hi”.  I’d love to see you.

My First SQL Saturday – As A Speaker!

I am so honored and excited to have been selected to speak at SQL Saturday Richmond on March 18, 2017.

I will be presenting my What is Power BI? session.  There have been a ton of changes to Power BI since I last presented this, so I’m off to start updating my slide deck. 

If you are close to Richmond, VA on March 18, 2017 and want to learn more about what Power BI is, please register for this event and stop by and see me.  I’d love to have you as would the SQL Saturday Richmond team.

Struggling With Due Diligence

It’s been a while since my last post, there are various reasons for my absence:  work, illness, having our house rewired, etc., but one of the biggest reasons is my struggle with how a few folks in the SQL community treat the PASS organization and those that volunteer their time to PASS.

As many of you know, I do a lot of volunteer work for PASS.  I do this because it’s an organization that I truly believe in.  It was created by the community for the community.  It’s a place where data professionals can exchange knowledge freely, no strings attached.  To my knowledge there is no other community in the IT world quite like it, we even have our own hashtag on twitter, #sqlfamily.  But lately I’ve been struggling with how a few community members have reacted to policies/procedures/contracts.  Essentially starting a fire, pouring gas on it and walking away.

I’ve been involved with the Program Committee (they are the folks that select the content for the annual Summit) since 2010.  I took over my local PASS chapter when the existing chapter leader stepped down.  I help out with local/regional SQL Saturdays when my schedule allows.  I moderate 24 Hours of PASS when my schedule allows.  I volunteer while on site during the Summit as an Ambassador.  I’ve served on the NomCom (2012).  You get the idea, I am a true believer and not just in lip service, so when someone “attacks” an organization that I truly believe in, I get more than a little irritated.

One thing I have learned throughout the years of being a DBA is that you need to be able to prove a problem is NOT yours by exploring all the other possible areas that could possibly be causing the problem.  You have to look at it from all angles, not just the DBA angle.

We’ve all been there.  Customer calls to say application is slow and a trouble ticket is automatically created and assigned to the DBA team because the application uses a database.  This is somewhat akin to saying the issue with a car’s performance is the gas – all cars use gas so it must be the gas.  It’s tiresome and frustrating, but we go through motions to prove the issue is not ours.  In the financial world, it’s called due diligence.

This kind of due diligence has proven to be useful in other areas of my life, both personal and professional.  I would ask that those in the community please do their own due diligence BEFORE posting a blog, sending a tweet or starting the good old fashioned room mill.  Lately several community members seem to have forgotten the kind of influence they carry with the rest of the community and not done their due diligence before posting a blog, sending a tweet or starting the rumor mill.

You will notice that I did not name any names.  That would really defeat the purpose of this post.  I don’t want to start a fire with this post then allow gasoline to be poured on it with all the comments (not that there would be tons, because I don’t carry a lot of influence in our community – not a slight, just stating a fact) and then walk away.

I want you ALL to think about what you post, tweet or say BEFORE you do it.

The Long and Winding Road

TSQL2sDay150x150

“The Long and Winding Road” is one of my favorite Beatles songs, it reminds me of my dad, he was a huge Beatles fan.  My dad passed away almost five years ago, but I still miss him like it was yesterday and I still remember the advice he gave me when I was a kid, “Work hard and don’t let anybody tell you you can’t do something, there’s always a way.  It may not be easy, but there’s always a way.”  I credit that advice for the success I’ve had in my life, both personal and professional.

I started working with SQL Server back on Windows 3.11 (version 4.21a).  I know I’m dating myself here, but that’s kind of the point of this post.  We thought Windows 3.11 was so cool after having to deal with DOS for so many years, we didn’t think it could get any cooler.  But it did.  We got Windows 95, then 98, XP, 7 and now 8.  I can’t wait to see what they come up with next.

The same goes for SQL Server.

We started out with what I thought was a great tool, then they made it better.  We went from having to create devices to hold our database files and keeping track of each file growth, to just being able to create our database files and let them grow on their own.  I don’t know about anybody else, but that made me so happy.  No more sp_helpdb every night to make sure I could recreate my databases if needed in a disaster.

It used to be very hard to write T-SQL code if you weren’t familiar with the database objects, the old iSQL query window didn’t have an object browser so you couldn’t see a list of your tables, let alone column names.  Now we have intellisense built right in to the query window.  This feature alone is with worth its weight in gold.  It allows me to be so much more productive, type the first few letters and hit tab.  “BAM!” as Emeril would say.

BCP really used to be the only way to get data in and out of your database, then they gave DTS.  Once again it’s like going from DOS to Windows.  Then they came out with SSIS.  Holy Cow, I thought I’d won the lottery after writing my first package in under 5 minutes.  It truly was an amazing transformation (pun definitely intended).

There are so many other features that have improved along the way, too many in fact to list them all here.

It’s been a long road,

The wild and windy night
That the rain washed away
Has left a pool of tears
Crying for the day.
Why leave me standing here?
Let me know the way.

There were definitely many tears shed along the way to where I am today, tears of frustration and of joy.  But I wouldn’t change my long and winding road for anything, it’s made me who and what I am today.

SQL Server let me know the way…

Replication and TFS

 So, we’ve had this issue lately at work with our TFS build/deploys causing a significant amount of contention and only partially succeeding. 

A little about our environment: 

  • SQL Server 2008 R2 Enterprise Edition, 64-bit.
  • Visual Studio 2010
  • Team Foundation Server (TFS)
  • Using Transactional Replication
  • Using partial projects for replicated objects
  • 31 database projects in our solution

When we looked at the build log, we saw that several of our largest tables were being rebuilt, but not on a consistent basis.  At a cursory glance we could not see why, they appeared to be the same as what was in TFS.  After a couple of weeks we discovered that the tables were only being rebuilt right after a replication snapshot had been re-initialized.  The tables that were being rebuilt were tables with identity columns that were being replicated.  Turns out we had some mismatched configurations between TFS and replication.  When using partial projects in TFS to manage replicated objects, you cannot use the NOT FOR REPLICATION attribute on your identity columns.  You must also “manually” manage the identity ranges in your replication publications.

 Tips/Reminders when using TFS and transactional replication.

  1. Create a partial project in the publication database project containing all the tables and their respective attributes that you want to replicate.
  2. Add a reference to the subscriber database project by importing the partial project created in step one.
  3. Do NOT use the NOT FOR REPLICATION attribute on the identity column in the tables that are being replicated.  This is counter intuitive, but necessary if don’t want your tables being rebuilt every time between a snapshot initialization and your TFS build. 
  4. When you create the replication publications, you will need to set the identity column management property to manual for the articles that have identity columns.  Again, counter intuitive, but necessary.  Another thing that we had to do was remove permissions from our users to directly update the tables in the subscriber databases.   Our database supports a web based application that uses a generic service account to access the database.  We had an issue when new/inexperienced support staff would directly modify data in the subscriber database.
  5. In your .sqldeployment file(s) check the option named IgnoreNotForReplication.

The reason for all this hoop jumping is because the tools do not support database replication, nor do they support Change Data Capture (CDC).  These are great features, but when they are not supported in the tools that are available, it makes it very hard to want to use them. 

NomCom, what it’s all about

I submitted my application for the NomCom last week and the polls opened up yesterday.  I’ve seen an amazing amount of support not only from friends and co-workers, but also members of the community that I’ve never met.  That is what PASS is all about.  A community that encourages and helps others without ever having met someone and asking nothing in return.  I know I made the right decision to run for NomCom.  Have you voted yet?  If not, get out there and vote, your vote does count.  Read through the candidates qualifications and their applications and make your decision based on the that.  Good luck to all the candidates!

Just another SQL blog

I started out as a software developer back in 1996 in Denver, CO, doing Client/Server development in PowerBuilder.  The internet was just starting to blossom and my knowledge base was non-existent.  Over the past 16 years I’ve seen how the IT world has changed and grown, which in turn has increased my knowledge base.  My goal for this blog is to share some of the knowledge that I’ve accumulated over the years with others.

I am now a Data Architect, living in High Point, NC and I love what I do.  My focus is SQL Server.  I’ve worked with all versions of SQL Server since the infamous split from the Sybase code (a.k.a. version 4.21a).  I’ve worn all the hats that come with dealing with SQL Server, developer, administrator and now data architect.  I think this gives me a unique perspective on things and a well rounded knowledge base.

I hope you find what I post helpful, if it not interesting.

-SQL Swimmer

SQL Swimmer, you ask?  I am also an active US Masters Swimmer.