Cannot set the initialization properties for OLE DB provider “MSOLAP” for linked server “TABULAR”

Description

When querying a SSAS instance using a linked server on a remote server while the same user also have a RDP session (Active/Inactive) to the same remote server using SSMS or any other method for ex SQL Unit Tests this error is produced:

OLE DB provider "MSOLAP" for linked server "TABULAR" returned message "The following system error occurred: ".
Msg 7373, Level 16, State 2, Line 1
Cannot set the initialization properties for OLE DB provider "MSOLAP" for linked server "TABULAR".

Microsoft Connect Item

Please vote on this connect item if you experience the same https://connect.microsoft.com/SQLServer/feedback/details/1470258

Steps to Reproduce

The setup consists of 3 different machines:
LOCALHOST (LH)
REMOTE SQL SERVER (RSQL)
REMOTE SSAS SERVER (RSSAS)

Ensure that you don’t have a RDP session (active/inactive i.e. disconnected) to RSQL
Create Linked Server to RSSAS on RSQL using a domain service account for security context as below

exec master.dbo.sp_addlinkedserver @server = N'TABULAR', @srvproduct=N'', @provider=N'MSOLAP', @datasrc=N'RSSAS\tabular_2012', @catalog=N'GroupDW'
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TABULAR',@useself=N'False',@locallogin=null,@rmtuser=N'domainX\builduser',@rmtpassword='SomePassword'

Scenario 1:

Now execute an open query from LH SSMS on RSQL for example:

select
    *
from
    openquery (TABULAR, 'EVALUATE SUMMARIZE (''#Loan Process'', ''~Data Source''[Source System], "Agreed Final Loan To Income Ratio",[Agreed Final Loan To Income Ratio])')

You get the results back from SSAS as expected

Scenario 2:

Login to RSQL via Remote Desktop to create a RDP Session
Now execute same query as before

OLE DB provider "MSOLAP" for linked server "TABULAR" returned message "The following system error occurred: ".
Msg 7373, Level 16, State 2, Line 1
Cannot set the initialization properties for OLE DB provider "MSOLAP" for linked server "TABULAR".
Advertisements

SAFastTrack Prices – Don’t Pay £15 to see if you want to use them

I recently looked for a renewal service as I was in a bit of a pinch. After looking at the reviews and everyone raving about it, I thought I’ll bite the bullet. I paid my £15 and logged in. After logging in I realised that their prices are quite expensive and I’m not willing to pay that amount unless I absolutely have to.

Fair enough, I guess you can charge whatever you want for your services, but I feel it’s a bit of a scam charging people to decide if they actually want to, or in some cases can afford to, use their services. So don’t get ripped-off and pay £15 find the January 2014 prices below.

Vague SSIS error message – SSIS Error Code DTS_E_PROCESSINPUTFAILED

So today I got the error message below

DFT Extract interface_RiskScorecard:Error: System.NullReferenceException: Object reference not set to an instance of an object.
 at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
 at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
 at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket) 
DFT Extract interface_RiskScorecard:Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. 
The ProcessInput method on component "Parse Scorecard Name into properties" (66) 
failed with error code 0x80004003 while processing input "Input 0" (76). 
The identified component returned an error from the ProcessInput method. 
The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. 
There may be error messages posted before this with more information about the failure.

So this happened when I passed some names through RegEx in a script task to split it into different parts. If you look at the error there are some things that stand out:

  • NullReferenceException: Object reference not set to an instance of an object.
  • Parse Scorecard Name into properties
  • Input 0

Ok, so this sort of points me in the direction of where the error happened, well at least which task caused it. In my script task I’m setting the values of 4 output columns with the results of different RegEx expressions. Now you will notice this error does not point to the line in the code or which function it was. The only thing I had to go by was the “object reference…” message. This led me to looking for working with an object that could potentially be null when I’m trying to manipulate it. Eventually I found it, I was referencing the Length property on a string with a null value in an If Statement, but I did not expect it to be a null value. To fix it, I just had to add “myStrProp != null” to test nullability first before accessing the Length property. This is a very common error in C# programming and easy to fix. I just wish the error messages from SSIS script components gave a better message as to where the error is.

QlikView – Open, Save, Close a QVW without reloading data using PowerShell

So you can open a QVW file from the command line with one of the following options

/r Reload, save, close.
/rp Partial-Reload, save, close.
/l Reload, leave open.
/p Partial-Reload, leave open.
/v Pass variable.

None of these options suited me, what I wanted was the following; Open QVW, immediately save it without doing anything i.e. not reloading data, not open script, nothing… then close the file. I was pointed towards the Automation Documentation over here http://community.qlikview.com/docs/DOC-1793 and I was provided with a macro that uses the COM Object in one QVW that opens, saves and closes another QVW. This solved the problem and could be called from the command line.

I didn’t like this solution as it was a bit hacky… Having one file call another etc etc, and I use PowerShell for the rest of my automation; I figured there has to be a PowerShell solution and came up with the following script:

function qv-SaveAndClose-QVW
{
 param(
  [Parameter(Mandatory=$true,ValueFromPipeline=$true)]
  $QvwPath
 )
 $qvComObject = new-object -comobject QlikTech.QlikView
 $NewDoc = $qvComObject.OpenDoc("$QvwPath")
 $NewDoc.Activate()
 $NewDoc.Save()
 $NewDoc.CloseDoc()
 $qvComObject.Quit()
}