RSS

Monthly Archives: December 2012

Error with TFS2012,Sharepoint 2010 and remote Analysis Services on SQL2008r2

An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset ‘dsArea’. (rsErrorExecutingCommand)

Above stated error pops up when we getting reports from report server: it could be caused by many authentication or Instance service types.

If you are trying to get Team Foundation Server 2010 to run with MS SQL Server 2012 Analysis and Reporting services, forget it ūüė¶ i tried didn’t get it to work no matter what.

01 "Could not load file or assembly 'Microsoft.AnalysisServices.AnalysisServices, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies"

AnalysisServices_TFS-640x502I first thought it had to do with the ‚ÄúClient Tools Connectivity‚ÄĚ of SQL Server which are required on the TFS application Tier.

After some digging I finally realized, that I missed the obvious.

The SQL2010 assembly has a different version!!!

01 'Microsoft.AnalysisServices.AnalysisServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

So it will never work with SQL2012 connectivity tools at least until now with TFS SP1.

Update:

If you install SQL Server 2008R2 Client Tools Connectivity on TFS Application Tier and SQL Server 2012 Client Tools Connectivity (Backwards Compatibility) on the SQL 2012 server it seems to work!
Unfortunately I still have to solve the annoying TF255186 Error with Reporting Services on SQL2012…

Update 2:

Solved!!! Finally solved the last issue by naming the Reporting Services ReportManager URL and WebServices URL by following schema:

http://&lt;ReportServer>/Reports_<InstanceName>
http://&lt;ReportServer>/ReportServer_<InstanceName>

This of course won’t be necessary when your reporting server runs on the default instance of SQL.
Thank you MS for not pointing out that this seems to be a requirement…

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

Update 3:

After the installation, I realized, that although everything was setup fine and all databases per created, I still had no access to the reports

Reporting Services answered with

01 An error has occurred during report processing. (rsProcessingAborted)
02 Query execution failed for dataset 'dsArea'. (rsErrorExecutingCommand)
03 For more information about this error navigate to the report server on the local server machine, or enable remote errors

If you look into the SSRS logfile located in C:\Program Files\Microsoft SQL Server\MSRS11.<your instance name>\Reporting Services\LogFiles

I found the error

01 TF221122: An error occurred running job Incremental Analysis Database Sync for team project collection or Team Foundation server TEAM FOUNDATION.

And

01 Errors in the back-end database access module. The provider 'SQLNCLI10.1' is not registered.

As SQL Server 2012 has the SQL Native Client 11.1 (SQLNCLI11.1) I tried to edit the analysis services datasource to use that provider instead. Unfortunately also that didn’t work.

So I installed the the SQLNCLI10.1 which can be downloaded here also on the DATATIER

http://go.microsoft.com/fwlink/?LinkID=188401&clcid=0√ó409

pocessed the cube again on the APPTIER by calling the webservice

 http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx?op=ProcessAnalysisDatabase

with the Processing Type: Full

After a while I checked the status of the warehouse by invoking the webservice

http://localhost:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx?op=GetProcessingStatus

The error about the wrong provider was gone, but now I have the error

01 OLE DB error: OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.

Stay posted‚Ķ ūüėČ

Update 4:

Solved!

you have to check out the permissions of xp_cmdshell in SQL Server Books Online. and enable it if its disabled. by executing following Query in Sql server.

---- To allow advanced options to be changed.

EXEC¬†sp_configure¬†‚Äėshow¬†advanced¬†options‚Äô,¬†1

GO

‚ÄĒ-¬†To¬†update¬†the¬†currently¬†configured¬†value¬†for¬†advanced¬†options.

RECONFIGURE

GO

‚ÄĒ-¬†To¬†enable¬†the¬†feature.

EXEC¬†sp_configure¬†‚Äėxp_cmdshell‚Äô,¬†1

GO

‚ÄĒ-¬†To¬†update¬†the¬†currently¬†configured¬†value¬†for¬†this¬†feature.

RECONFIGURE

GO

restart the services and done. all reports do not report any dsArea’. (rsErrorExecutingCommand) error.

Enjoy ūüôā

Advertisements
 
Leave a comment

Posted by on December 21, 2012 in MSSQL, Tips

 

Tags: , ,

Cannot set a credential for principal ‚Äėsa‚Äô. (Microsoft SQL Server, Error:15535)

In this Quick and dirty post, let’s see a simple fix for one of the annoying error messages you’ll see in SQL Server 2005. Basically, You’ll see below mentioned error in few cases when you try to change the sa password using SSMS.

The Simple trick is to check the box ‚ÄúMap to credential‚ÄĚ as shown below after providing a new password and click Okay.

This should work…Hope this helps!

APPLIES TO
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Express with Advanced Services
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Standard Edition for Small Business
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Express with Advanced Services
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Standard Edition for Small Business
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
 
4 Comments

Posted by on December 11, 2012 in MSSQL, Tips

 

Tags: , , ,

SQL SERVER ‚Äď ERROR: FIX ‚Äď Database diagram support objects cannot be installed because this database does not have a valid owner

Recently one of my friend sent me email that he is having some problem with his very small database. We talked for few minutes and we agreed that to further investigation I will need access to the whole database. As the database was very big he dropped it in common location (you can use livemesh or dropbox  or any other similar product) and I picked up from the location.

I was able to install the database successful. He informed me that he has created database diagram so I can easily understand his database tables. As soon as I tried to open the database diagram I faced following error. For a while I could not figure out how to resolve the error.

Error:
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

Workaround / Fix / Solution :

Well for a while I attempted few things and nothing worked. After that I carefully read the error and I realized that solution was proposed in the error only. I just have to read it carefully. Here are the steps I did to make this work.

-- Replace YourDatabaseName in following script
ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa
GO

  • Select your database >> Right Click >> Select Properties
  • Select FILE in left side of page
  • In the OWNER box, select button which has three dots (‚Ķ) in it
  • Now select user ‚Äėsa‚Äô or NT AUTHORITY\SYSTEM and click OK.

This should solve your problem.

Please note, I suggest you check your security policies before changing authorization. I did this to quickly solve my problem on my development server. If you are on production server, you may open yourself to potential security compromise.

Reference: Pinal Dave (http://blog.sqlauthority.com)

 
Leave a comment

Posted by on December 2, 2012 in MSSQL, Tips

 
 
%d bloggers like this: