• Post Categories

  • Browse Blogs

  • Blog Stats

    • 626,196 hits
  • Syndications

    SQLServerPedia Contributor

IT Pro Camp Tampa 2013


This year I was honored to sponsor and speak at IT Pro Camp Tampa 2013. Like always, it is a great pleasure to give back to the community and get to know people from different technical backgrounds. The event was very well-organized and the venue was great.

This year the entire Agile Bay team spoke at IT Pro Camp delivering 4 sessions in total for Track 2: Business Intelligence & Big Data (SQL Server, Hadoop, etc.). You can download the PowerPoint presentations by clicking on the session links below:

  1. Wes Springob | Introduction To Reporting Services
  2. Jose Chinchilla & Jon Bloom | Introduction to Big Data
  3. Jonathan Bloom | Introduction to T-SQL
  4. Jose Chinchilla |  Introduction to Microsoft BI

Wes Springob deliverin Introduction to SSRS


IT Pro Camp Tampa 2013 Attendees


SQL Server Upgrade error: It is not possible to change the SQL Server features to be upgraded in this release. Validation errors. There are no features selected for upgrade.

On a recent project I was asked to “upgrade” a licensed SQL Server 2008R2 Standard Edition to SQL Server 2008R2 Enterprise Edition. I have done this multiple times with no issues. The “kicker” was that the upgrade required a 48-hour turnaround time but no installation media for SQL Server Enterprise was going to be available in that same timeframe due to a delay in the procurement process.

In order to meet project requirements and gain some time, a team member suggested we go ahead and do the “upgrade” using a downloaded copy of SQL Server 2008R2 Evaluation Edition since Evaluation Edition contains all the Enterprise features that were required. Once the installation media for SQL Server 2008R2 Enterprise arrived, we were to simply upgrade the Evaluation Edition to Enterprise Edition.

My immediate response was that going from a licensed edition to an evaluation edition was not possible. It is not a “natural” upgrade path and is actually considered a “downgrade” as you are going from a licensed edition to an unlicensed edition. As a professional I had to back this statement up with official Microsoft documentation. As a geek no matter what the documentation said I had to try it myself to see what happened. So I did both.

A quick search of Books on Line on SQL Server 2008R2 led me to the following MSDN article titled “Version and Edition Upgrades” (http://msdn.microsoft.com/en-us/library/ms143393(v=sql.105).aspx) . This article is great as it outlines all the supported upgrade scenarios for all versions and editions of SQL Server. A quick scroll towards the bottom shows that the only two upgrade paths supported for Standard Edition is Enterprise and Datacenter. As a matter of fact this article shows that there is no support going from any other edition to Evaluation Edition.

Additionally, there are a couple of footnotes at the bottom. An important footnote to take a look at is footnote No. 2. Although the statements in footnote No. 2 are within the context of failover clusters, some of them also apply to stand-alone installations. In this footnote it is clearly stated that SQL Server 2008R2 Standard to SQL Server 2008R2 Evaluation upgrade is not supported.


I wanted to see for myself the error message generated if I tried to carry out the upgrade anyways. Of course I did not try to do this on the actual client’s environment. That is a big no no! I decided to spin up my own sandbox virtual environment. The steps I followed on my test were:

  1. Install SQL Server 2008R2 Standard Edition (Database Services, Analysis Services, SSMS, BIDS)
  2. Reboot
  3. Upgrade SQL Server 2008R2 Standard Edition to SQL Server 2008R2 Evaluation Edition using the Upgrade Setup Wizard from Installation Center.

As I went through the Upgrade Setup Wizard I got the following validation error as expected:

In summary, you cannot upgrade from a licensed SQL Server edition to SQL Server Evaluation Edition. It is perfectly possible though, to upgrade from SQL Server Evaluation Edition to other licensed SQL Server editions. For more details on the supported upgrade paths refer to MDSN article: http://msdn.microsoft.com/en-us/library/ms143393(v=sql.105).aspx.

Dynamically generate current Year, Month or Date member with MDX

MDX can be extended with Visual Basic functions like the FORMAT() and NOW() functions to dynamically generate the current year, month or date member. For example, it can be used as part of a named set to generate current year budget and actuals. It can also be used as part of a filter in a Performance Point dashboard.

First, a little review of VB NOW() and FORMAT() function:


The NOW() VB function returns the current system date and time. It is similar to the T-SQL GETDATE() function.


The FORMAT() VB function returns a string formatted according to instructions contained in a format String expression.
Source: http://msdn.microsoft.com/en-us/library/59bz1f0h(v=vs.90).aspx

There is an extensive list of characters you can use to create your own date and time formats. You can see the complete list here: http://msdn.microsoft.com/en-us/library/73ctwf33(v=vs.90).aspx

For example, you can extract the year portion of your date in the following formats:

y: Displays the year number (0-9) without leading zeros. Use %y if this is the only character in your user-defined numeric format.
Example: FORMAT(NOW(), “y”)
Result: 1 (for any date in 2011)

yy: Displays the year in two-digit numeric format with a leading zero, if applicable.
Example: FORMAT(NOW(), “yy”)
Result: 11 (for any date in 2011)

yyy or yyyy: Displays the year in four-digit numeric format.
Example: FORMAT(NOW(), “yyy”) or FORMAT(NOW(),”yyyy”)
Result: 2011 (for any date in 2011)

FORMAT() and NOW() with MDX

The following MDX example uses the Adventure Works 2008R2 Analysis Services OLAP cube. Suppose we need to return all Internet Sales for the year 2011. The MDX script would look something like this:

SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2011]

