An Error on Accessing and Running a Web Service Application and the Solution

Problem:

Today, I performed the example in the SSSR tutorial on accessing the SQL Server 2012 Reporting Services (SSRS) Web services from an application created with Microsoft Visual Basic or Microsoft Visual C#.

http://technet.microsoft.com/en-us/library/ms169926.aspx

On the last lesson (Lesson 4) when running the application, I received the following error message:

============================================================

System.Web.Services.Protocols.SoapException: The item '/AdventureWorks 2012 Samp

le Reports/Company Sales 2012' cannot be found. ---> Microsoft.ReportingServices

.Diagnostics.Utilities.ItemNotFoundException: The item '/AdventureWorks 2012 Sam

ple Reports/Company Sales 2012' cannot be found.

   at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.GetProperti

es(String Item, Property[] Properties, Property[]& Values)

   at Microsoft.ReportingServices.WebServer.ReportingService2010.GetProperties(S

tring ItemPath, Property[] Properties, Property[]& Values)

Press any key to continue . . .

================================================================

Solution:

1. Download the right file and install it

This tutorial uses the Company Sales report from SQL Server 2008R2 (in fact, it has been with SQL Server since version 2000). This report is part of the AdventureWorks Sample Reports 2008 R2. You can download it from here at this time.

http://msftdbprodsamples.codeplex.com/releases/view/89245

There are no AdventureWorks Sample Reports 2012 at this point.

Download the file, unzip it, and install it anywhere you choose. It does not matter if it is in the recommended default sample file path. I zipped the files to C:\downloads folder.

2. Open the solution file and rename the report file

(1)   When you open the solution file in SSDT, it will be upgraded.

(2)   The solution file name itself, which is currently "AdventureWorks Sample Reports SQL2008R2" does not matter. You can still keep the name.

(3)   Rename the Company Sales report file from Company Sales SQL2008R2.rdl to Company Sales 2012.rdl

3. Change the target report folder for deployment from AdventureWorks Sample Reports to AdventureWorks 2012 Sample Reports

Right-click the solution|Propeties|Deployment|OverwriteDatabase=True, OverwriteDataSources=True, TargetReportFolder=AdventureWorks 2012 Sample Reports (the original is AdventureWorks Sample Reports)

Alternatively, you could use report manager to complete steps 2 and 3. In other words, you can rename the report folder from AdventureWorks Sample Reports to AdventureWorks 2012 Sample Reports and the report file from Company Sales SQL2008R2 to Company Sales 2012 in report manager after you deploy the AdventureWorks Sample Reports SQL2008R2 solution.

4. After the AdventureWorks Sample Reports SQL2008R2 is deployed correctly, you should see the nice message in the console window in Lesson 4 when you start the project GetPropertiesSample without debugging.

A couple of issues on data-driven subscription on a local computer with multiple SQL Server instances

1. I used a named SQL Server 2012 instance (which is called SQL2012) for the Subscriber database. The SQL Server agent for this named instance already started. But I keep getting an error message complaining the SQL Server Agent is not started. After I start the SQL Server Agent for the default instance, which is for SQL Server 2008R2, the error message is gone and the subscription is saved. Why so? because I specify http://localhost/reportserver for the TargetServerURL on the project property. If I specify http://localhost/reports_SQL2012 as the TargetServerURL, I would not need to start the SQL Server Agent for the default instance.

2. I used my laptop for the project and created a shared local folder called Tutorial at C:\Users\CK\Documents\Tutorial. After I use a correct UPC as either \\localhost\Users\CK\Documents\Tutorial, or \\MyPCName\Users\CK\Documents\Tutorial, I have four files instantly populated in the 'Tutorial' folder. However, the message under the 'Status' tab on the My Subscriptions web page in report manger is "Processing: 0 processed of 4 total; 0 errors.". It takes about 10 minutes to change to: "Done: 4 processed of 4 total; 4 errors." 

a. Why so long? 
b. Why 4 errors? 
c. Where are the errors?

I have checked the event viewer and the tables ExecutionLog, ExecutionLog2, ExecutionLog3, or ExecutionLogStorage in the reportserver database. All reported a status of rsSuccess with the correct numbers of row counts. These may be just some of the minor bugs in SSRS.

Drop Row Fields Here and Drop Column Fields Here not available anymore in Excel PowerPivot in SQL Server 2012

See this one: http://msdn.microsoft.com/en-us/library/ms143682.aspx

An alternative:

Using the new icon Excel Pivot in SSDT

You may not be able to run the Free SQL Server Virtual Labs in Google Chrome. IE works fine! Maybe Chrome will be supported later

http://www.microsoft.com/en-us/sqlserver/learning-center/virtual-labs.aspx

SSAS Deployment Error - Login failed for user 'NT Service\MSOLAP$xxx

There are four options to get access to the data sources for an SSAS project as below:

