It all began in March of 2001. I survived 3 rounds of layoffs at Earthlink, an Internet service provider, but didn’t make the final round. A few weeks later I was hired by a small company (at the time) named Marketlinx.

 

Marketlinx writes Multiple Listing Service software for Realtors. I generated reports from listing data, and helped with cosmetic ASP/Javascript type work, which required me to query the database from time to time. 

Over the next five years I became very familiar with the software, but felt that I wanted to focus more on the inner workings of the database. In April 2006 I was moved into a database programmer position. I was already familiar with the data and SQL Server 2000/2005. Anything I didn’t know I asked or researched on the web. I took many self paid for classes, I was totally amazed at what I could do.  

 

In early December 2008 my career took on a new direction, I was hired to join a team that would write a brand new data warehouse with SQL Server 2008, using SSIS, for a large Ecommerce company. This is something a DBA gets to do what, once or twice in a career? 

 

Now it’s April, our warehouse is almost complete. I’ve learned so much and evolved with the software. I’ve made new friends in the database industry, and try to keep up with the not so local Nashville SQL Server User group. I have hopes of starting a Knoxville group later this year. I also registered for the annual Microsoft SQL PASS convention in Seattle.

 

I love my job and I’m excited about what I do, I don’t claim to be an expert, but every day I learn something new. 

 

So there you go, that’s how I got to be a DBA, and I love it.

No Comments | In: Uncategorized | | #

You know you’re a nerd when you lay around at night wondering…

Right now our SSIS configurations are stored in a control table in our primary database. However when installing an SSIS package if you do not choose an xml config you do not get prompted to change the connection string for the database (or any value you selected to be configurable). The config on the QA/Stage box just automagically has the correct connection string. But, I’m wondering if this is a feature or a flaw. If I’m not the one installing the package this might be a bad thing… hmmmm.

Also, right now we have a slowly changing dimension checking every attribute of a product for changes, the table is is checking has at least a million records… after going to the Nashville SQL Server User group I was told that this isn’t performant, but one product has many many attributes, so using multiple lookups is not an option. I’m thinking we are going to have to somehow do a large join on my staging table, or find another solution. But really right now the process doesn’t take THAT long, so in reality, we will probably leave it alone.

No Comments | In: Uncategorized | | #

to a newer version of Wordpress, and It seems to have lost some functionality. Getting it fix asap.

No Comments | In: Uncategorized | | #

it’s small and very funny*…

Okay it’s not so small but it’s called by a web service which updates a table that fires a trigger that starts the job(not my design) which updates an activity table and also starts my SSIS packages spinning. But I also want the job to fire, should the web service fail to call it.

All in all it seems really messy. I need to make it cleaner.

Also, SQL server 2008 offers a beautiful job status API however  helpdesk has no access to my production server to use it to see what happened to a failed job at 3am. So I need it in a format that someone who has very little access to the database to see.

I once wrote an ASP web application that would take all job status and show a green, yellow, or red light on the page depending on the health of the job. I may have to do something like that again. Unless there’s something already out there that would save me the work.

So those are my two big questions for now. Input is welcome.

1 Comment | In: Uncategorized | | #

I’ve been working strictly with SSIS in SQL Server 2008 for a a little over month now. It’s an amazing tool.

One thing I encounter when generating horizontal tables from a data warehouse is the fact that I have to pivot tables. Last week, when trying to pivot data using the “Pivot Task” I got the error:

“Cannot convert the pivot key value _ to the data type of the pivot key column.” (The underscore replaces the name of the output column I had created. )

Thing is, I was trying to be fancy, In my OLE DB Source I had decoded my column names from numbers to text.

This bit me in the end. I couldn’t figure out why I was getting an error. But here was the solution.

Pass the numbers in and name them under your PivotKeyValue.

Correct:
SELECT [1AnchorColumn], [3PivotValue], [2PivotKey] FROM [AdventureWorksDW].[dbo].[Table]

output column 1 “Blankets” PivotKeyValue=125 Name=Blankets SourceColumn=LineageID from Input
output column 2 “HomeOffice” PivotKeyValue=126 Name=HomeOffice SourceColumn=LineageID from Input
output column 3 “Kitchen” PivotKeyValue=300 Name=Kitchen SourceColumn=LineageID from Input