This works just fine and will return the aggregated sales of 2011. (Note: the Adventure Works 2008R2 does not contain sample sales data for 2011, result will be an Empty dataset).

But what if instead of a specific year, you are asked to return the Internet Sales amount for the current year? You have 3 options:

  1. On January 1st at 12:01 AM every year you need to change your MDX script to the new current year.
    CONS: You might be passed out from celebrating New Year’s Eve and most probably you will not get to it until a few days out into the new current year.
  2. You could use some MDX functions like LasNonEmpty and LastChild to figure out the last amounts recorded by the most recent date.
    CONS: You may have forecast data or simply bad data with transactions occurring on dates out into the future. (Trust me, I’ve seen it)
  3. You can build a dynamic MDX script that will return the current year based on the system datetime.
    CONS: It may be too sexy for your cube.

The trick is to construct the Date dimension member using the STRTOMEMBER MDX function. The STRTOMEMBER MDX function stands for “String to Member” and as the name implies, it will convert a string to a dimension member. In this case, we want to generate the [Date].[Calendar Year].&[2011] dimension member from a string using the Year portion of the system datetime.

The dynamic string will look like this:

StrToMember(“[Date].[Calendar Year].&[“+FORMAT(NOW(), “yyyy”)+”]”)

Now we can use it in our MDX script:

SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE StrToMember(“[Date].[Calendar Year].&[“+Format(now(), “yyyy”)+”]”)

The same applies if you want to select or filter data for the current month. In the following example I’m be using the Month level from the Calendar Hierarchy and specifying the month of July (7) for the year 2011:

SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Month].&[2011]&[7]

We can generate the current month member as follows:

SELECT [Measures].[Internet Sales Amount] ON COLUMNS FROM [Adventure Works] WHERE StrToMember(“[Date].[Calendar].[Month].&[“+Format(now(), “yyyy”)+”]&[“+Format(now(), “M”)+”]”)

Note that to specify the Month portion we use CAPITAL letter M because it is case-sensitive. Smallcase letter m stands for minute. The complete list of characters can be viewed here: http://msdn.microsoft.com/en-us/library/73ctwf33(v=vs.90).aspx

SSIS errors: Bulk Load failed. Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)

When working with the Bulk Insert Task in SSIS 2008 you may get the following error:

 The complete error message is:

[Bulk Insert Task] Error: An error occurred with the following error message: “Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)”.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.”

