title: SSRS Code Shared Variables and Simultaneous Report Execution
body: <p>We have some SSRS reports that are failing when two of them are executed very close together.</p>
  
  <p>I've found out that if two instances of an SSRS report run at the same time, any Code variables declared at the class level (not inside a function) can collide. I suspect this may be the cause of our report failures and I'm working up a potential fix.</p>
  
  <p>The reason we're using the Code portion of SSRS at all is for things like custom group and page header calculation. The code is called from expressions in TextBoxes and returns what the current label should be. The code needs to maintain state to remember what the last header value was in order return it when unknown or to store the new header value for reuse.</p>
  
  <p>Note: here are my resources for the variable collision problem:</p>
  
  <p>The <a href="http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/fb984f28-719b-45c4-aa2f-1bd32caeb93c" rel="nofollow noreferrer">MSDN SSRS Forum</a>:</p>
  
  <blockquote>
    <p>Because this uses static variables, if two people run the report at the exact same
    moment, there's a slim chance one will smash the other's variable state (In SQL 2000,
    this could occasionally happen due to two users paginating through the same report at
    the same time, not just due to exactly simultaneous executions). If you need to be 100% 
    certain to avoid this, you can make each of the shared variables a hash table based on 
    user ID (Globals!UserID).</p>
  </blockquote>
  
  <p><a href="http://odetocode.com/articles/130.aspx" rel="nofollow noreferrer">Embedded Code in Reporting Services</a>:</p>
  
  <blockquote>
    <p>... if multiple users are executing the report with this code at the same time, both 
    reports will be changing the same Count field (that is why it is a shared field). You 
    don’t want to debug these sorts of interactions – stick to shared functions using only 
    local variables (variables passed ByVal or declared in the function body). </p>
  </blockquote>
  
  <p>I guess the idea is that on the report generation server, the report is loaded and the Code module is a static class. If a second clients ask for the same report as another quickly enough, it connects to the same instance of that static class. (You're welcome to correct my description if I'm getting this wrong.)</p>
  
  <p>So, I was proceeding with the idea of using a hash table to keep things isolated. I was planning on the hash key being an internal report parameter called InstanceID with default <code>=Guid.NewGuid().ToString()</code>.</p>
  
  <p>Part way through my research into this, though, I found that it is even more complicated because Hashtables aren't thread-safe, according to <a href="http://prologika.com/CS/blogs/blog/archive/2009/06/30/maintaining-state-in-reporting-services-2008.aspx" rel="nofollow noreferrer">Maintaining State in Reporting Services</a>.</p>
  
  <p>That writer has code similar to what I was developing, only the whole thread-safe thing is completely outside my experience. It's going to take me hours to research all this and put together sensible code that I can be confident of and that performs well.</p>
  
  <p>So before I go too much farther, I'm wondering if anyone else has already been down this path and could give me some advice. Here's the code I have so far:</p>
  
  <pre><code>Private Shared Data As New System.Collections.Hashtable()
  
  Public Shared Function Initialize() As String
     If Not Data.ContainsKey(Parameters!InstanceID.Value) Then
        Data.Add(Parameters!InstanceID.Value, New System.Collections.Hashtable())
     End If
     LetValue("SomethingCount", 0)
     Return ""
  End Function
  
  Private Shared Function GetValue(ByVal Name As String) As Object
     Return Data.Item(Parameters!InstanceID.Value).Item(Name)
  End Function
  
  Private Shared Sub LetValue(ByVal Name As String, ByVal Value As Object)
     Dim V As System.Collections.Hashtable = Data.Item(Parameters!InstanceID.Value)
     If Not V.ContainsKey(Name) Then
        V.Add(Name, Value)
     Else
        V.Item(Name) = Value
     End If
  End Sub
  
  Public Shared Function SomethingCount() As Long
     SomethingCount = GetValue("SomethingCount") + 1
     LetValue("SomethingCount", SomethingCount)
  End Function
  </code></pre>
  
  <ol>
  <li><p>My biggest concern here is thread safety. I might be able to figure out the rest of the questions below, but I am not experienced with this and I know it is an area that it is EASY to go wrong in. The link above uses the method <code>Dim _sht as System.Collections.Hashtable = System.Collections.Hashtable.Synchronized(_hashtable)</code>. Is that best? What about Mutex? Semaphore? I have no experience in this.</p></li>
  <li><p>I think the namespace System.Collections for Hashtable is correct, but I'm having trouble adding System.Collections as a reference in my report to try to cure my current error of "Could not load file or assembly 'System.Collections'". When I browse to add the reference, it's not an available component to select.</p></li>
  <li><p>I just confirmed that I can call code from a parameter's default value expression, so I'll put my Initialize code there. I also just found out about the OnInit procedure, but this has its own gotchas to research and work around: <a href="https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=383212" rel="nofollow noreferrer">the Parameters collection may not be referenced from the OnInit method during parameter initialization</a>.</p></li>
  <li><p>I'm unsure about declaring the Data variable as New, perhaps it should be only be instantiated in the initializer if not already done (but I worry about race conditions because of the delay between the check that it's empty and the instantiation of it).</p></li>
  <li><p>I also have a question about the Shared keyword. Is it necessary in all cases? I get errors if I leave it off function declarations, but it <em>appears</em> to work when I leave it off the variable declaration. Testing multiple simultaneous report executions is difficult... Could someone explain what Shared means specifically in the context of SSRS Code?</p></li>
  <li><p>Is there a better way to initialize variables? Should I provide a second parameter to the GetValue function which is the default value to use if it finds that the variable doesn't exist in the hashtable yet?</p></li>
  <li><p>Is it better to have nested Hashtables as I chose in my implementation, or to concatenate my InstanceID with the variable name to have a flat hashtable?</p></li>
  </ol>
  
  <p>I'd really appreciate guidance, ideas and/or critiques on any aspect of what I've presented here.</p>
  
  <p>Thank you!</p>
  
  <p>Erik</p>