2 SQL 2012 Standard Edition servers in a VM environment. SQL2 (OLTP) and SQL3 (DATAWAREHOUSE).
Let me run you through the situation.
Error above, with some other errors as well, has become an issue since the weekend.
The problem is intermittent, but can be repeated.
This happens almost every time we use a linked server and connect to views.
It has started happening after a windows update and a server restart last weekend.
Now, it seems to me like a sql statement optimising issue.
We can call the following statement in SQL03 where the "OLTP" component is a linked server back to SQL02 where the Membership DB resides. The Companies table isn't a table it is a view. (I am not impressed either with the naming). Now comes the tricky bit.
The Companies view has in it's From statement references to both data on SQL02 (ie a local call) and via a linked server connection to SQL03 as well.
I think that suddenly the SQL Query Optimiser is having issues around the "double hop" Linked Server calls. I don't know how to prove or disprove that.
Anyway, if when I am executing the statement in SSMS I can execute the query say 4 times and get the 33094 error, and 2 others...
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'Membership.dbo.Companies' because of binding errors.
Msg 33094, Level 16, State 1, Procedure Members, Line 57
An error occurred during Service Master Key decryption
On the 5th and 6th refresh, I may get only the 4413 and 33094 errors only.
On the 7th refresh, I just get the 33094 error.
On the 8th time say...bingo, I get my data.
On the 9th + time...data is good.
Now, that is fine running statements by hand in SSMS, when I have calls in apps, or in SSIS packages, I get failure because it craps out on the first attempt.
Steps to rectify so far.
1. Regenerate Service Master Key on SQL02 and SQL03. Same problem.
2. Tried creating a new linked server. Same problem.
3. Restarted both SQL services, Same problem.
4. Restarted both Windows machines. Same problem.
Is there anything you all can suggest. I can't see why it would be OK Friday and then not on Monday.