This error description can make you jump through hoops trying to figure out why is it detecting an error with the OLE DB provider or why does SSIS thinks you are trying to execute this operation on a linked server?

The real issue here has nothing to do with the first  sentence in the error description. Sentence 4 gives you the actual error:

Verify that the field terminator and row terminator are specified correctly.

You may be experiencing this error due to one or more of the following 3 reasons:

1) You may be specifying a wrong CommonDelimiter for your source file.
For example, You may be trying to do a Bulk Insert operation from a Comma Separated Value (CSV) file but did not change the CommonDelimiter property to Comma {,}. When you drag in the Bulk Insert Task the CommonDelimiter property default value is Tab.

2) You may be specifying a wrong RowDelimiter for your source file.
For example, you may be trying to do a Bulk Insert operation from a Comma Separated Value (CSV) file whose row delimiter character is different than the RowDelimiter property default value of {CR}{LF}. In some cases, you may receive a file with a very long stream of text with no Carriage Return (\r)  & Line Feed (\n) characters, commonly denoted as {CR}{LF} in between rows. These hidden {CR}{LF} row delimiter characters are placed on a text file each time you hit the ENTER key on your keyboard denoting the end of a row and beginning of the next row. You may read a little more about the Carriage Return and Line Feed characters in Pinal Dave’s blog: http://blog.sqlauthority.com/2009/07/01/sql-server-difference-between-line-feed-n-and-carriage-return-r-t-sql-new-line-char/

As seen on the image below, there are two properties,  CommonDelimiter and RowDelimiter, that you need to make sure  you specify the correct values for depending on your input or source file format:

3) You may be using a format file with an incorrect or invalid format defined.
Format files can be non-xml, commonly with an *.fmt extension or for SQL Server 2005 and later only you can also use xml format files. For more information about format files read MSDN Books on Line http://msdn.microsoft.com/en-us/library/ms191516.aspx

If you are using a format file make sure you are pointing to the right format file or that the format defined in your format file is correct. (Notice that the Format property value changes from Specify to Use File):

I have been Knighted

As I announced on my 2000-2010 a Decade in Retrospect blog post I have joined the ranks at Pragmatic Works and started my first day today. I’m excited to join such a great professional team, full of experts in the SQL Server and Business Intelligence field. There is no waste of time here. Lots of engagements from the get go. Less than an hour at work, I got in my first brainstorming session. An hour later I have been assigned to two client engagements I will be involved with during the next few weeks. I will also be involved with the Virtual Mentoring services. If you have purchased Virtual Mentoring hours from us, I might be giving you a call soon! 

 The atmosphere here at the new offices is laid-back and you can feel a sense of teamwork from all of the guys. If you are looking to make a career move, Pragmatic Works is your best choice. Lots of expansion going on, great carer opportunities and lots of expertise in-house to tap into.


Business Intelligence: Decaffeinated Please!

For many Database Administrators, Data Analysts and other IT and Business Professionals, Business Intelligence (BI) and Data Warehousing (DW) may be a new and uncharted territory with no clear path towards the destination. Others, who have already jumped head-on into the Business Intelligence journey, may be facing challenges that can potentially put their efforts at the risk of failure. For this reason I put together a presentation titled Business Intelligence: Decaffeinated Please!

In this presentation I cover terms and acronyms associated with Business Intelligence and Data Warehousing. I make a differentiation of what Business Intelligence is and what it is not. I also go over my “10 Rules of Wisdom” towards BI success based on lessons learned from personal experience as well as from insight gained from leading authors and speakers in the BI universe. The purpose of my 10 Rules of  Wisdom is to serve as a guideline for anyone involved in a BI initiaive or in its planning stages.

