• Post Categories

  • Browse Blogs

  • Blog Stats

    • 483,300 hits
  • Syndications

    SQLServerPedia Contributor

Configuring BIDS 2008 / 2008 R2 and Visual Studio 2008 to work with Database & Business Intelligence Projects and Team Foundation Server (TFS)

Working with the SSIS, SSAS, and SSRS Business Intelligence projects along with Database projects in the same Visual Studio 2008 development environment can be challenging as there is no clear documentation how to enable both set of projects.  Database projects are supported by Visual Studio 2008 Developer, Professional and Team System Database Editions. The  SSIS, SSAS, and SSRS Business Intelligence projects are supported by the Visual Studio 2008 shell that comes with SQL Server 2008 / 2008 R2.

If you have BIDS 2008 / 2008 R2 installed by itlself and you try to open a database project you get an error saying that this type of project is not supported. You get the same error if you have Visual Studio 2008 installed by itself and try to open one of the Business Intelligence projects. The reason behind these errors is that BIDS 2008 / 2008 R2 does not come with the database project template and Visual Studio 2008 does not come with the SSIS, SSAS, and SSRS Business Intelligence project templates.

So how do you get both set of templates in a single development environment?

In order to get both set of templates in the same development environment, you need to install both Visual Studio 2008 and BIDS in the right order as described in the following steps:

  1. Uninstall all of these if you already have them installed in your environment:
    – Visual Studio 2008
    – BIDS 2008 / 2008 R2
    – Team Explorer 2008
  2. Install Visual Studio 2008
  3. Install Visual Studio 2008 Service Pack 1
    http://www.microsoft.com/en-us/download/details.aspx?id=10986
  4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media

At this point should have a working Visual Studio environment with both set of projects.  A quick way to verify that the installation was successful, is to take a look at the Visual Studio version in the Help|About page. It will show as Version 9.0.30729.4462 QFE as shown in the picture below. QFE stands for Quick Fix Engineering.

VS2008QFE

When you launch Visual Studio 2008 or BIDS 2008 / 2008 R2 you will be prompted to select the default environment settings. Since I work with the Business Intelligence projects most of the time, I select the Business Intelligence environment. This is an option presented only the first time you open Visual Studio. To change this setting, use the Import and Export Settings wizard, which is available on the Tools menu. For more information on choosing and changing the environment settings go to http://msdn.microsoft.com/en-us/library/6k364a7k(v=vs.90).aspx.

Connecting to TFS 2005 and 2008

To connect to TFS 2005 and 2008 you will need to download and install Team Explorer 2008 from http://www.microsoft.com/en-us/download/details.aspx?id=16338.

If your end goal is to be able to work with database projects, business intelligence projects and connect to TFS 2005 or TFS 20008 the complete steps are as follows:

  1. Uninstall all of these if you already have them installed in your environment:
    – Visual Studio 2008
    – BIDS 2008 / 2008 R2
    – Team Explorer 2008
  2. Install Visual Studio 2008
  3. Install Visual Studio 2008 Service Pack 1
    http://www.microsoft.com/en-us/download/details.aspx?id=10986
  4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media
  5.  Install Team Explorer 2008
    http://www.microsoft.com/en-us/download/details.aspx?id=16338.

Connecting to TFS 2010

In some cases, the solutions & projects are 2008 / 2008 R2 projects but the repository is TFS 2010 or TFS 2012. Even if you installed Team Explorer 2008 you will get an error when trying to connect to a TFS 2010 or TFS 2012 server. The reason for this is due to the fact that Team Explorer 2008 does not support full URL paths (i.e. https://myservername/mytfs/mycollection) in the TFS server name section.

To fix this issue you will need to download and install the Forward Compatibility Update Team Explorer 2008 SP1 for Team Foundation Server 2010 (http://www.microsoft.com/en-us/download/details.aspx?id=10834).

Notice that this update can only be applied to Team Explorer 2008 SP1 (Service Pack 1). The curve ball here is that there is no Team Explorer 2008 SP1 available  as a download. In order to turn Team Explorer 2008 into Team Explorer 2008 SP1 is to apply the Visual Studio 2008 SP1 to it. This means that if you followed steps 1 to 4 above, you will need to repeat Step 3 (Re-install Visual Studio 2008 SP1).

If your end goal is to be able to work with database projects, business intelligence projects and connect to TFS 2010 the complete steps are as follows:

  1. Uninstall all of these if you already have them installed in your environment:
    – Visual Studio 2008
    – BIDS 2008 / 2008 R2
    – Team Explorer 2008
  2. Install Visual Studio 2008
  3. Install Visual Studio 2008 Service Pack 1
    http://www.microsoft.com/en-us/download/details.aspx?id=10986
  4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media
  5. Install Team Explorer 2008
    http://www.microsoft.com/en-us/download/details.aspx?id=16338
  6. Re-install Visual Studio 2008 SP1
  7. Install the forward compatibility update for Team Explorer 2008 SP1 for Team Foundation Server 2010
    http://www.microsoft.com/en-us/download/details.aspx?id=10834

Connecting to TFS 2012

To connect to TFS 2012 the following update is required :

Visual Studio 2008 SP1 Compatibility GDR for Visual Studio 2012 Team Foundation Server and Team Foundation Service Preview (http://www.microsoft.com/en-us/download/details.aspx?id=29983).

If your end goal is to be able to work with database projects, business intelligence projects and connect to TFS 2012 the complete steps are as follows:

  1. Uninstall all of these if you already have them installed in your environment:
    – Visual Studio 2008
    – BIDS 2008 / 2008 R2
    – Team Explorer 2008
  2. Install Visual Studio 2008
  3. Install Visual Studio 2008 Service Pack 1
    http://www.microsoft.com/en-us/download/details.aspx?id=10986
  4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media
  5. Install Team Explorer 2008
    http://www.microsoft.com/en-us/download/details.aspx?id=16338
  6. Re-install Visual Studio 2008 SP1
  7. Install Visual Studio 2008 SP1 Compatibility GDR for Visual Studio 2012 Team Foundation Server and Team Foundation Service Preview
    (http://www.microsoft.com/en-us/download/details.aspx?id=29983

Hopefully this post helps you get your environment all squared away.

Connecting to the Hortonworks Sandbox v1.3 through IP 192.168.56.101

sandbox_logo

I recently downloaded the Hortonworks Sandbox v1.3 Hyper-V VM and had a hard time connecting to the included web interface that comes with it. I downloaded and followed the instructions to configure the VM in my Hyper-V server. As of this writing, the instructions point you to take note of the IP address that shows up in the console screen. The default IP address is 192.168.56.101 as shown in the next figure.

consolescreen

As part of the instructions you are directed to create a dedicated Virtual Switch in Hyper-V that is then given shared internet connectivity with your physical network adapter (wireless or Ethernet). The issue is that the vSandox virtual switch gets assigned an IP address from a random subnet  in the 192.168.xxx.xxx range that is not in the same subnet as the Hortonworks Sandbox default IP address (192.168.56.101).

In my case, my virtual switch vSandbox got an IP address of 192.168.137.100. The solution is to change the vSandbox’s IP address to an IP address within the same subnet range as Hortonworks Sandbox default IP address, for example: 192.168.56.100 with the default Subnet Mask 255.255.255.0 and no Default Gateway or DNS as shown in the next figure.

vSandbox

Once you apply the new IP address for vSandbox virtual switch, you should be able to reach the Hortonworks Sandbox web interface by typing http://192.168.56.101 on your browser as show in the next figure.

Sandboxweb

Happy Hadooping!

Windows 8 Activation Error: DNS name does not exist Error Code: 0x8007232B

The Windows 8 Activation may fail while using the online activation process. You may receive an error screen similar to this:

Image

This issue was documented in this Microsoft Support article http://support.microsoft.com/kb/929826 for previous versions of Windows but also applies to Windows 8. The cause according to this article is:

As the support article explains, the issue has to do with the default behavior of the activation wizard in certain installation media. Depending on where the installation media was obtained from (for example MSDN or TechNet subscription download) the activation wizard  by default will look for a Key Management Service (KMS) host on your internal network. It will not try to activate using the internet to reach Microsoft Activation servers.

In my case, I downloaded the Windows 8 Pro ISO file from my MSDN subscription. The installation binaries from the MSDN ISO file uses a temporary / default key to install as I was not prompted for an installation key. This installation key is apparently a temporary key that tells the Windows 8 activation wizard to look for a KMS host on the local network only and not try Microsoft’s activation servers online.

The solution is to manually change the product key to a Multiple Activation Key (MAK) most likely provided with your MSDN or TechNet subscription. To change the product key, use the slmgr.vbs script included with your Windows installation. The steps to manually change the product key are as follows:

1. Open an elevated Command Prompt (CMD) in Windows 8. Elevated means, Command Prompt needs to be run as an administrator. To do this, go to Windows 8 Start (click on the Windows key), locate the Command Prompt shortcut on your tiles (or search for it on your Apps) and righ-click on it.

2. The option to Run as Administrator appears on the bottom toolbar as shown in the following screenshot:

3. Once the elevated Command Prompt opens type the following command and press ENTER:slmgr.vbs /ipk “Your 5×5 Product Key”

For example:

4. After you press ENTER you will receive the following confirmation message within 10 seconds:

5. Once you receive this confirmation message you may proceed to activate Windows 8 using Microsoft’s activation servers online.

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.

Documentation
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.

Test

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.

Master Data Services Error: Resolving from a long-running stucked Staging Batch with Status “Queued to Clear” or “Not Running”

In some ocassions,  you may run into a runaway, long-running or stucked staging batch with Master Data Services in SQL Server 2008 R2. The issue happens most frequently when a fairly large batch of rows are being processed for update or deletion.

The issue has to do with Service Broker, either due to a timeout or notifications not being received or received incomplete.  You can confirm the issue when you query the queue [mdm].[microsoft/mdm/queue/stagingbatch]. You will see one or more messages in this queue. After searching through a couple of forum threads I found the solution to be fairly simple: rollback transactions an re-enable Service Broker. The steps are as follows:

  1. Set the MDS database in single user mode and rollback uncommitted transactions.
    ALTER DATABASE [MDS] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
  2. Re-enable Service Broker on the MDS database.
    ALTER DATABASE [MDS] SET  ENABLE_BROKER WITH NO_WAIT;
  3. Set the MDS database back in to “regular” multi-user mode.
    ALTER DATABASE [MDS] SET  MULTI_USER WITH ROLLBACK IMMEDIATE;

Once the MDS database is brought back online, all queued messages should be cleared out and MDS database will be able to communicate normally with Service Broker.

You can check Books on Line for more information on the difference between “NO WAIT” and “WITH ROLLBACK IMMEDIATE” options when issuing the ALTER DATABASE command here. Pinal Dave has a good post about the difference between these two options as well here.

Hope this post has helped you. Your feedback is greatly appreciated.

SQL Server 2012 Master Data Services Error: The required .svc handler mappings are not installed in IIS.

Microsoft SQL Server 2012 Release Candidate 0 (RC 0) has a confirmed issue with Master Data Services configuration. The following error is raised by the Master Data Services Configuration Manager when launched:

Before creating an MDS web application, you must correct the following errors:
Internet Information Services (IIS) is not configured on this server.
The required .svc handler mappings are not installed in IIS. For more information, see http://go.microsoft.com/fwlink/?LinkId=226284.
For web application requirements, see http://go.microsoft.com/fwlink/?LinkId=215355.
This issue has been communicated to Microsoft via Connect ID: 701993 and is documented in the Technet Article “Troubleshoot Installation and Configuration Issues (Master Data Services in SQL Server 2012)”
The Workaround
After several days trying to figure out the issue, today I received notice from Reagan Templin (LinkedIn | Twitter), technical writer for Microsoft, that the MDS Team has identified the issue. This issue will NOT be fixed in the current RCO but will be fixed in the RTM version. The error can be replicated under the following conditions:
  1. Configuring Master Data Services on a server that is not joined to a Windows domain
  2. Running Master Data Service Configuration Manager with a local account (non-domain user) on a server joined to a Windows domain.

The workaround is simply to join the server to a Windows domain and launch Master Data Services Configuration Manager with a domain account that has administrator privileges. In my case,  after I joined the server to a domain I was able to complete by MDS configuration.

Many thanks to Reagan Templin for her above and beyond customer service. She followed up on this issue directly with the MDS Team after I posted a message on Twitter about the issue I was having and kept me posted with updates towards resolving the issue.

The CASE of the missing non-NULL T-SQL Error: None of the result expressions in a CASE specification can be NULL.

Recently, while doing some data scrubbing for a customer I got an interesting error in SSMS with one of my CASE statements. The error received was:

None of the result expressions in a CASE specification can be NULL.

It was a long T-SQL script (and a long night) and could not understand the error message. After a couple minutes BinGling (Google +Bing) around the web, I still could not find the root cause, so I decided to comment out every single CASE statement and run one by one until I pinpointed the offending syntax.

 

To my surprise, the issue was very simple, yet undocumented. The T-SQL syntax I was writing was somewhat as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

END AS SerialNumber_Clean

FROM tblProduct

 

After executing this script the error mentioned above is raised. The same error is raised even if we rewrite the script as a simple CASE statement:

 

SELECT ProductID,

CASE SerialNumber

WHEN ‘0’ THEN NULL

WHEN  ‘-‘ THEN NULL

WHEN ‘Unknown’ THEN NULL

END AS SerialNumber_Clean

FROM tblProduct

 

So what if we add an ELSE statement as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

ELSE NULL

END AS SerialNumber_Clean

FROM tblProduct

 

We still get the same error. The issue as the raised error describes can be narrowed down in that you cannot explicitly return NULL for every resulting expression. There must be at least one non-explicit NUL in the resulting expression. For example, we can rewrite the script correctly as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

ELSE SerialNumber

END AS SerialNumber_Clean

FROM tblProduct

 

As you can observe, adding an ELSE statement that does not return another explicit NULL makes the script work and as a matter of fact, should be the correct syntax. Interestingly, ELSE is not necessary to make the script valid. The script will run even without an ELSE expression, but only if at least one of the resulting values is not an explicit NULL. The following script runs successfully (notice no ELSE):

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’  THEN NULL

WHEN SerialNumber = ‘No Serial’ THEN ‘Non-Serialized Product’

END AS SerialNumber_Clean

FROM tblProduct

 

Books on Line should include this caveat on their documentation. http://msdn.microsoft.com/en-us/library/ms181765.aspx

 

The following sections should be modified as follows:

THEN result_expression

Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid expression. If no else_result_expression is specified or if else_result_expression is set to return an explicit NULL, at least one result_expression has to be specified to return a non-explicit NULL.

 

ELSE else_result_expression

Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion and at least one of them should be specified to return a non-explicit NULL.

%d bloggers like this: