18 Replies Latest reply on Mar 31, 2014 1:16 PM by Gregor

    Odd ACE Actions

    tgreen

      My ACE calculations that I've built won't run on their own, they do function when I do a recalculation for the last hour fine.

       

       

       

      Looking at the logs, there's a few things going on.  One that seems to be an issue is this:

       

      Current thread must be set to single thread apartment (STA) mode before OLE calls can be made. Ensure that your Main function has STAThreadAttribute marked on it.

       

      Any ideas on where this may be?  I can't find it

       

       

       

      also get this for a different calc

       

      There is already an open DataReader associated with this Command which must be closed first.

       

      I'm assuming this may be referring to my external SQL server connection I've made for logging various non-PI items.  I'm in the process of searching that out, but if it could be something else, please let me know what to look for!

       

       

       

      Thanks in advance!!!

        • Re: Odd ACE Actions
          tgreen

          I also get these when I first run the Debug

           

          A first chance exception of type 'System.BadImageFormatException' occurred in mscorlib.dll

           

          A first chance exception of type 'System.BadImageFormatException' occurred in mscorlib.dll

           

          A first chance exception of type 'System.Collections.Generic.KeyNotFoundException' occurred in System.dll

           

          A first chance exception of type 'System.Collections.Generic.KeyNotFoundException' occurred in System.dll

           

          A first chance exception of type 'System.Collections.Generic.KeyNotFoundException' occurred in System.dll

            • Re: Odd ACE Actions
              mhamel

              @Trevor: This exception is thrown when the file image of a dynamic link library (DLL) or an executable program is invalid. This means that something wrong occurred in your code. Have you tested the module with the wizard tool? From the PI ACE Menu in Visual Studio, you have two options for testing: debug and test. The former one will test the module by compiling it in debug mode and let you break in your code. The latter compiles it in release mode and test your module with a dialog box to mimic the different operations of the PI ACE scheduler.

               

              It would be important to test with less code to see where the problem could hide.

                • Re: Odd ACE Actions
                  mhamel

                  @Trevor: Can you give more details about the OLE calls your are making to access your database? Since ADO.NET there should be no more use of OLE. Could you share what are your assembly references that you use in your project?

                    • Re: Odd ACE Actions
                      tgreen

                      Thanks for the reply,

                       

                      I am calling DLLs (3rd party) in my code.  There's another calculation that I've built using the exact same referenced DLLs, separate calc altogether.  I'm guessing this won't be the issue as the other one works fine

                       

                      I believe the issue began when I made reference to System.Threading.  Shortly after that, I realized that I didn't need this so I removed it from the ACECalculations.  I did have to do 'Imports System.Threading' prior to the ACE class as the VB environment didn't understand that.  I think if I could remove the Import, I'd be golden, but I lack the experience in VS to know where to look I think

                       

                      Only other oddity that I'm doing that may not be a normal thing is I'm referencing SQL (Select and Insert)

                       

                      How I've done this may not be correct.  Below is some snips out of the code as I'm sure you don't want to read the entire thing! lol

                       

                       

                       

                      In the public area:

                       

                      Dim conn As New Data.SqlClient.SqlConnection()

                       

                      Dim sqlCommand As New Data.SqlClient.SqlCommand()

                       

                       

                       

                      In the ACECalculations (Just the SQL stuff)

                       

                             Dim SQLQuery As String

                       

                             SQLQuery = "Select top 1 * from NatGasComp where MeterID = '" & MeterName & "' order by Timestamp"

                       

                             sqlCommand.Connection = conn

                       

                             sqlCommand.CommandType = Data.CommandType.Text

                       

                             sqlCommand.CommandText = SQLQuery

                       

                             Dim SQLRS As Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader()

                       

                             SQLRS.Read()

                       

                       

                       

                             Blah, blah, blah code

                       

                       

                       

                             SQLRS.Close()

                       

                             SQLRS = Nothing

                       

                       

                       

                             'set up the command object to Gas Meter Configuration Table table

                       

                             SQLQuery = "Select top 1 * from GasMtrConf where MeterID = '" & MeterName & "' order by Timestamp"

                       

                             sqlCommand.CommandText = SQLQuery

                       

                             Dim SQLRSConf As Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader()

                       

                             SQLRSConf.Read()

                       

                       

                       

                             Blah, blah, blah code

                       

                       

                       

                             SQLRSConf.Close()

                       

                             SQLRSConf= Nothing

                       

                       

                       

                             SQLQuery = "Select ExpFact from MatRef where ID = '" & OrfMatRef & "'"

                       

                             sqlCommand.CommandText = SQLQuery

                       

                             x_orf = sqlCommand.ExecuteScalar()

                       

                       

                       

                             SQLQuery = "Select ExpFact from MatRef where ID = '" & TubeMatRef & "'"

                       

                             sqlCommand.CommandText = SQLQuery

                       

                             x_tube = sqlCommand.ExecuteScalar()

                       

                       

                       

                       

                       

                             'Log Alarm conditions resulting from calculations

                       

                             For AlmWrite = 0 To AlmPoint

                       

                                 If AlmList(AlmWrite) <> "" Then

                       

                                     SQLQuery = "Insert Into MeterAlarms (Timestamp, MeterID, FullTag, FriendTag, Status, Value, EU, Description) Values ('" & ScriptStartTime & "','" & MeterName & "', '" & FullPath & "', '" & TagName & "', 1, 'N/A', '" & AlmList(AlmWrite) & "', 'VFC');"

                       

                                     sqlCommand.CommandText = SQLQuery

                       

                                     sqlCommand.ExecuteNonQuery()

                       

                                 End If

                       

                             Next

                       


                       

                      In the ModuleDependentInitialization

                       

                             'SQL SERVER connection

                       

                             conn.ConnectionString = "Data Source=WIN-A03306BE681\BRION_MCKAY_EFMH;Initial Catalog=BrionEnergy;Persist Security Info=True;Trusted_Connection=True"

                       

                             Try

                       

                                 conn.Open()

                       

                             Catch ex As Exception

                       

                                 'MsgBox(ex.Message)

                       

                                 Exit Sub

                       

                             End Try

                       

                                 'MsgBox("connected")

                       

                       

                       

                      In the ModuleDependentTermination

                       

                             conn.Close()

                       

                             conn = Nothing

                       

                       

                       

                      The reasoning here was to open the connection once on startup and leave it open until the DLL is shut down so that I already have connectivity to SQL server when the calc goes to run (make it a little faster)

                       

                      To use the Data.SqlClient.SqlConnection(), I had to add reference to System.Data

                       

                      Let em know if my methodology is incorrect please.  Like I said this does work in the other DLL project and does test fine in my problematic one

                      • Re: Odd ACE Actions
                        tgreen

                        Full zipped project is in 

                         

                        http://vcampus.osisoft.com/discussion_hall/development_with_other_osisoft_products/f/12/t/4918.aspx

                         

                        Sorry there are two threads going on, perhaps someone can merge them

                          • Re: Odd ACE Actions
                            michaelh

                            Optimally, some errors could trigger a reconnection. (Not sure if connection loss is likely in your situaltion)

                             

                            Also for easy debugging, it's always a good idea to minimize ModuleDependentInitialization and do complex tasks in a branch of AceCalculations that is only performed when required (here: when reconnection is required at start or after connection loss)

                              • Re: Odd ACE Actions
                                mhamel

                                From the code you provided, I can see you declare external code within your class without any .NET/COM interop reference. I suspect that your different STA/MTA problems come from there, as PI ACE runs with a MTA model.

                                 

                                When you use external declaration inside .NET class, you'll have to manage the possible differences between the two "worlds". Have you tried to reference these *.dlls within your project and let the tlbimp tool creates the .NET interop? You could have by this an optimized and safe way to utilize your external library.

                                 

                                If you follow Gregor's example without the use of external libraries, does his example works?

                                  • Re: Odd ACE Actions
                                    mhamel

                                    I've edited my post to remove something that wasn't related to my answer.

                                    • Re: Odd ACE Actions
                                      tgreen

                                      by moving my part of setting up the DB connection withing the AceCalculations, it seems to debug with less warnings.  still complains about the "Current thread must be set to single thread apartment (STA) mode before OLE calls can be made. Ensure that your Main function has STAThreadAttribute marked on it"

                                       

                                      I can move everything in to the AceCalculations, but i can already see that moving the the DB connection in to here has slowed the execution time down.  I need these to run as quickly and efficiently as possible

                                       

                                      I figured having the connection created and checking my licensing would be good to only do when the DLL firsts runs, if I can do that within the AceCalculations, that would be good

                                       

                                      I'll start adding the Try CAtches in these areas with the logging to the Pi messages to see if that presents any clarity

                                       

                                      I'll move the DLL references to the ACeCalculations area as that would likely have little impact to the speed of the execution

                                        • Re: Odd ACE Actions
                                          michaelh

                                          Sure, normally you'd reuse the open connection for better performance.

                                           


                                          I had in mind something like a global

                                           

                                             Dim conn As Data.SqlClient.SqlConnection()

                                           

                                          In ACECalcuations a block like

                                           
                                          If conn Is Nothing Then
                                             conn = new Data.SqlClient.SqlConnection()
                                             conn.ConnectionString = "Data Source=WIN-A03306BE681\BRION_MCKAY_EFMH;Initial Catalog=BrionEnergy;Persist Security Info=True;Trusted_Connection=True"
                                             conn.Open()
                                          End If
                                          

                                           

                                           

                                          In case of errors requiring a reconnection you can trigger an emergency reopen by

                                           

                                          conn.Close()

                                           

                                          conn = Nothing

                                           

                                          without the need to manually restart your ACE Context.

                                            • Re: Odd ACE Actions

                                              For everybody that might be wondering if there's missing information, please note that there exists another thread that was maintained in parallel. Please read both to get a complete picture.

                                               

                                              Please consider both threads closed and open a new one. If you have a related question, please provide a link to this thread or the other.  

                                            • Re: Odd ACE Actions
                                              tgreen

                                              I have this working now.  

                                               

                                              The DLLs I left up being declared as I tried to add them in to the project as references and they don't seem to agree with VS.  I'll play with that part more later

                                               

                                              I've modified my queries to the code suggested by Gregor as using the actual column names is likely to work better later since columns could move and an Index reference won't pick that up if something changes, moves get added

                                               

                                              I think the biggest problem was that the ACE module was added on to the system as a 32 bit for some reason.  we're in the works for getting a new machine set up as an actual test (not simply vCampus) so we can interconnect to real data.  I'll make sure that is done as a 64 bit installation through and through.  Likely alleviate my pains.  Don't thing changing the vCampus type ACE installation over to 64 bit would be a simple task unfortunately, so I'll code it this way then modify when I transport it over later

                                               

                                              I appreciate all the help guys!

                                                • Re: Odd ACE Actions

                                                  Hello Trevor,

                                                   

                                                  Thank you for letting us know that you've sorted out things for now.

                                                   

                                                  Regarding your plans about moving your ACE Modules to 64-bit please be sure the libraries you are referencing support 64 bit.

                                                   

                                                  Please allow me referring you to KB00591 describing a known issue you may run into if a library doesn't support 64 bit. Also the PI ACE User Manual has a paragraph titled "Support for 64-bit Calculations" I recommend you to review.

                                  • Re: Odd ACE Actions

                                    Hello Trevor,

                                     

                                    Thank you for updating the other thread as resolved.

                                     

                                    Is there any specific reason you've opened this thread at PI SDK Development forum?

                                     

                                    Both issues you are reporting appear more related to your custom code than to PI ACE.

                                     

                                    # 1 Single thread Apartment
                                    If you are using Class System.Threading you may need to insert a line of code that explicitly sets STA. You should be able to find help within the Internet. Otherwise you may need to mark the method causing the error message as STA. In VB.NET this is done by adding the following line right before the method:

                                     

                                     

                                     
                                    <STAThread()> _
                                    

                                    # 2 Open DataReader
                                    This sounds to me as if you either attempt to open multiple connections at the same time i.e. in a loop or you don't properly close the connection i.e. after accessing tables in SQL Server.

                                      • Re: Odd ACE Actions
                                        tgreen

                                        Sorry, opened it in the wrong thread!

                                         

                                        I do have multiple queries going in and out of a side SQL Server.  I was sure I was closing and setting the connections to nothing prior to using the next one, but I'll recheck that!

                                         

                                        As for the System.Thereading, I did add reference to this at one point, but I ended up not needing it.  I thought I removed it from the project though

                                          • Re: Odd ACE Actions
                                            tgreen

                                            For the life of me, I can't find the reference that still has System.Threading in my project anywhere, yet the problem persists.  Any ideas on how to remove all reference to it (not needed anyway).  I even created a new project, ported the code over and removed the old.  The reference remains.  Must be in Visual Studios somewhere, but I'm still green on .net (lots of VB6 experience)

                                              • Re: Odd ACE Actions
                                                tgreen

                                                Not sure why, but the calculations that were complaining about the DataReader not being closed has begun to work.

                                                 

                                                I haven't changed anything as I was trying to source the other issue down with the System.Threading problem.  I'll let this run for a bit and keep an eye on the log files

                                                 

                                                Anyway, I'm not sure where to add in <STAThread()> as I have nothing referencing threading anymore.  The word 'Thread' doesn't show up in any searches

                                                 

                                                because I did reference it at one time, would it be loaded up in some hidden class reference or something?