My “10 Rules of  Wisdom” for Business Intelligence Success are listed below:

  1. The Business Intelligence solution needs strong executive management support. Keyword: Sponsor
  2. The Business Intelligence solution must add value to the organization and be trustworthy.
  3. Always have a Business Intelligence roadmap in clear view.
  4. Define iterative success criteria.
  5. Choose the right technology based on your business and user needs.
  6. The BI solution needs to be understandable and documented.
  7. The BI solution needs to be accessible.
  8. The BI solution must be able to grow according to the business needs.
  9. The BI solution needs to adapt to new business analysis needs.
  10. Partner with experts in the field to help you achieve your goals.

These and other topics can be found on my PowerPoint presentation below which I continuosly enhance for my presentations for SQL Saturdays and User Groups.

 (click on image to download)

Slides updated 3/24/2011

4 certifications in 30 days

On June 23rd, 2010 I achieved a professional milestone by becoming a Microsoft Certified IT Professional, MCITP: Database Administrator 2008 passing Exam 70-450.  Along with this certification I ended up earning a total of 4 certifications:

Exam 70-448 MCTS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
Exam 70-431 MCTS: Microsoft SQL Server 2005 – Implementation and Maintenance
Exam 70-432 MCTS: Microsoft SQL Server 2008 – Implementation and Maintenance
Exam 70-450 MCITP: Database Administrator SQL Server 2008

See my official transcript at https://mcp.microsoft.com/authenticate/validatemcp.aspx and enter Transcript ID: 913755 and Access Code: transcript2010

* Note: Exact time-frame was 43 days. I had last exam scheduled within 30 days from initial test but had to re-schedule due to last-minute high priority project meeting at work.

At first glance you may think:
1) I have photographic memory and memorized each and every page of the training material.
2) I am a SQL/BI god or rock star (Thanks @mikeSQL) </blush>
3) I am the more handsome and smarter love child between @Buckwoody and @BrentO.

The reality is that:
1) My memory span is as bad as the page memory lifespan of a poorly configured SQL Server.
2) I’m humble enough to say that I know enough to get the job done.
3) No, I’m not as half as smart as our SQL masters and rock stars, but more handsome…definetely!

So here is the 5 step process I followed preparing for my certifications:
1) Play
2) Read
3) Break
4) Fix
5) Practice Test

My 5 step process in detail:
1) Play. First, setup several SQL Server instances in a virtualized environment. Install all features and try to figure things out on your own first.
2) Read. Research and read how to do the things you could not figure out by your own.
3) Break. Once you figure things out, run several scenarios to break whatever you had running in order to learn from error messages and logs. This helps to learn what not to do in a production environment.
4) Fix. After breaking something try to fix it. If you cannot fix it on your own, read and reasearch it. Tweet #sqlhelp if necessary.
5) Practice Test. Go through the Practice Tests that come in the CD included with the Self-paced books to measure your understanding of a specific topic or objective. You can customize these tests to include only questions about specific objectives.

Takeaway #1: Do all the Practice Exams that come with the Companion CD and view the answer to the questions you got wrong to get the right answer and explanation. Re-take the Practice Exams covering only questions you answered incorrectly.

Takeaway #2: Practice, Read, Break, Fix, Self-asses…loop

As usual, you need to understand the concepts, terminology, vocabulary, and acronyms of any technology you want to master. In order to achieve this, you need a combination of experience and reading.  Reading and memorizing just don’t work. Exam crams and brain dumps don’t work and you are just cheating yourself.

You don’t have to be a book-worm. I have purchased more than 20 books on SQL Server and Business Intelligence but have not read even one of them from front to back. What I do with each exam is I lookup the exam objectives and cross out the ones I feel 100% confident (concepts and hands-on experience). The ones I did not cross out are the ones I seek good reference material on.  And where do you get the best learning and reference material from? The official self -paced training books,  Microsoft Books Online (BOL) and last but not least…your SQL MVPs’, masters’ and rockstars’ books, white papers, blogs, etc.

These are part of the books I used to prepare for all these certifications:

When I say experience, I don’t mean xxx number years of experience being a Database Administrator or Developer. By experience I mean, hands-on working knowledge,  the “Oh, yeah, I know how to do that” type of confidence.

There is a saying by Confucius that summarizes it all (modified a little by me):

