13 Replies Latest reply on Jul 4, 2012 9:01 PM by mhamel

    EventFrames and CLR - Stored Procedure

    wpurrer

      Since PI OLEDB Enterprise with EventFrames doesn't exist.

       

      For me there is only one whay to use Event Frames in a powerpivot in excel.

       

      Through an .net CLR Stored Procedure...

       

      Has some one already done this, and maybe can provide an example,.. hints,...

       

       

       

       

        • Re: EventFrames and CLR - Stored Procedure
          mhamel

          @Wolfgang: You are right, PI Event Frames are not exposed through PI OLEDB Enterprise 2010 R3 but I invite you to take a look at the upcoming webinar on April 4th, 2012 regarding PI Data Access. You'll see the roadmap for the introduction of this feature within different families of products of the PI Data Access suite. For the moment, I have provided you a template of a .NET CLR stored procedure (VB.NET) to let you expose PI Event Frames inside SQL Server.

           

          All the methods to be exposed on the SQL Server side must be provided as static members that return nothing or an integer value. Internal calls can make use of standard access of all managed code and objects. Parameters passed to a CLR stored procedure can be any of the native SQL Server types that have an equivalent in managed code. For the Transact-SQL syntax to create the procedure, these types should be specified with the most appropriate native SQL Server type equivalent.

           

           

           

          After you need to map your assembly inside the SQL Server namespace by using the CREATE ASSEMBLY statement and the path to your *.dll file. You will need to set the permission to UNSAFE because it needs to access resources outside the boundaries of SQL Server. I have added an example on how to map your assembly:

           

           

           

          Finally, you need to create a stored procedure that will use the managed code method. You need to declare the exact same number of arguments that you have in the managed code version and with the equivalent data-type.

           

           

          I suggest you take some time to read these interesting links from Microsoft on the subject here, here and here.

            • Re: EventFrames and CLR - Stored Procedure
              mhamel

              For those of you interested, I have re-posted my answer as the code snippets were not visible.

                • Re: EventFrames and CLR - Stored Procedure
                  wpurrer

                  Deare Mathieu => use google and but in ".net Clr Stored Procedure"  I already have done...

                   

                  There are some more advanced problems with this:

                   

                  for example:
                  http://www.sqlserver-training.com/adding-external-references-to-sql-clr-projects/-

                   

                  I did this already one time in the past, and i think have the right references (and depending references, and depending references,.. and depending references.....) will be another challenge.

                   

                  Integreated Windows Authentification will be another funny thing...

                   

                  Could you maybe provide an more advanced example where you have access some information (i don't care which ) through AF in a .net CLR Stored Procedure with integreated authentifcation (AF should use the user which access the sql server...) and which assemblies i have to add to the sql server (in which order...)

                   

                  I think this will be a good idea for an advanced webinar or documentation in the Vcampus Library...

                    • Re: EventFrames and CLR - Stored Procedure
                      mhamel

                      @Wolfgang: In the past, I have used PI SDK and Event Pipes (or sign up for updates) to be available within SQL Server 2008 R2. My .NET CLR stored procedure was relying on OSIsoft.PISDK.dll and OSIsoft.PISDKCommon.dll assemblies. I have copied them in the same folder as my assembly containing the CLR stored procedures because the CREATE ASSEMBLY statement looks for any dependent assemblies of this assembly in the same location and also uploads them with the same owner as the root level assembly. If these dependent assemblies are not found and they are not already loaded in the current database, CREATE ASSEMBLY fails. If the dependent assemblies are already loaded in the current database, the owner of those assemblies must be the same as the owner of the newly created assembly.

                       

                      I can certainly provide some examples regarding PI AF and PI Event Frames. I don't have any examples ready but I can work on some and post them when I complete them.

                        • Re: EventFrames and CLR - Stored Procedure
                          scolli

                          Mathieu,

                           

                          I am attempting to create a CLR stored procedure that uses the PISDK. Apparently, I need to first register OSIsoft.PISDK.dll and OSIsoft.PISDKCommon with SQL server using CREATE ASSEMBLY. Can you explain how you registered these and how you use them in a CLR stored procedure. Some code examples would help a great deal.

                           

                          Thanks,

                           

                          Scott

                            • Re: EventFrames and CLR - Stored Procedure
                              mhamel

                              @Scott: Before registering your assembly against the SQL Server, make sure you have copied a local version of the OSIsoft.PISDK.dll and OSIsoft.PISDKCommon.dll files in the same folder as your own assembly (.dll file). Don't forget to copy the respective .config file. You will be able invoke a command such as the one below.

                               
                              CREATE ASSEMBLY  from N'path_to_assembly_dll' WITH PERMISSION_SET = UNSAFE statement required.
                              GO
                              

                              Every CLR related stored procedure must match a public method within your assembly.

                               

                              I strongly suggest you to take a look at this link from Microsoft to learn how to do these steps.

                                • Re: EventFrames and CLR - Stored Procedure
                                  ygalipeau

                                  Mathieu

                                   

                                  when i run my procedure in SQL i receive this error:

                                   

                                   

                                   

                                  A .NET Framework error occurred during execution of user-defined routine or aggregate "ExtractArchiveData":
                                  System.Security.SecurityException: That assembly does not allow partially trusted callers.
                                  System.Security.SecurityException:
                                     at PISDKExtractions.StoredProcedures.ExtractArchiveData()

                                   

                                   

                                   

                                  did you ran into this?

                                   

                                  thanks

                                   

                                  yannick

                                    • Re: EventFrames and CLR - Stored Procedure
                                      mhamel

                                      @Yannick:

                                       

                                      Sharing code libraries is a common scenario with common language runtime (CLR) integration, where an assembly containing a user-defined type, stored procedure, user-defined function, user-defined aggregate, trigger, or utility class is often accessed by another assembly or application. Code libraries that are to be shared by multiple applications must be signed with a strong name.

                                       

                                      From Microsoft SQL Server, only applications that are fully trusted by the runtime code access security system are allowed to access a shared managed code assembly that is not explicitly marked with the System.Security.AllowPartiallyTrustedCallers attribute. A partially trusted assembly (one that is registered in SQL Server with the SAFE or EXTERNAL_ACCESS permission set) that attempts to access a strong-name signed assembly without this attribute causes a System.Security.SecurityException to be thrown.

                                       

                                      In your case, this will be translated by either marking your .NET assembly with the AllowPartiallyTrustedCallers attributes and adding it into SQL Server with a SAFE mode or not marking your .NET assembly and adding it into SQL Server with an UNSAFE mode.

                                       

                                       

                                        • Re: EventFrames and CLR - Stored Procedure
                                          ygalipeau

                                          Mathieu, it have try to mark the assembly with the AllowPartiallyTrustedCallers attributes but was not able to make it work. however, setting it as unsafe work fine.

                                           

                                          thanks

                                           

                                          yannick

                                            • Re: EventFrames and CLR - Stored Procedure
                                              ygalipeau

                                              another question, evrything work fine, but it's been two time now that at a certain point, i receive the following message:

                                               

                                               

                                               

                                              A .NET Framework error occurred during execution of user-defined routine or aggregate "ExtractArchiveData":
                                              System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {3BCB2DC2-5F66-11D1-BD64-0060B0290178} failed due to the following error: 80070008.
                                              System.Runtime.InteropServices.COMException:
                                                 at PISDKExtractions.StoredProcedures.ExtractArchiveData(String PIServer, String PiTag, String Aggregate, DateTime starttime, DateTime endtime)

                                               

                                               

                                               

                                              once i get this error, any call i am doing are giving the same error. it seems that something is lost/lock somewhere and i am clueless

                                               

                                              the only way i had found so to fix the error so far is to delete all the osi and my assembly from SQL Server and put them back.

                                               

                                               

                                               

                                              any idea?

                                               

                                              thanks

                                               

                                              yannick

                                                • Re: EventFrames and CLR - Stored Procedure
                                                  mhamel

                                                  @Yannick: You exception message points out that component with CLSID {3BCB2DC2-5F66-11D1-BD64-0060B0290178} which is the PISDKClass object which is missing. Before registering your assembly against the SQL Server, make sure you have copied a local version of the OSIsoft.PISDK.dll and OSIsoft.PISDKCommon.dll files in the same folder as your own assembly (.dll file). Don't forget to copy the respective .config file. You will be able invoke a command such as the one below.

                                                   

                                                  Microsoft SQL Server stores internally a copy of the assembly required for your CLR stored procedures. SQL Server will only use what is defined in its namespace and won't go outside. In your case, it seems the PISDKClass definition does not exist.

                                                   

                                                  Did you get an error/exception when you tried to register your assembly within SQL Server?

                                                    • Re: EventFrames and CLR - Stored Procedure
                                                      ygalipeau

                                                      mathieu, this is what i did. call to my assembly can work 500 times or more and then suddenly this error appears and i need to delete everything from SQL and reload all. so it works and stops at a certain point for no reason that i can identify.

                                                       

                                                      yannick

                                                        • Re: EventFrames and CLR - Stored Procedure
                                                          mhamel

                                                          To find out where are stored your assembly in your SQL Database, you can use the query below. Just replace the SandBox database name with the proper one and execute. You will see which assemblies are loaded under SQL Server workspace.

                                                           
                                                          USE SandBox
                                                          GO
                                                          
                                                          SELECT
                                                               T1.[name],
                                                               T1.[principal_id],
                                                               T1.[assembly_id],
                                                               T1.[clr_name],
                                                               T1.[permission_set],
                                                               T1.[permission_set_desc],
                                                               T1.[is_visible],
                                                               T1.[create_date],
                                                               T1.[modify_date],
                                                               T1.[is_user_defined],
                                                               T2.[name],
                                                               T2.[file_id],
                                                               T2.[content]
                                                            FROM
                                                               [SandBox].[sys].[assemblies] T1
                                                               INNER JOIN
                                                               [SandBox].[sys].[assembly_files] T2
                                                                    ON T2.assembly_id = T1.assembly_id
                                                          GO