On the Journey to be a SQL Server Professional: 12/30/12

Installing SharePoint Foundation 2010 on Windows 7 Ultimate for Development or Testing

Oh, man, this is a hard one. I finally have a successful installation with the help from several different sources.

1. To begin, follow the steps at:

http://jeanpaulva.com/tag/sharepoint-2010-install-windows-7/

2. To config the IIS settings, see

http://lassewikmark.wordpress.com/2010/07/20/installing-sharepoint-foundation-2010-on-windows-7/

3. To fix the configuration error after installation

http://krunaljani.blogspot.com/2011/10/sharepoint-installation-on-windows-7.html

4. If complaining the web page not found, re-start the WMI service. I had the problem because I just installed the 2nd instance of 2008 R2 with the integrated mode for SSRS for the purpose of this project.

5. I got an error on complaining loopback of the server when I used: http://localhost/reportserver_R2S2 (for my 2nd instance). But when I changed it to: http://CK-PC/reportserver_R2S2, the error message is gone. CK-PC is my PC name.

6. If complaining insufficient permission for the service account on accessing to the database, run SharePoint 2010 Product Configuration Wizard one more time.

7. Now the only remaining open issue is to install and use Report Builder 3.0 when the SharePoint is opened in Chrome. This is also true for report manager opening in Chrome. So it's nothing to do with the SharePoint Foundation setup. If the SharePoint site is opened in IE, Report Builder works fine.

Errors on Opening the reporting manger (http://localhost/reports) in R2/Win7/IE9: The report server cannot decrypt the symmetric key

Problem 1:

I tried to open http://localshot/reportserver to examine the reports deployed earlier today. Strangely, my laptop shows the Intranet warning. I ignored it. Enter the logon ID/PW for the laptop. It complains the insufficient privilege. I open it in IE as an admin, the same problem! I switched to Reproting Services Configuration Manager to change the RS service account to local system. It showed an error. Click it for more details:

"The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content..."

Solution:

I clicked the Delete button for the encrypted content.

Problem 2:

Then I got the insufficient permission error logging as a local admin on my laptop.

Solution:

Turn off the UAC.

Issue 3:

When I log into http://localhost/reports, I can see site settings now. But I cannot add the local admin user (i.e., CK-PC\CK) into the content manager role in Report Manager. I only see the two system roles: Sys Admin and Sys User. I confused myself on 'Site Settings' and 'Folder Settings'. The assignment of content manager is in Folder Settings.

This post helped me

http://www.sql-server-performance.com/2011/security-ssrs-reporting-error/

Problem 4:

Windows Security Dialog Box Pops up each time when opening http://localhost/reports in IE or Authentication Required in Chrome

Trial 1: modifying the rsreportserver.config file based on the suggestions below, does not help

http://msdn.microsoft.com/en-us/library/cc281253.aspx

Trial 2: adding http://localhost to the trusted site as suggested below, does not help

http://sqlblogcasts.com/blogs/testas/archive/2010/06/03/accessing-ssrs-report-manager-on-windows-7-and-windows-2008-server.aspx

Solution:

Adding http://localhost as a trusted intranet site works for me, no more pop up asking for login credentials.

IE|Tools|Internet Options|Security|Local Intranet|Sites|Advanced|entering http://localhost|Add

Class Not Registered when Browsing a Cube in SSAS

Reason: I have removed OWC when I cleaned up the MS Office installation

Solution: download and reinstall OWC. It works again

http://www.microsoft.com/downloads/details.aspx?FamilyID=7287252c-402e-4f72-97a5-e0fd290d4b76&DisplayLang=en

How to make the SSAS server as a linked server on my laptop

Validating the project on http://www.mssqltips.com/sqlservertip/2790/dynamic-xmla-using-tsql-for-sql-server-analysis-services/

Step 0. Download the project files, unzip them, deploy the solution first. If you see deployment errors, fix them as suggested here.

Step 1. In SSMS 2012, add a linked server as below:

name: SSAS
provider - MS OLEDB Provider for Analysis Services 10
product name - SQLServerSSAS (whatever, just make sure no space)
data source - localhost\SQL2012 (not localhost/SQL2012, be careful)
server option changes: set RPC and RPC Out true (a must!)

Step 2. Verify the linked server works

2.1 - Run the following entire code in SSMS (not in SSAS)

--This is an example on dynamic XMLA using T-SQL for SQL Server Analysis Services, adding a new partition based on the year of getdate().

Declare @myXMLA nvarchar(max),

@value nvarchar(30),@date varchar(8),@year nvarchar(4)

set @date=FORMAT (getdate(), 'yyyyMMdd')

set @year=FORMAT (getdate(), 'yyyy')

select @value='Internet_Sales_'+FORMAT (getdate(), 'yyyy')

Set @myXMLA =N'<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<DatabaseID>AdventureWorksDW2012Multidimensional-EE</DatabaseID>

<CubeID>Adventure Works</CubeID>

<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>

</ParentObject>

<ID>'+@value+'</ID>

<Name>'+@value+'</Name>

<DataSourceID>Adventure Works DW</DataSourceID>

<QueryDefinition>SELECT [dbo].[FactInternetSales].[ProductKey],[dbo].[FactInternetSales].[OrderDateKey],[dbo].[FactInternetSales].[DueDateKey],[dbo].[FactInternetSales].[ShipDateKey], [dbo].[FactInternetSales].[CustomerKey], [dbo].[FactInternetSales].[PromotionKey],[dbo].[FactInternetSales].[CurrencyKey],[dbo].[FactInternetSales].[SalesTerritoryKey],[dbo].[FactInternetSales].[SalesOrderNumber],[dbo].[FactInternetSales].[SalesOrderLineNumber],[dbo].[FactInternetSales].[RevisionNumber],[dbo].[FactInternetSales].[OrderQuantity],[dbo].[FactInternetSales].[UnitPrice],[dbo].[FactInternetSales].[ExtendedAmount],[dbo].[FactInternetSales].[UnitPriceDiscountPct],[dbo].[FactInternetSales].[DiscountAmount],[dbo].[FactInternetSales].[ProductStandardCost],[dbo].[FactInternetSales].[TotalProductCost],[dbo].[FactInternetSales].[SalesAmount],[dbo].[FactInternetSales].[TaxAmt],[dbo].[FactInternetSales].[Freight],[dbo].[FactInternetSales].[CarrierTrackingNumber],[dbo].[FactInternetSales].[CustomerPONumber]

FROM [dbo].[FactInternetSales]

WHERE OrderDateKey <= '''+@date+'''</QueryDefinition>

</Source>

<StorageMode>Molap</StorageMode>

<ProcessingMode>Regular</ProcessingMode>

<AggregationStorage>MolapOnly</AggregationStorage>

<NotificationTechnique>Server</NotificationTechnique>

</Source>

</ProactiveCaching>

<AggregationDesignID>Internet Sales 1</AggregationDesignID>

</Partition>

</ObjectDefinition>

</Create>'

Exec (@myXMLA) At MSOLAP;

2.2 - Go to SSAS, verify the new partition Internet_Sales_2013 is added

How to make the ascmd utility working in SSAS 2008R2

To make ascmd working is a little bit different from other cmd utilitites. You cannot download an executable program for ascmd, neither ascmd.exe is given to you directly. You have to first build the ascmd.exe by yourself, and then use it.

Step 1: Download and install the appropriate sample project (e.g., SqlServerSamples.msi for SQL Server 2005 SP2, or AdventureWorks 2008R2 Analysis Services Project.zip for SQL Server 2008R2)

The ascmd utility is included as a part of the sample project. You need to download the proper sample project first (for 2005 SP2, 2008 and 2008 R2). I have the R2 samples installed in F:\Program Files\Microsoft SQL Server\100\Samples\Analysis Services\Administrator\ascmd

Step 2: Generate a strong name key file

Open a Microsoft Visual Studio 2008 command prompt as below:

Click Start, point to All Programs, point to Microsoft Visual Studio 2008, point to Visual Studio Tools and then click Visual Studio Command Prompt.

Use the change directory command (CD) to change the current directory of the command prompt window to the folder where the samples are installed.

Note:If the default installation location was used, the samples are located in :\Program Files\Microsoft SQL Server\100\Samples

At the command prompt, run the following command to generate the key file:sn -k SampleKey.snk

Step 3 - Build the ascmd.exe utility

After generating the strong name key file, build the sample using the following instructions.

In Visual Studio, From the File | Open menu, click Project and open the solution file ascmd.sln.
From the Build menu, click Build ASCMD. The ascmd.exe file is created now!

Step 4 - to test the ascmd.exe utility (note the . between -S and -i)

F:\Program Files\Microsoft SQL Server\100\Samples\Analysis Services\Administrator\ascmd\CS\ascmd\bin\Debug\ascmd -S . -i d:\backupMD.xmla

(I have created a backupMD.xmla file as below to backup the Adventure Works DW 2008R2 database to the D drive)

D:\Analysis Services Tutorial.abf

In SQL Server 2012, this old-fashion command can be replaced by the Revoke-ASCmd commandlet in PowerShell.

How to fix a long file name problem

Problem:

Today I try to restore some MS Word files from a recent backup, but some files with long names cannot be deleted, renamed, or even opened.

Reason:

The file name length is about 256 characters in NTFS, which includes all of the path/folder/subfolder name and the file name.

Solution:

Option 1 - Rename the folder, subfolder, and/or file to make them shorter than 256.

Option 2 - Use commercial tools. I have tried some, but there are overheads.

Subscribe to: Posts ( Atom )