” What I hear I forget. What I see I remember. What I do I understand.”

Acquiring the hands on knowledge is key to a real understanding of SQL Server and its features.

Failing to pass a certification test
If you fail it is ok. I learn a lot more by failing. I call it “Success by failure” or “Learning from failure”. Many people fail because of the fear of failure itself. Take advantage of exam discounts and vouchers like Second Shot offers from Microsoft.

Takeaway #3: Failure is an option. Success is a choice.

Again, what if I fail?  It’s ok to fail !

Failing a certification exam does not mean that you are no good. Failing an exam means that there are areas or features that you need to get more familiarized with either conceptually or hands-on or both.

Takeaway #4: If you fail an exam, keep a record (mentally or write it down on pad provided) of the features or exam objectives that came up on the exam and for which you did not feel confident on the answer. Do not write down the question(s). It is against the rules and most likely they will not come up again next time you retake the exam. After failing your test, go back home and read and play with those features that you identified you needed more understanding. Re-take the test immediately. Don’t let time pass by.

So where do you start?
In my case, I’ve been working mostly on Business Intelligence and Database Maintenance on SQL Server 2008 for the last two years at my current position. As the appointed Technical Project Manager, Architect and Developer of our Data Warehouse and Business Intelligence Solution for my current employer, I have spent endless nights doing all the planning, envisioning, configuration, development and deployment of our SSIS Packages, OLAP Cubes and SSRS Reports. It was only natural for me to go first for the MCTS: SQL Server 2008, Business Intelligence Development and Maintenance Exam 70-448.

What learning resources did I used for Exam 70-448?
1)  Self-Paced Training Kit for MCTS 70-448
2) Business Intelligence Boot Camp by TechSherpas Tampa, FL
3) The Data Warehouse Toolkit (Second Edition)
4) The MicrosoftData Warehouse Toolkit: With SQL Server2005 and the MicrosoftBusiness Intelligence Toolset
5) Microsoft SQL Server 2008 MDX Step by Step
6) Pro SQL Server 2008 Reporting Services
7) Smart Business Intelligence Solutions with Microsoft SQL Server 2008

Next on my certification path was SQL Server Administration, Maintenance, Configuration and Deployment. Since I have been working with SQL Server 2005 since its release to market I felt that I should prove myself worthy on SQL Server 2005 by passing Exam 70-431. Although, since mid 2009 I had migrated all our production databases to SQL Server 2008 and had not used SQL Server 2005 since then, I still felt, for sentimental purposes perhaps, that I wanted to have SQL Server 2005 under my certification belt.

I believe this strategy worked really well for me because it allowed me to focus on the new features of SQL Server 2008 for the MCTS: SQL Server 2008 Exam 70-432.

What learning resources did I used for Exam 70-431?
1) MCTS Self-Paced Training Kit (Exam 70-431): Microsoft SQL Server 2005 Implementation and Maintenance
2) Microsoft SQL Server 2005: Database Essentials Step by Step

Having passed the MCTS: SQL Server 2005 exam with no problems I immediately scheduled the MCTS: SQL Server 2008. Since I have been using SQL Server 2008 for more than a year in our production and testing environment, I felt confident to simply go over some of the concepts to reinforce my knowledge.

Once I passed Exam 70-432 MCTS: SQL Server 2008, I felt I wanted to go all the way and obtain my MCITP: SQL Server 2008. One area I did not felt 100% confident was the dreaded performance monitoring and tuning part of every DBA’s job. So I buckled up and did deep dives on performance monitoring and tuning based on Andy Warren’s (@sqlandy) methodology and his blogs along with the many blogs from our other SQL masters and rock stars.

After going over more than 20 books, reading hundreds of blogs, spending late night hours practicing hands-on and assessing my knowledge through practice exams, I was able to pass Exam 70-450.

 What learning resources did I used for Exam 70-432 and  Exam 70-450?