·       User a specific Windows user

·       User the service account

·       Use the credential of the current user

·       Inherit

By default, Inherit is selected. But you may encounter an error if you use this option such as Login Failed for user 'NT Service\MSQLServerOLAPService', Why? Here is the reason:

At the data source level, Inherit means that Analysis Services should use the impersonation option of the parent object. In a multidimensional model, the parent object is the Analysis Services database. Choosing the Inherit option lets you centrally manage the impersonation settings for this and other data sources that are part of the same database. For this option to be meaningful, choose a specific Windows user name and password at the database level. Otherwise, the combination of Inherit on the data source and Default on the database are equivalent to using service account option.

The error indicates that the service account for SSAS does not have permission on the data source. What are the solutions?

Solution 1 – as recommended, use a specific Windows user name and password at the database level.

As an example, I use AdventureWorksDW2012 as the database source.

Step 1 - Create a user with the password

Step 2 - In SQL Server 2012, create a login from the windows account. Choose AdventureWorksDW2012 as the default database for the login (optional).

Step 3 - In AdventureWorksDW2012, create a user associated with the login and assign it with a db_datareader role. At this point, you may see an error message complaining no user or role not associated with the login, just ignore it.

Solution 2: to give the default SSAS service account a read permission on the data source.

The trick for this option is to find the 'NT Service\MSQLServerOLAPService'.

Here is the background information:

When installing SSAS, a per-service SID for the Analysis Services service is created. A local Windows group is created, named in the format SQLServerMSASUser$computer_name$instance_name. The per-service SID NT SERVICE\MSSQLServerOLAPService is granted membership in that local Windows group, and the local Windows group is granted the appropriate permissions in the ACL.

So you will not use the 'NT Service\MSQLServerOLAPService' directly, instead, you use its group SQLServerMSASUser$computer_name$instance_name

Step 1:

SQL Server 2012 | Security | Login (right click) | New Login | Search | Object Type | Group (select it!) | OK | Advanced | Find Now.

You should be able to see the SQLServerMSASUser$computer_name$instance_name group at this point

Step 2:

Select the group

Step 3:

On user mapping, assign the Login with the db_datareader role on the data source database- AdventureWorksDW2012. You may see an error message complaining no user or role not associated with the login, just ignore it. 

Cannot find the toolbox in SSDT even from the View Menu

Solution: You need to click the magic icon at the upper right corner for the SSIS toolbox.

SSDT Debugging Fails to start

Problem:

Solution:

Re-starting SSDT does not help, but Re-starting does the trick.

'URL' is not a recognized Device Type option.

Problem:

When I try to do "SQL Server Backup and Restore to Windows Azure Blob Storage Service" as that in http://msdn.microsoft.com/en-us/library/454c8296-64e9-46ed-b141-5ebfbc8a4fe2, by running the code below:

BACKUP DATABASE AdventureWorks2012 TO URL='http://cxu.blob.core.windows.net/cxu/AdventureWorks2012.bak'
WITH CREDENTIAL = 'mycredential';
GO


I got the error message:

Msg 155, Level 15, State 1, Line 1
'URL' is not a recognized Device Type option.

Reason: 

I have SQL Server 2012 SP1 (11.0.3128.0), lower than the required SP1 CU2 (11.0.3339.0) .

Solution: 

Download CU2, install it. Then the code works.

Unable to resolve the server's DNS address

Problem:

Cannot open IE or Chrome anymore, showing the above error message.

Solution: 

Control Panel|Network and Internet|Network and Sharing Center|Change Adapter Settings|Wireless Network Connection|Properties(right click)|Internet Protocol Version 4(TCP/IPv4)|Properties|Obtain IP Address Automatically and Obtain DNS server address automatically (the DNS server address was manually assigned in my case)

In conclusion, changing to "Obtain DNS server address automatically" resolved the problem.

How to install AdventureWorks along with AdventureWorks2012 in SQL Server 2012?

Problem:

I have both AdventureWorks and AdventureWorks2008R2 available for the SQL Server 2008R2 instance. Now I want to have the AdventureWorks sample database available as well in my SQL Server 2012 instance. But when I tried to attach the  AdventureWorks sample database, the AdventureWorks_Data.mdf file in the SQL Server 2008R2 data folder is not available to be selected for SQL Server 2012.

Solution:

If you want the original AdventureWorks sample database for the SQL Server 2008R2 instance is not affected, and also available to the SQL Server 2012 instance, try the solution below:

1. copy the AdventureWorks_Data.mdf file to the data file folder for your SQL Server 2012 instance (or somewhere else). After that, the file is available to attach, or

2. Run the code below as an admin in the query window:

CREATE DATABASE AdventureWorks ON

            (FILENAME = 'D:Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks_Data.mdf')
 FOR ATTACH_REBUILD_LOG;