I have moved to Medium. Please follow me there
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:
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'
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
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".
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.
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.