Amazon Prime Air - yup, this is happening....maybe

90 Days to MCSA - week 6

Well here we are in "week 6" and the time has been flying...being so busy with fully renovating a house (dedicateddiy.blogspot.com) I have neglected my 90 Days To MCSA prep, but no more!
Tonight I will be asking about certifications at the New England SQL Server Users Group meetup and see who has some advice for me, and then I think I need to just book the first test to jolt myself into action.

Checking out the dates and locations near me on Prometrics site I see there are plenty of test dates right nearby, so I have no excuse there. Has anyone else stalled out, or are you all charging ahead ? How is it going ? A walk across the MIT campus is always inspiring, I'll make sure to do that again after the meetup.

From last month:

PASS TV YouTube channel - all sessions available

Now that PASS 2013 is over, head to the PASS TV YouTube channel to watch the sessions, I have embedded my favorite so far, parallel query plans with Paul White (@SQL_Kiwi)

90 Days to MCSA - Week 2 - New functions for 2012

In studying for the Querying Microsoft SQL Server 2012 (70-461) exam I am trying out a couple new items, below are a few that are mentioned in a few different training programs, probably good to know these.

-- NEW FOR 2012
-- IIF(<boolean expr>, <value if true>, <value if false>)
   SELECT IIF(1 = 1, 'True Value', 'False Value')
-- TRY_CONVERT(<expr>, <datatype>, <style>)
   SELECT TRY_CONVERT(DATETIME, '1/1/12330', 101)
-- TRY_PARSE(<string val> AS <datatype> USING <culture>)
   SELECT TRY_PARSE('String' AS INT USING 'cy')
-- OFFSET...FETCH 
   SELECT name
       FROM sys.objects
       OFFSET 5 ROWS
       FETCH NEXT 5 ROWS ONLY

IIF works just like the instant IF in Excel
TRY_CONVERT and TRY_PARSE both perform the same function as CONVERT and PARSE respectively, but these new version will return a NULL instead of raising an error if the conversion or parse fails
OFFSET and FETCH are good for pagination, in the example above it would return rows 6-10
Happy studying!

90 Days to MCSA - Week 2 YouTube video training playlist

I have been watching some video training on YouTube and found these videos to be pretty well made and contain some good review points for exam 70-461. It can be pretty basic in a lot of places but it's always good to brush up before the test. Check it out:

SQL PASS 2013 Live Blogs

For everyone living vicariously through the live blogs and PASSTV, here is a quick round up of the bloggers:

SQL PASS Live Stream

Aaron Bertrand on SQLBlog.com
Ryan Adams Blog 
The Midnight DBA 
Jen Stirrup
Grant Fritchey

90 Days to MCSA - Week 2

Week 2 - Build your Knowledge

I'm starting off week 2 of my "90 Days to MCSA" with some free training from Microsoft Virtual Academy on Querying SQL Server. I will probably skip over some of the basics, but this seems like a good starting point to get some learning under my belt and get some momentum.

I also checked out the Prometric site and found that all the MCSA exams are offered near me on a regular basis so no need to book way in advance.

If you're doing this as well and have some good training resources please let me and the group at the 90 Days to MCSA G+ community know.

EDIT - adding more training and embedded exam prep video

• born to learn training and wiki link - BornToLearn.com 70-461 prep
• MS exam prep on Amazon:   
Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Week 1 of 90 days to MCSA - Setting up your lab

Week 1 of 90 days to MCSA - Setting up your lab

I chose this option since I already had an instance of SQL Server 2012 running but I needed to install Adventure Works (below instrucitons from MS site):

Install SQL Server 2012 on your current workstation

If you have a workstation (or laptop / server) that meets the hardware and software requirements, you can install SQL Server 2012.

Basic steps to get started:

• ·        First of all – if you don’t already have some kind of SQL Server 2012 installation media - download one of            the SQL Server 2012 Evaluation packages. For both the executable- and ISO-files there are 32- and 64-bits            downloads. You can find the download-page here.
• ·        Install SQL Server 2012 including the SQL Server Management Studio
• ·        Download and - Using the SQL Server Samples Readme - install the Adventure Works Database
• ·        You’re good to go!

Since MS did such a good job with their instructions I wont go over what I did, the links above are all you need.

I also completed the other 'week 1' task and signed up for the newsletter suggested in the original timeline:

Starting "90 Days to MCSA" yourself?  Begin Here

90 Days to MCSA - ready, set GO!

I just came across a great resource for those seeking certification in SQL Server (like me) or Windows Server 2012, it's called 90 Days to MCSA and I found it on Gareth Swanepoel's blog (@GarethSwan) and it's just the motivation I needed.

I am going over the plans now, this puts a great structure around a task that I think we have all said "Yeah I should do that...someday..." about, but haven't done anything active to get things rolling. Seeing all these other people getting started on this is motivating to me, it's like we're doing it as a group. If you do this as well, make sure to get into the G+ group too.

So what is the plan over the next 90 days? Here is what Gareth laid out, all credit goes to him:

90 days to MCSA – SQL Server 2012

Week 1 (this week):
     Build your Plan. Following this blog post series and visiting the Microsoft 90 days to MCSA web site will help you to do that.
     Build your Lab. Here is a great article from the fine folks at Microsoft learning on how to set up a lab to prep for exam 70-461: Lab Setup Guide to Prep for Exam 70-461 Querying SQL Server 2012. 
     Sign up for the Microsoft Learning 90 Days to MCSA e-newsletter.This can be found on this page.

Week 2 (next week):
     Build your Knowledge. Do some free training at Microsoft Virtual Academy Querying SQL Server
     Sign up for the first exam. Microsoft is offering free second shot retake vouchers for a limited time. Go and grab 15% or more savings on certification packs here. 
     Share with us in the Google+ community. Let us know how you’re doing, if you have any questions or if you just generally need to talk. We’re here for you.

Week 3:
     Build your Knowledge. Watch the Microsoft Learning Exam Prep video for the first exam that will be attempted – 70-461
     Join the Microsoft Learning Study Group. There are some great resources available in these Study Groups including discussion forums and exam prep wikis. Go and join the Database Certification Study Group.

Week 4:
     Build your Knowledge. You should be consuming the content of an exam training kit like the one offered by Microsoft Press which can be found, amongst other places, on Amazon: Training Kit (Exam 70-461): Querying Microsoft® SQL Server 2012. Also available and recommended is the Study notes and review questions book: MCSA SQL Server 2012 Exam 70-461 & 462 ExamFOCUS Study Notes & Review Questions. 
     Take the 70-461 exam. By this point, you should be in a position to exert your effort into actually putting knowledge to paper. Well, digital bits of paper. You will go and attempt to pass the exam. Let us know how it goes !

Week 5 and beyond:
     The process is more-or-less repeated with the 70-462 and 70-463 certification exams. I will be updating this post with specific links that cover those exams. Keep an eye out for that.

SharePoint List Adapter in SSIS using GUID instead of name

So you have an SSIS package that pulls data from a SharePoint List, and it's working fine (It's not? start here), until...someone comes along and changes the name of the list, breaking your package and possibly failing the agent job that calls it...what to do?

You could just paste in the new name, but to fix this for good, you can use the GUID of the list to make sure that it does not happen again because the GUID remains constant even if the name changes.

In the properties of your SharePoint List source, in the name field, replace the text with the GUID of the list in question as shown below.

For anyone who doesn't have this installed yet, here is the link to the list adapter on codeplex