1) MCTS Self-Paced Training Kit (Exam 70-432): Microsoft SQL Server 2008-Implementation and Maintenance
2) Professional SQL Server 2008 Internals and Troubleshooting
3) SQL Server 2008 Query Performance Tuning Distilled
4) Microsoft SQL Server 2008 Internals
6) SQL Performance Tuning Class by SQLShare (Instructor: Andy Warren @sqlandy)

The bottom line:  Do not attempt to obtain all of these certifications just by reading books or doing practice exams. The hands on experience really counts. Nowadays it is so easy to build your personal training labs on your home computer or laptop with free virtualization tools like VMWare.

So what is next?
Microsoft Certified Master (MCM). The MCM will take me a year or two of preparation and most probably I will attend one of Paul Randal’s and Kimberly Tripp’s (www.sqlskills.com) Public Immersion event in 2011 to help me prepare on some advanced topics. During this period I will have completed several infrastructure upgrades to our current OLTP Production system as well as really interesting Business Intelligence at my current employer.

In the meantime I will be working towards achieving certifications and advancing my skills on Virtualization technologies (Hyper-V and VMWare), SAN Administration (EMC, HP), Windows Server, Active Directory, SharePoint  2010, PowerPivot, PowerShell, Exchange 2010, Citrix, MicroStrategy, .Net Programming, Project Management PMP, and the list goes on…

Lessons from my first week on Twitter.

I finally gave in to the peer pressure of social media and started tweeting and following fellow twiterers in the SQL/BI arena. The first set of questions that popped-up on my head when I made the decision to start my Twitter account were:

  1. What am I going to tweet about?
  2. Who will care about what I got to say?
  3. Will following too many people cause an information overload?
  4. Should I limit my tweets to SQL/BI only matters?
  5. Will I be lost with all the lingo and abbreviations as in SMS texting?
  6. Will I become addicted once again to something non-productive?
  7. If I have survived life so far without Twitter, why do I need it now?
  8. If I complain about not having enough time balancing work, family and professional development, how can I justify twittering?

Although,  haven’t been able to answer all of these questions, I decided to bite the bullet and get on Twitter. The closing case to become part of the Twitter gang was  made by Jorge Segarra @SQLChicken during a SQL Server R2 / Sharepoint 2010 presentation during the April 5th Tampa Bay SQL BI User Group meeting. Jorge’s statement about being a “legitimate business and professional tool for DBA’s” was heard by my boss as well, who I had invited to attend this particular meeting. Now I can justify my tweetering and blogging at work. Yey!

My next challenge was to justify my twittering at home. Of course, I wear the pants at home but…it is my wife who choses which ones should I wear, when and where. Her resistance was as expected and she just made me realize that I have become what I have always criticized her young sister about…a texting junkie! OK, so less twittering at home I concede. See what I talk about deciding when and where? She just knows how to keep the family harmony.

Eighty (80) tweets later within a week, tweeting  as @sqljoe , I have learned a couple of things:

  1. Twitter feels much like those old IRC chats in  which you can filter messages by sender and topic (#channels).
  2. Twitter is a great way to get in touch with respected SQL/BI experts such as seasoned DBAs, developers, authors and folks from Microsoft.
  3. Interesting to learn what other DBA’s are doing in their day to day tasks, challenges and their approach, as well as learning about the technologies they play with at their jobs.
  4. Twitterers and bloggers are an important part of technology marketing strategies to get the word out quickly about new products and features with minimum effort and cost, other than fine wine and limos (#sqlr2 Microsoft tweetup).
  5. There are SQL chefs, midnighters, chickens, sheeps, chicks, bucks,  knights, doctors, m-a-chanics, soldiers, runners and of course regular joes and green handed masters.
  6. SQL Twitterers use the word Pimpin a lot.
  7. Body disposal services can be contracted through Twitter.
  8. SQL experts have hair feuds as well.
  9. Margaritas make you enjoy progress bars way too much.
  10. DBAs do have lives and are a good source of recommendations for places to eat.

 Among other things,I think my first week on Twitter was with lack of better terms:  interestingly fun, cool and informative.

%d bloggers like this: