A Business Intelligence Blog

How to connect to Windows Internal Database(MICROSOFT##SSEE instance)

2012-03-05T08:18:00.000-05:00

You’ll see the following error when trying to connect to a SQL Server Embedded Edition instance, aka Windows Internal Database(MICROSOFT##SSEE instance) using SQL Server Management Studio if you are directly using the server name.

Cannot connect to MachineName\MICROSOFT##SSEE.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

To connect to the Windows Internal Database using Management Studio, specify the server name as \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

Hope this helps!

How to enable xp_cmdshell?

2012-02-26T17:17:00.000-05:00

The following script can be used to enable xp_cmdshell

EXECUTE SP_CONFIGURE 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

EXECUTE SP_CONFIGURE 'xp_cmdshell', '1'

RECONFIGURE WITH OVERRIDE

EXECUTE SP_CONFIGURE 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

Reporting Error. The report cannot be displayed

2012-02-26T11:16:00.000-05:00

Recently, I was upgrading one of our client’s SQL Server 2008 server to SQL Server 2008 R2 and got this error when testing Microsoft Dynamics CRM reports after the upgrade.

Reporting Error. The report cannot be displayed.

Try this action again, if the problem continues, check the Microsoft Dynamics CRM Community for solutions or contact your organizations Microsoft Dynamics CRM Administrator. Finally, you can contact Microsoft Support.

Additionally, the following event is logged on the CRM Server.

Source:MSCRMReporting

Event ID:19969

Web service request SetDataSourceCredentials to Report Server http://ServerName/ReportServer failed with SoapException. Error: An error has occurred during report processing. (rsProcessingAborted)

To resolve this, uninstall and reinstall the SSRS data connector for CRM. The SSRS data connector for CRM can be downloaded from here http://www.microsoft.com/download/en/details.aspx?DisplayLang=en&id=12005. The download includes a lot of other stuff that you don’t need. After extracting the files, open up the SSRSDataConnector and run the Microsoft.crm.setup.ssrsdataconnectoe.exe file as shown in the screen shot below.

After the installation is completed, you will need to reset IIS and restart Reporting Services service.

Microsoft Visual Studio SP1 error

2012-02-25T09:31:00.002-05:00

You might encounter this error while upgrading SQL Server or when installing SQL Server Development Tools:

The following error has occurred: Another versions of Microsoft Visual Studio 2008 has been detected on this system that must be updated to SP1. Please update all Visual Studio installations to SP1 level, by visiting Microsoft Update

The solution as suggested by the error message is to download and install Microsoft Visual Studio SP1 available here -http://www.microsoft.com/download/en/confirmation.aspx?id=10986

Unable to prepare the SSIS bulk insert for data insertion error

2012-01-26T10:17:00.005-05:00

You might encounter this error if you have an SSIS package with SQL Server Destination component in the Data Flow task.

SQL Server Destination] Error: Unable to prepare the SSIS bulk insert for data insertion.
[SSIS.Pipeline] Error: component "SQL Server Destination" failed the pre-execute phase and returned error code 0xC0202071.

This error occurs if the SQL Server Destination component is trying to access a SQL Server Database on a remote server(You can’t use SQL Server destination to access a database on a remote server). To resolve this, either replace your SQL Server Destination component with OLE DB Destination component or run the package on the same machine as your SQL Server Destination.

What if you are on the on the same machine as your SQL Server Destination and you still get this error? Well, this could be due to User Account Control (UAC). You can try one of the options below to resolve this:

Solution 1: Launch the tool you are running the package from as Administrator. For example, if you are running the package in BIDS, go to Start > All Programs > SQL Server 2005 or SQL Server 2008, right-click Business Intelligence Development Studio and select “Run as administrator” as seen below.

Second solution: Grant "Create Global Objects" user right to the account you are using. Go to Start > Administrative Tools > Local Security Policy > User Rights Assignment > Create global objects and add the user account you are using and click ok. Please note that this account should not be a member of the local Administrators group.

If you are on SQL 2008 Service Pack or later, the error would be: Unable to bulk copy data. You may need to run this package as an administrator.

Hope this helps!

SSRS parameter default values not working

2012-01-07T15:58:00.000-05:00

Having seen this issue several times on various forums, thought it would be a good idea to blog about it. In some cases, Reporting Services parameters with default values wouldn’t default and so you would have to manually select the values in the parameters. Obviously, this defeats the purpose of setting default values as you are required to manually select the values when running the report.

So what is the reason behind this issue? There could be 2 possible reasons based on my experience:

1. You have NULL or blank values in the dataset you have used to populate the parameter. There are two ways to fix this:

a. Filter the blank/NULL values using a WHERE clause(example WHERE ColumnName <> NULL AND ColumnName <> ‘’)

b. In the parameter properties, select the check boxes “Allow blank value” and “Allow null value” as shown in the figure below:

2. If the report works fine in BIDS(meaning the parameter is defaulting to the default values) but doesn't work in Report Manager after deployment, then you’ll have to either check the option “Has default” in the report properties in Report Manager as shown in the screen shot below or delete the report from Report Manager and deploy it again.

Reporting Services error: An item with the same key has already been added

2011-11-23T11:12:00.000-05:00

When creating a data set in Reporting Services or Report Builder, you might encounter the following error:
Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct

When you click on the details button, you’ll see An item with the same key has already been added.

This error occurs when the result set returned by the data set query has more than one column with the same name. Please make sure that each column is given a distinct name. You can use alias if you have columns with same name from different tables.

Reporting Services Tutorials

2011-10-30T08:07:00.001-04:00

Articles:

Reporting Services Tutorials(SSRS 2005) – http://msdn.microsoft.com/en-us/library/ms170246(v=sql.90).aspx

Reporting Services Tutorials(SSRS 2008 R2) - http://msdn.microsoft.com/en-us/library/bb522859.aspx

Tutorial: Designing Your First Report in SQL Server Reporting Services - http://www.sqlservercentral.com/articles/Development/designingyourfirstreportinreportingservices/1416/

Building Subreport in Reporting Services - http://shahfaisalmuhammed.blogspot.com/2011/10/building-subreport-in-reporting.html

Passing Parameters to a Drill-through Report in SSRS -

http://shahfaisalmuhammed.blogspot.com/2011/09/passing-parameters-to-drillthrough.html

Videos:

MSDN Video: Introduction to SQL Server Reporting Services 2008 (SSRS) - http://www.bing.com/videos/watch/video/msdn-video-introduction-to-sql-server-reporting-services-2008-ssrs/qmqw38cg

Virtual Labs:

TechNet Virtual Lab: Authoring Reports Using SQL Server 2008 Reporting Services - https://www.microsoft.com/resources/virtuallabs/step2-technet.aspx?LabId=fd426db1-c659-4421-aeac-fd4ef4ccdc4d&BToken=ex

MSDN Virtual Lab: Building Your First Multidimensional Report with SQL Server 2008 R2 Reporting Services - https://cmg.vlabcenter.com/default.aspx?moduleid=606eec91-3234-4448-ad86-5d0ddf278da2

MSDN Virtual Lab: Visualizing Data with Maps in SQL Server 2008 R2 Reporting Services - https://cmg.vlabcenter.com/default.aspx?moduleid=a94a0e61-d485-4031-8158-74d17d4822ee

PerformancePoint Services Tutorials

2011-10-22T09:15:00.000-04:00

PerformancePoint Services is a component of SharePoint Server that provides flexible, easy-to-use tools for building dashboards, scorecards, analytical reports, and key performance indicators (KPIs). Here are some cool tutorials(videos and virtual labs) you can use to get up to speed with PerformancePoint Services 2010:

Getting up to speed with PerformancePoint Dashboard Designer - http://office2010.microsoft.com/en-us/dashboard-designer-help/up-to-speed-with-performancepoint-dashboard-designer-HA101818158.aspx

MSDN Virtual Lab: Developing Business Intelligence Applications - https://cmg.vlabcenter.com/default.aspx?moduleid=8a534c06-2023-469c-b5d8-4451a222da89

TechNet Virtual Lab: Developing KPIs and Scorecards with SharePoint 2010 PerformancePoint Services - https://cmg.vlabcenter.com/default.aspx?moduleid=0038f9e1-dc60-4e08-987e-65bc74ee5e6c

MSDN Virtual Lab: Building Your First Dashboard with SharePoint 2010 PerformancePoint Services - https://cmg.vlabcenter.com/default.aspx?moduleid=913125f8-4552-4ee4-a7ef-c6ae2db646ba

MSDN Virtual Lab: Developing KPIs and Scorecards with SharePoint 2010 PerformancePoint Services - https://cmg.vlabcenter.com/default.aspx?moduleid=%2084100cba-f6c9-4297-8909-bb7036ddeae6

MSDN Virtual Lab: Developing Reports and Dashboards with SharePoint 2010 PerformancePoint Services - https://cmg.vlabcenter.com/default.aspx?moduleid=7fcd897a-0ab3-4f01-8d36-d0ef5fe67900

SQL Server Integration Services Resources

2011-10-02T18:39:00.002-04:00

Articles:

SQL Server Integration Services Tutorials 2005 - http://msdn.microsoft.com/en-us/library/ms167031(v=sql.90).aspx

SQL Server Integration Services Tutorials 2008 R2- http://msdn.microsoft.com/en-us/library/ms167031(v=SQL.105).aspx

Hands On Training for SQL Server Integration Services - http://www.microsoft.com/download/en/details.aspx?id=20766

Details: Contains a Microsoft Word lab Document, sample files to process, and a sample DB, all of which support a series of exercises which guide a reader/user through step by step creating SSIS packages. Lab document includes commentary and by completion of the exercises a user will have a good understanding of what can be accomplished with SSIS as well as how to create some simple SSIS packages.

SQL Server Integration Services Hands on Training - Creating Custom Components - http://www.microsoft.com/download/en/details.aspx?id=16454

Details: This manual was presented as a hands on training lab which walks users through, step-by-step, creating a sample Custom Component/Transformation for SQL Server Integration Services (SSIS).

Videos:

Integration Services Videos (SQL Server 2008) - http://msdn.microsoft.com/en-us/library/dd299421(v=sql.100).aspx

General Integration Services Videos - http://www.sqlshare.com/channel.aspx?cat=c871236d-8554-42e3-8683-4d422356c0bd

Hands on Labs:

Virtual Lab: Building Your First Extract-Transform-Load Process with SQL Server 2008 R2 Integration Service - https://cmg.vlabcenter.com/default.aspx?moduleid=aa84c347-addc-43b8-b894-54eec7ce01a4

Virtual Lab: Business Intelligence in Integration Services - https://www.microsoft.com/resources/virtuallabs/step1-msdn.aspx?LabId=ccb08636-f7e8-4d9e-aed8-403963d3a85a&BToken=ex

Analysis Services Design and Performance

2011-10-02T18:39:00.000-04:00

TechNet Webcast: 24 Hours of SQL Server 2008: AS Design and Performance Improvements (Level 200)

SQL Server Analysis Services Tutorials

2011-10-02T18:38:00.001-04:00

Articles:

SQL Server Analysis Services 2008 R2 Tutorial - http://technet.microsoft.com/en-us/library/ms170208.aspx

SQL Server Analysis Services 2005 Tutorial - http://msdn.microsoft.com/en-us/library/ms170208(v=sql.90).aspx

Managing Analysis Services Deployment - http://www.sqlservercentral.com/articles/Analysis+Services+(SSAS)/69467/

Partitions in Analysis Services -http://www.sqlservercentral.com/articles/Analysis+Services+(SSAS)/70282/

Videos:

The following website has an excellent list of SQL Server Analysis Services videos by Craig Utley - http://www.learnmicrosoftbi.com/Videos/tabid/75/Default.aspx. You can download all of them(free), just need to register.

Hands-on Labs:

MSDN Virtual Lab: Building Your First Cube with SQL Server 2008 R2 Analysis Services - https://cmg.vlabcenter.com/default.aspx?moduleid=b9b8dd3e-81c1-42fe-9434-446bedb52aff

MSDN Virtual Lab: Building Your First Queries and Calculations with SQL Server 2008 R2 Analysis Services MDX - https://cmg.vlabcenter.com/default.aspx?moduleid=9e785ad4-b926-4d77-b248-006697aec684

TechNet Virtual Lab: Managing Microsoft SQL Server 2008 Analysis Services - https://www.microsoft.com/resources/virtuallabs/step2-technet.aspx?LabId=5ef8fbe1-bf11-4c77-afbd-e47daa49abfd&BToken=ex