What is the RPC and RPC Out option on a SQL Server linked-server?

Someone asked me this week – what are the relevance of the RPC and RPC Out settings on a linked server? Do you need both of them or only one of them set to True? The documentation is online in this article, but it doesn’t clearly show examples or provide enough context, so I will try…


SSIS error when deploying from VS 2013 to SSISDB in SQL 2012

An SSIS package has been developed in SSDT-BI Microsoft Visual Studio Professional 2013 (freely available download). When we deploy the package to SSISDB in SQL Server 2012 RTM or SP1 (11.0.3153.0) and run it, it returns the following error, because the SSIS 2014 package format is not downgradable to SSIS 2012 version format.  “The package failed…


Analysis Services fails on a cluster with “An attempt was made to access a socket in a way forbidden by its access permissions”

The problem On a clustered SQL Server 2008 R2 installation, we have installed SQL Server in two separate instances on each cluster node. One Analysis Services resource will start, but the second one will not come online. The error is shown in the application event log and in the MSOLAP\LOG\msmdsrv.log Error(s): event id: 27 -…


Cannot reopen an Analysis Services Tabular Project the second time– error database already exists in the detached state

Using SQL Server 2012 Analysis Services running in Tabular mode, we get this error every time we open an existing Tabular Project solution. Even creating a new Analysis Services Tabular Project, closing it, and opening a second time again causes the same error. An error occurred while opening the model. Click Details for more information….


Cache Warming on an Analysis Services Tabular server

SQL Server 2012 introduces the new tabular BISM modeling in Analysis Services.  If you have many tabular databases deployed, or several large tabular databases, a server restart can cause a performance hit. The typical scenario is that when the Analysis Services service starts, it does not immediately load and warm the cache of the database…


Script Component Recompile in SQL Server 2012 Integration Services (SSIS) to refresh metadata

In SQL Server releases 2005-2008 R2 there is a programmatic approach to be able to dynamically change dataflow columns and their data types in a dataflow task from the SSIS programmatic APIs, and have a Script Component still work magically (most of the time). In Microsoft Customer Service and Support we saw this from time…


How do I ensure Analysis Services client TCP connectivity is encrypted?

SQL Server Analysis Services often deals with sensitive financial numbers, sales figures,  and strategic information used by businesses. Therefore it is natural to want to protect and encrypt data when is it queries to and fro from the Analysis Services over the TCP layers in Windows. I’m am going to talk about network traffic encryption…


Analysis Services Synchronization fails with permissions error

When you want to run Synchronization to copy an Analysis Services database from one server to another, the account running the synchronization needs to be a server admin in the Analysis Services instance receiving the database.   For example, when running a job to do synchronization from a SQL Agent job, you may see the…


SQL 2012 Data Mining Add-in for Office 2013 prerequisites error

If you try to install the Data Mining Add-in into Office 2013 the setup may fail with the prerequisite error. This may happen if you are using the ClickToRun install of Office, chances are you still have Office 2010 or another version side-by-side, as ClickToRun  doesn’t uninstall previous versions of Office that are installed.  Error:…


Getting Error in an Excel 2010 PivotTable “The field that you are moving cannot be placed in that PivotTable area” when referring to PowerPivot data sources

We kept getting this error when attempt to drag and drop dimensions attributes into a PivotTable in Excel 2010 “The field that you are moving cannot be placed in that PivotTable area” We were trying to use an Excel workbook to drop in dimension attributes that are sourced from data living in the PowerPivot for…