Recently trying to debug some really cryptic T-SQL scripts.

Guess what this is trying to do?

--

DECLARE @dt varchar(6)
SET @dt = RIGHT(CONVERT(varchar(4), YEAR(getdate())),2) + REPLICATE('0', 2 - LEN(CONVERT(varchar(2), MONTH(getdate())))) + CONVERT(varchar(2), MONTH(getdate())) + REPLICATE('0', 2 - LEN(CONVERT(varchar(2), DAY(getdate())))) + CONVERT(varchar(2), DAY(getdate()))

--

Answer:

It’s trying to print the current date in YYMMDD format.

Uhm, there’s a much better way to do this.

Try CONVERT.

--

SET @dt =  CONVERT(VARCHAR(6), GETDATE(), 12)

--

I still quite enjoy debugging or refactoring T-SQL scripts, but sometimes I’m just amazed at what I still see …

“Choose a job you love, and you will never have to work a day in your life”. – Confucius

It’s amazing how many, and how consistently, I get “reluctant would-be DBAs” in my class. I always do a quick survey or poll when the course starts where I ask the students why they’re taking the class. My classes are part time studies courses, and usually taken by students who are already working and just doing continuous learning, or are making a career change.

I’ve seen different types of reluctant DBAs:

• Some of the students do say they were “forced” to take the class by their bosses, because they need it for their corporate professional development program.
• Other students have other job titles (PMs, data entry clerks, accountants, marketing managers), but are taking the course because they were recently assigned the title “DBA” — because they happened to know a little bit more about the data or the system than anybody else in the company.
• Still other students truthfully admit they want to become DBAs because they heard it’s a stable and secure job, and that’s what they want even though they have no interest whatsoever with databases.

When they ask me “How do you become a DBA?”, I answer them with another question “Do you like working with databases?”

If they say “Yes”, then I go all gung ho, and share all my RSS feeds and twitter people I follow, and articles I’ve read and written. Then I’ll talk their ears out on how I love working with databases too – SQL Server specifically.

If they answer “I don’t know” .. then I think there’s a potential there to start liking databases more as they work on it more. Which is a good thing. That’s when I start encouraging them to learn more about it, refer them to BCIT’s elibrary (Books 24X7) for additional ebooks, Brent Ozar’s site, SQLServerPedia and Brad McGehee’s book “How to Become an Exceptional DBA”

When they answer “I don’t” or “Absolutely hate it” but they’re there for the title or the job security (what job security? is there such a thing?), that’s when I start rambling that they should try to figure out what they want to do, and that they shouldn’t be in it just because they think it’s a “stable” job, or somebody said “you should be one”.

As with any other profession, becoming a DBA almost has to be a calling. It’s either you like (better yet, LOVE) working with databases, or you don’t.

I don’t discount the fact that you can learn to love working with databases. Take Jorge Sergarra (@sqlchicken) – he mentioned in his addendum to his “3 things post” that

“…One thing I forgot to mention in my post is that in college I HATED the database class. I was almost reduced to tears because I couldn’t figure out who to create a proper stored procedure. It’s funny, years later, that I absolutely love all things database! …”

If after trying the DBA field for some time, and you’re quite sure you’re not enjoying the work, you should get out and find your calling. Don’t choose to be “imprisoned” in a field you don’t want to be in. You’d want to be in a field where you come to work all excited and giddy about what you’re going to do for the day, what new things you’re going to learn, and what impact your work is going to make for the company or people you’re working for.

Glenn Alan Berry (@GlennAlanBerry) tagged me, so here goes my version of “3 things that got me here” …

I wanted a job that didn’t involve hiking up the mountains in winter …

I took Geology in University (while working almost full time closing shifts at a local McDonald’s), spent a lot of years trying to get the degree and learning all about rocks and palaeontology and plate tectonics and petrology, but realized it was not for me. How did I know? First, Im not really the sporty athletic type. So hiking up the mountains was not that appealing to me.

In University, we had to do a field work up one of the mountains here in British Columbia in the dead middle of winter, that’s when I realized working in really high up cold snowy environments just does not sit well with me. As soon as our field work was done, I went back to University, dropped all my Geology courses, and took an MIS (management information systems) course – because my perception was MIS courses didnt involve hiking-up-the-mountain-in-winter.

What I didn’t really realize was that taking MIS meant I had to use a computer, a statistics application (I think it’s called JMP), and Excel.

And boy did computers and Excel ever scare me. I remember going home one time crying to my brother, because I cannot wrap my head around the exercise we did that day. I had to put some data in some of the columns, and they had to automatically add up. I just didn’t get it. How did that happen? Was that some kind of witchcraft?

I ended up just barely passing that course, leaving University, and tried starting again … by going to BCIT. People said it’s the “institute that gets you a job”. And yeah, I wanted one of those ..

BCIT rejected me, but I still went anyway .. and there I met Elsie …

I wanted to go to BCIT to do a computer programming course, which is ironic because I really was scared of computers (yes, scared. At that time I was a new immigrant, and the high school and University I attended prior to immigrating barely had anything computer-related). BCIT turned me away though because I did not have any programming background, that apparently Canadian high school students should have.

Rejected and dejected but still not giving up, I tried to go to another local college to do my computer programming pre-requisites. I went to CompuCollege (now called CDI College) to do computer programming. I was lucky to have an instructor who was so passionate about programming, that I ended up enjoying working with computers and programming. I graduated from CompuCollege with honours, and went back to BCIT. Yes I still wanted to have that BCIT Diploma.

Despite having a computer programming diploma in my belt, BCIT still proved to be very challenging. I spent two (2) years of sleepless nights finishing up tons of assignments, crazy number of projects, and just piles and piles of quizzes and exams. But it paid off in the end. I finished Client/Server Option with honours.

After graduating from the Diploma program, I had the urge to pursue the Bachelor’s Degree, and I did. It proved to be one of the best decisions I ever made. I started doing TA work for the instructors, got introduced to the Operations Manager for School of Computing PTS (which started my teaching career at BCIT – 8 years and going!), met and worked with the Dean and Associate Dean at that time, and also got to know Elsie.

Elsie was the program head for the Database Option for the BTech, which I was part of. I admired her so much and her knowledge and passion about databases really got to me. She had us working with a whole slew of DBMSs (Oracle, SQL Server, MySQL, Object Relational databases etc). I think that’s how I started really becoming curious about databases. I did well in my courses, and got a job as IT Manager even before I finished my degree.

I used to be an open source gal …

I started as an all around IT person for a small IT company which use mostly Open Source products – doing networking with Linux/PCs, developing applications using VB/C/C++/J2ME, develop tons of web applications using HTML/CSS/JavaScript/PHP and Perl (I even tried Zope and Python!), and manage the company MySQL database. I learned so much from this experience, but I have to tell you that as good as MySQL is at that time, there still were so many limitations that I found quite frustrating. At that time, the version we used did not support subqueries and stored procedures – which made my scripts a lot more complicated than it needed to be.

I also had to create a lot of reports (yup, similar to what you see in SSRS). Have you tried creating reports with Perl, bare GDI and other open source packages? I have. And it was painful.

Ever tried doing the same exact thing using reporting services? I have. And it was the complete opposite of painful. That’s when I decided that I really would love to work more with SQL Server.

When it was time for me to look for another job, I actively pursued jobs that used SQL Server, and other Microsoft products. I got hired as a SharePoint and SQL Server consultant, and my affair with SQL Server continued to flourish. I pursued SQL Server consulting gigs (report writing, script writing, tuning queries, administration), and went on to become a DBA for a big publishing/advertising firm (24X7X365) and part time DBA for an online ticketing system, and then as a consultant again for Black Ninja Software, where I head any SQL Server-related projects, training and contracts.

All this time I also remained active in teaching at BCIT, and as much as I can, I’ve tried help my students out in with their SQL Server related challenges in their day jobs. I still get emails from students I’ve had five or six years ago asking for advise on some things.

I also became more active with the community, having volunteered to speak at VANPASS, VANTUG, Victoria Users Group and DevTeach. Started blogging more, tweeting more, and just getting to know more really smart and helpful SQL Server folks out there .. and now also helping out Scott Stauffer (@sqlsocialite) in running VANPASSBI.

Now it’s your turn…

That’s my story, now I would love to hear your stories, so Im tagging :