Incorrect:
SELECT
       [1AnchorColumn], [3PivotValue],[2PivotKey] =
     case [2PivotKey]
      when 125 then ‘Blankets’
      when 126 then ‘HomeOffice’
      when 300 then ‘Kitchen’
      else ‘Other’ end
  FROM [AdventureWorksDW].[dbo].[Table]

output column 1 “Blankets” PivotKeyValue=Blankets Name=Blankets SourceColumn=LineageID from Input
output column 2 “HomeOffice” PivotKeyValue=HomeOffice Name=HomeOffice SourceColumn=LineageID from Input
output column 3 “Kitchen” PivotKeyValue=Kitchen Name=Kitchen SourceColumn=LineageID from Input

The logic was  that characters were coming from a field that was an integer. Thus the error:

“Cannot convert the pivot key value _ to the data type of the pivot key column”

Hope this helps you in your pivoting woes. I figured it myself! Brian Knight would be proud.

Toodles!

(I really will try to post more often!)

1 Comment | In: Uncategorized | | #

So far Ive found some serious lack of tools I need in SSIS. Don’t get me wrong, I LOVE SSIS. So we’ve had to either write in house custom components or purchase them from third parties.

The two big ones are SFTP Task and XML Destination.

Here is what I could find form Microsoft about the XML Destination:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=303815
I need to output some seriously complex XML… writing this component in house was the only option.

The other Component was a SFTP Task, now FTP is available, but I needed SFTP, my coworkers did a lot of research and came up with a nice plug-in from  cozyroc.com to handle this.

No Comments | In: Uncategorized | | #

I wanted to blog about whatever I had learned in the past week but really what came to mind was not answers, but more questions. Questions are good, they have a side effect of helping me learn. So here you go…

Migration:

What is the ideal way to push changes from your development server, to stage, and then production SQL servers with Microsoft products?

The way I am familiar with is using a third party tool, Apex Diff, to script data/schema differences add scripts to VSS, then having a custom script for any data cleanups… and push.

But Microsoft has built this all into Team system I think, I’ve never used it personally.  

Also, versioning… say I make changes to a table, “alter table add column blah blah”, what is the best way for VSS to pick up this change? I’ve always checked out the table creation script, manually added my alter script to the bottom with comments about the workorder where the new column was requested by the client, then checked in the script, hit ”f5″ to push just that portion to my database and called it a day.

But is there a way for VSS to automagically pick up these changes, and who made them… it would save me so much time to just hit “f5″ and be done.

The same goes for SSIS. How do I know what changes were made to SSIS packages before I got to them? I suppose I could compare the XML files… but I need more, I need to know why the change was made and who did it…

I guess what I’m saying is, if I only had Microsoft tools to work with, what are the best practices for pushing these changes to my databases.

All these questions are things I’ll most likely be researching and blogging about in the next few weeks.

This seems like a vital part of having a database, code change and migration process per Microsoft should be clear as a bell right?

3 Comments | In: Uncategorized | | #

Sometimes you need to check to see if a file exists before you put the path in your database. Per local rumor this is an undocumented sproc in SQL Server. Love it, use it. 1 means yeah 0 means no. Try it out!

xp_fileexist ‘C:\test.txt’  from SSMS.

No Comments | In: Uncategorized | | #

http://www.sqlsaturday.com/

I would LOVE to put together a SQL Server Users Group in Knoxville. Even more I would love to eventually put together a SQL Saturday. (link above) if your interested send me a message.

2 Comments | In: Uncategorized | | #

Database locking issues arose this morning for me. When two updates or selects happen to the same object and the same record, record/page locking occurs. In my defense I was running a script for someone else, also I didn’t realize that there were two recurring jobs that started moments into running my script. I had the DB in restricted user mode. I only knew of one job that was going to start this morning but I should have been done with my work way before it ran.

Here is a simple debug script you can run to see if you have locking on your database and what spids are causing it: 

SELECT status, SPID, CPU, Physical_IO, WaitTime, Blocked, HostName, Program_Name, nt_username,Login_Time, Last_Batch, Open_Tran, waittype, lastwaittype
FROM master..sysprocesses with (nolock)
where blocked >0 or open_tran > 0

Also there is the SQL Server 2000 sp_lock command MSDN article about it’s results is here.

After a phone call or two I managed to disable the jobs and kill off any spids that were keeping my script from finishing. I then reenabled/ran them safely when I was done. The angels sang, the publish was complete, and had passed testing.

Lesson learned? Be more attentive to what jobs are running on the server, ugh. *smacks forehead* Gah.

No Comments | In: Uncategorized | | #