9 Replies Latest reply on May 16, 2014 11:51 AM by Marcos Vainer Loeff

    Using PISDK in a SQLServer CLR Assembly

    mnadeau

      I created a class library in .Net to allow me to interface with PI in realtime in SQL Server.

       

      I have resgistered it as an assembly in SQL Server and created my Stored Procedure (which uses the assembly) to execute.

       

      My routine to insert values into PI for a tag is working properly.  My routine to get the value of a PI tag is not working.  I put in some debug messages into my class library to pinpoint where the problem is.  It is on this line of code. :

       

      dblTagResult = pv.Value (pv is of type PISDK.PIValue)

       

      I get the error message -> Dynamic operations can only be performed in homogenous AppDomain.

       

      I wrote a wrapper windows form to also execute my class library and it is working fine.  Its only when I introduce SQL Server I have the issue.

       

       

       

      Thanks in advance for your assistance.

       

       

        • Re: Using PISDK in a SQLServer CLR Assembly
          mnadeau

          I'm am using VS2010 & SQL Server 2012

            • Re: Using PISDK in a SQLServer CLR Assembly
              mnadeau

              I also tried adding an app.config file to my project with the following code and copying that to the same location as the DLL (residing on the SQL Server and used in the SQL Server Assembly).  This also did not fix the issue.

               

              <?xml version="1.0" encoding="utf-8" ?>

               

              <configuration>

               

               <runtime>

               

                 <NetFx40_LegacySecurityPolicy enabled="true"/>

               

               </runtime>

               

              </configuration>

                • Re: Using PISDK in a SQLServer CLR Assembly
                  Marcos Vainer Loeff

                  Hello Marc,

                   

                  We actually are not used developing a class library in .Net to allows to interface with PI in real time in SQL Server (CLR) because we have two products that makes our life much easier: PI OLEDB Provider and PI OLEDB Enterprise. Why don't you take advantage of those products?

                   

                  I have spent some hours trying to make it work with PI SDK and PI AF SDK with no success.  Could you describe exactly what you have done so far with some code snippets? Did you have to import the PI SDK libraries into the SQL Server with the CREATE ASSEMBLY command?

                    • Re: Using PISDK in a SQLServer CLR Assembly
                      Marcos Vainer Loeff

                       Hello Marc,

                       

                       After trying a little more, I was able to register my custom assembly CLR referencing PISDK.

                       

                       Nevertheless, I want to share you my conclusions about this topic.

                       

                       According to this article, CLR integration in SQL Server does not have access to all .NET libraries/namespaces registered on global assembly cache (GAC) by default, but only some of them: 

                      • CustomMarshalers
                      • Microsoft.VisualBasic
                      • Microsoft.VisualC
                      • mscorlib
                      • System
                      • System.Configuration
                      • System.Data
                      • System.Data.OracleClient
                      • System.Data.SqlXml
                      • System.Deployment
                      • System.Security
                      • System.Transactions
                      • System.Web.Services
                      • System.Xml
                      • System.Core.dll
                      • System.Xml.Linq.dll

                       

                       

                      If you try for instance to register the PI AF SDK 2.6 reference, you will use the following command:  

                       
                      CREATE ASSEMBLY [OSIsoft.AFSDK]
                      
                      FROM 'C:\Program Files (x86)\PIPC\AF\PublicAssemblies\4.0\OSIsoft.AFSDK.dll'
                      
                      WITH PERMISSION_SET = UNSAFE
                      

                        

                       

                      After running this code snippet on SQL Server 2012, you will receive an error message saying that there are other .NET libraries required by OSIsoft.AFSDK as System.Runtime.Serialization.dll and System.ServiceModel.dll that still needs to be registered. While performing this task, you will receive the following warning message:

                       


                       

                      "Warning: The Microsoft .NET Framework assembly 'system.servicemodel, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details."

                       

                       

                       

                      Even if you keep trying to register all the required .NET assemblies, you will ultimately receive a different error message:

                       

                      "Msg 6544, Level 16, State 1, Line 1

                       

                      CREATE ASSEMBLY for assembly 'System.ServiceModel' failed because assembly 'microsoft.visualbasic.activities.compiler' is malformed or not a pure .NET assembly. Unverifiable PE Header/native stub."

                       

                      And this is where I stopped. I didn't think it was worth it to keep pushing because even if I make it work, it wouldn't be a stable solution. Probably you will have to do the same procedure when upgrading PI AF SDK to version 2.7 in the future. 

                       

                      A better option and more stable would be using PI Web API instead of PI AF SDK and PI SDK. The advantage is that you will probably need to register only two external libraries. One to get the string response from the PI Web API service and the other reference to convert it into a JSON object. You are also able to send values to the PI Server. Even if you upgrade PI Web API, you won't have issues with the assemblies.

                       

                      Nevertheless, due to performance reasons, I would rather take advantage of  PI OLEDB Provider and PI OLEDB Enterprise. Those products were developed to solve exactly the type of issue you are facing: integrate SQL Server and the PI System.

                       

                       Hope this helps!!

                       

                       

                        • Re: Using PISDK in a SQLServer CLR Assembly
                          Marcos Vainer Loeff

                          Hello Marc,

                           

                          You might want to read this blog post I have just created.

                            • Re: Using PISDK in a SQLServer CLR Assembly
                              mnadeau

                              I have already successfully been able to get this to work using PIPoint.Data.UpdateValue.  

                               

                              I now want to use Data.RecordedValues and access the .Value property of a PIValue and this is where I am seeing an error.  Have you been able to retrieve the. Value property of a PIValue, as opposted to updating the values?  I can access the PIValue.timestamp fine it is only the PIValue.Value that gives me an error.

                               

                              This is the error I get:

                               

                              Dynamic operations can only be performed in homogenous AppDomain.

                                • Re: Using PISDK in a SQLServer CLR Assembly
                                  Marcos Vainer Loeff

                                  Hi Marc,

                                   

                                  I was able to make it work, getting the snapshot value.

                                   

                                  Here is the code snippet in C# for the SQL assembly.

                                   

                                   

                                   
                                  public partial class UserDefinedFunctions
                                  {
                                      [Microsoft.SqlServer.Server.SqlFunction]
                                      public static SqlString GetSnapshotValuePISDK(SqlString piServerName, SqlString piPointName)
                                      {
                                          Server myPIServer = null;
                                          try
                                          {
                                              PISDK.PISDK sdkroot = new PISDK.PISDK();
                                              myPIServer = sdkroot.Servers[piServerName.ToString()];
                                              myPIServer.Open();
                                              PIPoint myPIPoint = myPIServer.PIPoints[piPointName.ToString()];
                                              PIValue SnapshotValue = myPIPoint.Data.Snapshot;
                                              return (SnapshotValue.Value.ToString());
                                  
                                  
                                          }
                                          catch (Exception ex)
                                          {
                                              SqlContext.Pipe.Send("An error occured" + ex.Message + ex.StackTrace);
                                          }
                                          finally
                                          {
                                  
                                              if (myPIServer != null)
                                              {
                                                  myPIServer.Close();
                                              }
                                          }
                                          return new SqlString (string.Empty);
                                      }
                                  }
                                  

                                   From my understanding, you should create a User Defined Function and not a Stored Procedure due to this error:

                                   

                                  "CREATE PROCEDURE failed because a CLR Procedure may only be defined on CLR methods that return either SqlInt32, System.Int32, System.Nullable<System.Int32>, void."

                                   

                                  On SSMS, you will need to run the following query after creating the assembly:

                                   

                                   

                                   
                                  CREATE FUNCTION [dbo].[GetSnapshotValuePISDK]
                                  (@piServerName NVARCHAR (4000),@piPointName NVARCHAR (4000))
                                  RETURNS nvarchar(200)
                                  AS
                                   EXTERNAL NAME [PISDK_CLR].[UserDefinedFunctions].[GetSnapshotValuePISDK]
                                  

                                   

                                   

                                   And finally, to get the snapshot value of PI Point sinusoid, just run the query below:

                                   

                                   

                                   
                                   select dbo.GetSnapshotValuePISDK('marc-pi2014','sinusoid')
                                  

                                   

                                   

                                  Hope this helps!

                                   


                                   

                                   

                                    • Re: Using PISDK in a SQLServer CLR Assembly
                                      mnadeau

                                      Will this work since I don't know the exact time the last value was registered.  I need to have the last value.  So if I sent a value of 20 to the tag 10 minutes ago and nothing since I need to have the value of 20 sent back to me (not an interpolated value).  I would also need to be able to get this to work where I pass in a date.  So maybe I don't want the value as of right now but the value as of 2 hours ago.

                                       

                                      Thanks

                                        • Re: Using PISDK in a SQLServer CLR Assembly
                                          Marcos Vainer Loeff

                                          Hi Marc,

                                           

                                          When you send a value to the PI Server, you should use PI System Management Tools in order to make sure that the timestamp of the new value is the one you expected to be. The same check you can do retrieving values by comparing if the timestamp of the value received is the same one that is stored on the PI Server. You can detect easier a problem if you follow this procedure.

                                           

                                          Your questions are related to PI SDK and not using PI SDK with CLR. Therefore, I would first create a .NET class with the InsertValue() and GetValue() methods and when they are working fine, you try to implement them on the CLR assembly.

                                           

                                          According to your description, I believe that you are looking for the ArcValue method of the PIData object. This method lets you input the timestamp returning a PIValue object if the value with this timestamp is found. Please refer to the PI SDK Programming Reference located on %PIHOME%\Help for additional information.