Jorge Sergarra (@sqlchicken), Mladen Prajdic(@MladenPrajdic) and Josef Richberg (@sqlrunner)

here’s an old bio I have from BCIT

This is my SQLPASS :

Sessions

Day 0 – Pre-Conference

• Building a Microsoft Data Warehousing Platform by Brian Knight – excellent introduction to data cleansing, warehousing, and even data mining!

Day 1

• Keynote
• Spotlight: SSRS in the Enterprise – How to Survive with a Smile (Wayne Snyder)
• To Partition or Not To Partition – The Questions … and the answers (Don Vilen)
• SQL Server Execution Plans from Compilation to Caching to Reuse (Maciej Pilecki)
• Designing and Building Private SQL Server Clouds

Day 2

• Quest Software Breakfast Presentation about DMVs (Kevin Kline – @kekline, Brent Ozar – @brento, Tim Ford @sqlagentman, Buck Woody @buckwoody, Louis Davidson @drsql) – Wish there was another Quest breakfast for tomorrow
• Keynote
• Applied SSIS Design Patterns (Andy Leonard @AndyLeonard)
• DRP101: Learn the Difference Between Your Log and Your Cluster (Brent Ozar @brento)
• Women in Technology Luncheon sponsored by CA
• Adding SSRS Report Bells and Whistles (Jessica Moss @jessicamoss)
• Security and Deployment with Reporting Services 2008 R2 (Lukasz Pawlowski)

Day 3

• Keynote
• and highlight of the week: got invited to the bloggers table! Thanks Grant (Grant Fritchey – @GFritchey) and Brent (Brent Ozar – @brento)
• said hi to Peter de Betta (@debettap)
• Advanced Techniques for SSIS Package Development (David Noor)
• Super Reports:Patterns and Recipes (Paul Turley) — and at the same time watching hilarious tweets from the @buckwoody comedy hour .. err session
• SQLCAT: SharePoint on SQL Server – Implementation, Configuration and Tuning (Burzin Patel) — and still watching back and forth hilarious tweets from the @buckwoody comedy hour .. err session
• Storage for the DBA (Denny Cherry @mrdenny)

Last Day – Post-Conference

• with Itzik Ben-Gan on Query Tuning and Indexing for SQL Server 2005 and 2008 (Grant did warn that “bleeding in the ears” is normal, and I have to say, he wasn’t kidding!)

There are many sessions I would have wanted to attend, but they all happened at the same time. Which justifies why Im getting the DVD And – all of the presentations have been great!

Inspiration

Additional highlights of my first SQLPASS:

• I mentioned it already, but again worth mentioning – highlight of the week: got invited to the bloggers table! Thanks Grant (Grant Fritchey – @GFritchey) and Brent (Brent Ozar – @brento)
• Kevin Kline (@kekline) remembering my name! (his first recollection was “ninja.. you’re with the ninjas…”
• Quest Software Breakfast Presentation was great
• Women in Technology Luncheon was (is) very inspiring

SQLPASS Twitter Connection

And, even if this is the first SQLPASS I attended, I think it has been one of the coolest so far (based on remarks I heard). Bloggers and “tweeps” have made their mark in this conference. And some of “tweeps” I have met so far… (the pleasure and privilege is mine!) ~ I’m @sqlbelle

• @brento
• @kekline
• @GFritchey
• @brianknight
• @AndyLeonard
• @wendy_dance
• @sqlfool
• @pinaldave
• @joewebb
• @bknight
• @peshkaj
• @stuarta
• @lotsahelp
• @sqldba
• @paschott
• @mike_walsh
• @DamonRipper
• @SQLDBA_Dude
• @SQLSocialite
• @GlennAlanBerry
• @llangit
• @SQLServerMag

It is very inspiring to meet a lot of these people. If you meet them, you’ll know right away how passionate they are about their craft, about learning, about their dedication to share their knowledge and help others. It is very humbling.

Swags

And .. thanks to the vendors for the swags

• Quest – for the free signed Database Benchmarking book (thanks Kevin Kline!) – plus the twitter shirt!
• Red Gate for the free books on Exceptional DBA and DBA Tacklebox
• Idera – I got to spin twice .. got a $1 and a cup holder. But it was good to see our buddy David Terry!

Lessons Learned

Aaron Bertrand mentioned in his post Another PASS has come and gone – “If you didn’t make it to PASS this year, I feel bad for you.” ~ in some ways I do too.

We have our own reasons for not being able to attend conferences such as SQLPASS, but after attending my first PASS, I realize now how much I have been missing. I feel I’ve gotten SO MUCH out of this year’s SQLPASS conference (networking, learning, just being able to feel “at home” in the company of people who love the same technologies and work you love), and I will try every way I can to ensure I attend again next year – SQLPASS2010.

One (1) day Post-PASS

• missing the #sqlpass tweets
• volunteered for VANPASS
• Started reading Lynn Langit’s Smart Business Intelligence Solutions and SQL Server MVP Deep Dives
• Itzik Ben-Gan, I just ordered your books! That was how awesome I thought the sessions were

PS – I will be sharing some tips/tricks/enlightenments I’ve learned in the next few blogs

I just finished the SQLPASS pre-conference from Brian Knight – Building a Microsoft Data Warehousing Platform, and I’m so pumped to do more BI! Reporting, Data Warehousing, Data Mining – all fun stuff.

In a gist, this is what we learned today (and it’s a lot of acronyms):
- SSIS, ETL
- SSAS
- Cubes, Data Warehouse
- Data Mining
- MDX and DMX (on a very very high level)

It was a lot of details and demos jammed in one day, but I won’t have it any other way, and overall the session was just great! Loved it!

Here are some bits and pieces of information / tips / useful sites / resources (probably mostly for my benefit, so I remember. But if you find it useful, then all the better):

• 65% of data warehousing projects fail, and one of the reasons for this is lack of communication. Business users want something, but devs deliver something else.
• Waterfall model for DW projects – hard to react. Agile is better – and Brian recommends 1 month iterations (ie complete deliverable cycle per month)
• Why data warehouse? Consolidate data, aggregates, reporting, analytics, archived data (ex if you need to keep data for years and years)
• Business Key = Alternate Key = PK from source
• Surrogate Keys – insulates data warehouse from source changes
• In one of Pragmatic Works’ projects, they lowered SSIS loading time from 4.5 hours to 3 mins – all about choosing “better” way. In SSIS most components are in memory and are synchronous.
  • Most SSIS components are synchronous – good for performance
  • Some are asynchronous and partially blocking, not so good – for example UNION ALL and MERGE – slight overhead
  • Some are asynchronous and fully blocking – not good at all – for example sort or aggregate transforms – but sometimes there are reasons to use these
• In BIDS, devenv.exe -NOSPLASH to prevent splash screen
• In SSIS – Protection Levels for Packages – EncryptSensitiveWithUserKey generally a bad idea because it associates the package with your profile; if you transfer package, might say package is corrupt. Best to Encrypt All With Password; ServerStorage relies on msdb
• Different types of dimensions
  • Type 0 – Changing Attribute – updates; no history kept
  • Type 1 – Fixed Attribute – cannot change under any circumstance; ignores change
  • Type 2 – helps track changes; biggest disadvantage is additional storage; it creates a new record per update
• Good practice – smaller packages; easier to change and maintain
• Slowly Changing Dimension Wizard (SCD) – data types need to be lined up, need to be very careful; easy to break; also when you need to customize later on and relaunch the wizard, previous customizations will be dropped. You should keep documentation on how to recreate your customizations
• Another problem with SCD – similar to a cursor, goes through row by row; alternative way is to create staging table first. Use T-SQL for what it’s good at, SSIS for what it’s good at. If you can do sorts in T-SQL, do the sorts in T-SQL
• Inferred members – data that not in source, but is in Fact; for example, if it takes 2 weeks to add a salesperson in HR, but salesperson already has sales from Day 1
• Better load – “Fast Load”; also configure “Maximum Insert Commit Size”
• Every member should has an unknown member
• ALWAYS alias your columns
• Alternatives to detecting updates – instead of specifying all columns in a multi OR expression, use CHECKSUM or HASHBYTES. Problem with CHECKSUM is it is not guaranteed to be unique. Ex.

  HASHBYTES('sha', ISNULL(col1, '') + ISNULL(col2, ''))

  So in your expression column can just have something similar to: (DT_WSTR, 64)yourhashcol_src != (DT_WSTR,64)yourhashcol_dw

• SQL Cache Transform – creates cache file, can be reused multiple times; 80% faster in SQL Server 2008; SQL Server 2005 was bad with threading
• Terminologies – data warehouse (SSAS 2008)
  • Measure Group = Fact Table
  • Attributes = Columns in a Dimension
  • Member = Row in an Attribute
• Hierarchies are shortcuts for end users; you can ultimately hide actual attributes (AttributeHierarchyVisible = True), and just expose the hierarchies
• Always reconnect to cube after dimension or attribute changes so changes are apparent
• Set up attribute relationships ex:

  Date SK -- Date -- Month -- Quarter -- Year 

  Note that uniqueness of quarter is really Year + Quarter.

• Set up constraints/uniqueness through the “Key Column”. Set up what’s being displayed to user using “Name Column”. You will probably need to explore your dimensions and see which attributes need this.
• For any time dimensions, add “Time Intelligence”. As a side effect, this automatically adds additional filters like “Yesterday”, “This Week”, “Last Week” etc in Excel
• “Mushroom Cloud” – Invalid queries, but you are getting invalid results. For example if you drag Product then EmployeeCount. This gives you the EmployeeCount value for Product.
• In Cube Editor, MeasureGroup, set IgnoreUnrelatedDimension = False helps prevent “mushroom clouds”; result will display blank instead of giving you invalid value
• Discretization Method – allows you to create dynamic buckets of data
  • Automatic
  • EqualAddress – your just specify number of groups
  • Clusters – groups based on your data
• HideMemberIf (trying to remember why this option was highlighted in the session)
• MDX is language for querying the cube/SSAS; used for creating KPIs, calculations
• Tuple in MDX points to a cell; you can work with six (6) different axes. Good book is MS Press MDX Step by Step
• When you want to show data, put in SELECT; if you don’t but you want to filter by it, put in WHERE
• Parenthesis in an MDX helps you represent a tuple
• SSRS and Excel can generate some MDX
• Calculations < Calculated Member does not store anything in the cube, just the metadata; actual calculations done on the fly
  • PRO: Only formula is stored in the cube; don’t need extra storage
  • CON: Only formula is stored in the cube; performance will be worse
• Usage Based Optimization : Go to SSMS < Connect to SSAS < Set CreateQueryLogTable = true; you can also choose to “Review Aggregation Usage”
• How to lock data in your cube : Go to your dimension then go to Role, and add roles/users
• Option: Enable Visual Totals – like an implicit WHERE; Off by default, which means users will still see all data even if data is locked down; for example, if user can only see US data, his/her totals should be just for US, but s/he will see US Totals, then a different Grand Total. Enable this to lock down these totals to only the data they can see
• Deployment wizard in SSAS – creates .asdatabase file that contains metadata required to deploy the cube
• You dont need to always process the whole cube; you can do incremental processing
• Partitioning in SSAS – in about 50K records, you will start to feel the slowness
• SQL Server Standard allows 3 partitions; SQL Server Enterprise allows any number
• New feature in SQL Server 2008 Enterprise – Proactive Caching
• In SQL Server 2008, you can use profiler on SSAS to deconstruct MDX queries. In Excel, you can also right click on some columns then check MDX
• There is an option to do offline OLAP : Excel < Options < OLAP Tools < Offline OLAP. You can get to your data, but actions and drillthrough are off
• Double hop issue in SSRS and SSAS when you build reports on top of the cube : check Kerberos, SPN
• You don’t need a cube to do data mining! You can also allow your users to do predictions based on flat tables or views.
• Sample data mining scenario: used to auto approve insurance claims; can be used for fraud detection; can be integrated with SSIS in conditional tasks
• For data mining structures, Decision Trees is the best way to start; as far as alogrithms go, you can even create your own, or purchase 3rd party algorithms
• Users can get/do data mining through Excel
• Users can do drillthroughs with data mining

Useful Resources / Sites

• pragmaticworks.com
• sqlshare.com
• ssas-info.com
• dtsxchange.com
• sqlserverdatamining.com
• Marco Russo’s Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
• Brian Knight’s 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services (Wrox Programmer to Programmer)
• MS Press Microsoft SQL Server 2008 MDX Step by Step (Step By Step (Microsoft))

Every now and then I need to resize my VMWare Server disks.

The is the easiest and least troublesome way, from my experience, is to use GParted Live (Gnome Partition Editor)

Prep Work:

1. Download GParted (Gnome Partition Editor) Live (http://sourceforge.net/projects/gparted/files/gparted-live-stable/). It is an iso file that we will mount to our vm

2. Locate where your vmware-vdiskmanager.exe is. It would typically reside in the install folder, for example:

 C:\Program Files (x86)\VMware\VMware Server\vmware-vdiskmanager.exe

3. Note the path to your virtual disk (vmdk). For example:

 vmware-vdiskmanager.exe -x 20GB "C:\Virtual Machines\SQL2005SP3\
  Windows Server 2003 Enterprise Edition\Windows Server 2003 Enterprise Edition.vmdk"

Ready to resize your VMWare disk? Let’s go:

1. Shut down your VM
2. Open the Command Prompt
3. Go to the folder where you have your vmware-vdiskmanager.exe is (or if you have added this to your PATH, then it doesn’t matter from which path you execute it from).

 cd C:\Program Files (x86)\VMware\VMware Server

4. Resize your disk. For example if you want to resize your disk from 10GB to 20GB:

 vmware-vdiskmanager.exe -x 20GB "C:\Virtual Machines\SQL2005SP3\
  Windows Server 2003 Enterprise Edition\Windows Server 2003 Enterprise Edition.vmdk"

5. Confirm that your VM disk size has been changed, before we resize your partition

1. Power up your VM
2. Go to Administrative Tools > Computer Management > Disk Management
3. Check how much space you have in your disk. You should see an extra 10GB unallocated

6. Shut down your VM again.

7. In your VMWare web access, select your VM.

8. Mount the GParted Live ISO

1. In the Hardware section, select “CD/DVD Drive”
2. Select the dropdown arrow, then click Edit
3. In the Window that appears, select Host Media, and under Connection section select ISO image. Navigate to the GParted Live ISO location, and then save your preferences.

9. Configure your VM to boot from CD/DVD

1. Configure VM on the right hand panel
2. In the popup window that appears, go to the Power tab.
3. Go to the BIOS Setup section, and check “Enter the BIOS setup screen the next time this virtual machine boots” and save your preferences.

10. Restart your VM

11. Resize your partition using GPartLive

1. Follow the screens, and select appropriate options if prompted (i usually select defaults) ex:
   language           US English
   keyboard          qwerty/us.map
   video driver    generic
   resolution        1024X768
2. When the partition editor appears, you will see a partition named /dev/sda1
3. Click on Resize/Move button above the partition list
4. The /dev/sda1 becomes a movable slider. Drag the slider (make sure it’s double arrow, not four way arrow) all the way to the end of the partition. The double arrow is a “resize” operation, while the four way arrow is a “move” operation.
5. When done, click Apply.
6. When resize operation is complete, click Close.
7. Click the “Power” button, and choose to reboot.

12. When your VM starts up, it will prompt you to do a checkdisk. Let this finish. You will be prompted to reboot (yet again). Reboot – do not resist

Last Check

Now, when your VM starts up again, check your disk to confirm additional space has been allocated:

1. a. Go to Administrative Tools > Computer Management > Disk Management
2. b. Check your Primary Partion. Check that the new size is correct, and that there is no unallocated space

That’s it, we’re done.

Are they?

I personally find them still valuable. Poured my thoughts into our company blog :
Black Ninja Software – Are Vendor Certifications Still Valuable?

How about you? What’s your take on it?

Excellent Free Ebooks from RedGate

SQL Server eBook Selection
1. Protecting SQL Server Data by John Magnabosco
2. The Art of High Performance SQL by Grant Fritchey
3. The Art of XSD by Jacob Sebastian
4. Two Minute SQL Server Stumpers Vol. 5

DBA Ebooks Bundle
1. Mastering SQL Server Profiler
2. Brad’s Sure Guide to SQL Server 2008
3. Brad McGehee’s DBA Best Practices
4. How to Become an Exceptional DBA (2nd edition)
5. SQL Server Tacklebox

The Best of Simple Talk .NET
1. The Best of Simple Talk .NET – Jit N’ Run vol.2

You can download them from this link