1 Reply Latest reply on Aug 30, 2011 8:51 PM by spilon

    URGENT HELP REQUIRED : PI SDK connection to PI Server is failing from SDK based SQL CLR code

    schugh@nalco.com

       

       

      Hi , 

       

      We are facing an exception below while connecting to PI Server from SQL, We are using a .net based clr code to connect to PI Server , This is installed in SQL DB as a CLR assembly.

       

      Here is the code for assembly and here is the error we are facing. This code is running in most of our environment without any issue but suddenly stopped after the weekend. Error is given below. We went to DB server and checked for PI SDK connectivity to PI Server, it is connecting as expected but from code it is giving com exceptions. We have checked that CLR is enabled at SQL and even our assembly is running under unrestricted mode, which means it can execute unsafe code. All the settings are matching to the other environments where this code is running as expected but in one of our environment it is giving com exceptions.

       

       

       

      exec PI_GetMeasurementData '10450.07FD382D-2421-E011-92BA-0017A477202C','08/20/2011','08/28/2011',1,15;

       

       

       

      CLR PREPARING TO SEND DATA

       

      CLR Exception: GetArchiveData:  Connection to the server lost.  : System.Runtime.InteropServices.COMException (0x8004049A): Connection to the server lost. 

       

         at PISDK.PISDKClass.GetPoint(String ServerTagName)

       

         at StoredProcedures.GetPiArchive(String piTagName, SqlDateTime startDate, SqlDateTime endDate, SqlInt32 parameterId, SqlInt32 controllerId, DataTable& tagMeasurements, Boolean includeParameterId, Boolean includeControllerId, Int32 ArchiveCallType, String PiArgument)

       

      CLR ROWS:0

       
      using System;
      
      using System.Data;
      
      using System.Data.SqlClient;
      
      using System.Data.SqlTypes;
      
      using Microsoft.SqlServer.Server;
      
      using System.Data.OleDb;
      
      using System.Collections;
      
      using System.Collections.Generic;
      
      using System.Xml;
      
      using PISDK;
      
       
      
      //TEB UPdated to except some control parameters
      
      //TEB Added Interpolated Option
      
      //TEB ArchiveCallType = 0 = Arvhive, 1 = Interpolated, 2 = Piplot
      
      //TEB PiArgument = the string to send indicating the parameters for the the call type Interpolated = the timestep, Piplot = screen resolution 
      
      //TEB updated to using 
      
      public partial class StoredProcedures
      
      {
      
       
      
          [Microsoft.SqlServer.Server.SqlProcedure()]
      
          public static void GetMeasurementsByTableCLR(SqlXml tagList, SqlDateTime timeStart, SqlDateTime timeEnd, SqlInt32 maxTagsToProcess,
      
              SqlBoolean includeParameterId, SqlBoolean includeControllerId, SqlInt16 ArchiveCallType, SqlString PiArgument, SqlBoolean TimeValueOnly, out SqlXml MeasurementData)
      
          {
      
              string piServerName = "";// = @"\\rov2619\";
      
              string query = "";
      
              int timeout = 90000; //milliseconds
      
       
      
              SqlCommand command;
      
              XmlReader tagReader;
      
              DataSet tagData = new DataSet();
      
       
      
              //get SQLXml taglist into a datset
      
              tagReader = tagList.CreateReader();
      
              tagData.ReadXml(tagReader);
      
       
      
              //get first CID, PID so that we can get the correct PIServer
      
              DataRow firstTag = tagData.Tables[0].Rows[0];
      
       
      
              if (ArchiveCallType.IsNull)
      
              {
      
                  ArchiveCallType = 0;
      
              }
      
       
      
              //get the piTagName and the piServerName of the Measurement with the given parameterId and controllerId
      
              using (SqlConnection connection = new SqlConnection("context connection=true"))
      
              {
      
                  connection.Open();
      
       
      
                  try
      
                  {
      
                      //Get piServerName
      
                      query = "SELECT ServerName FROM PIServers PS INNER JOIN Measurement M ON PS.PiServerIDI = M.PiServerIDI WHERE M.PiTagName = '" + firstTag["TagName"].ToString() + "' ";
      
                      command = new SqlCommand(query, connection);
      
                      piServerName = @"\\" + command.ExecuteScalar().ToString() + @"\";
      
                  }
      
                  catch (Exception ex)
      
                  {
      
                      //return some error state
      
                      SqlContext.Pipe.Send("CLR Exception: Get PI server: " + ex.Message + ": " + ex.ToString());
      
                  }
      
              }
      
       
      
              ////Call GeneratePIData via delegate
      
       
      
              ////Create a delegate to facilitate timeout
      
              ////Note that because this function is running in a separate thread
      
              //// it is not allowed to call SqlContext. (i.e. cannot send messages).
      
              //MethodDelegate timeoutDelegate = new MethodDelegate(GeneratePIData);
      
       
      
              //DataTable tagMeasurementsTable;
      
              ////loop through tags and output data for each one
      
              //IAsyncResult ar = timeoutDelegate.BeginInvoke(piServerName, timeStart, timeEnd, tagData, maxTagsToProcess,
      
              //(bool)includeParameterId, (bool)includeControllerId, (int)ArchiveCallType, (string)PiArgument, TimeValueOnly, out tagMeasurementsTable, null, null);
      
       
      
              //if (ar.AsyncWaitHandle.WaitOne(timeout, false))
      
              //{
      
              //    timeoutDelegate.EndInvoke(out tagMeasurementsTable, ar);
      
              //    SendDataTableOverPipe(tagMeasurementsTable);
      
              //}
      
              //else
      
              //{
      
              //    timeout = timeout / 1000;
      
              //    SqlContext.Pipe.Send("Operation timeout after " + timeout + " seconds");
      
              //}
      
       
      
              SqlContext.Pipe.Send("CLR PREPARING TO SEND DATA ");
      
              DataTable tagMeasurementsTable;
      
              try
      
              {
      
                  GeneratePIData(piServerName, timeStart, timeEnd, tagData, maxTagsToProcess,
      
                  (bool)includeParameterId, (bool)includeControllerId, (int)ArchiveCallType, (string)PiArgument, TimeValueOnly, out tagMeasurementsTable);
      
       
      
                  //SqlContext.Pipe.Send("CLR ROWS:" + tagMeasurementsTable.Rows.Count.ToString());
      
                  SqlContext.Pipe.Send("CLR ROWS:" + tagMeasurementsTable.Rows.Count.ToString());
      
              }
      
              catch (Exception ex)
      
              {
      
                  SqlContext.Pipe.Send("CLR Exception: Building Data: " + ex.Message + ": " + ex.ToString());
      
                  tagMeasurementsTable = new DataTable();
      
              }
      
       
      
       
      
              try
      
              {
      
                  SendDataTableOverPipe(tagMeasurementsTable);
      
       
      
              }
      
              catch (Exception ex)
      
              {
      
                  if (SqlContext.Pipe.IsSendingResults == true)
      
                  {
      
                      SqlContext.Pipe.SendResultsEnd();
      
                  }
      
                  SqlContext.Pipe.Send("CLR Exception: Sending Data: " + ex.Message + ": " + ex.ToString());
      
                  //MeasurementData = null;
      
              }
      
       
      
              MeasurementData = null;
      
       
      
              //try
      
              //{
      
              //    SqlContext.Pipe.Send("CLR GenerateXML: " + System.DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));
      
              //    //Convert the data to SQL XML and send it out
      
              //    DataSet dsXMLReturn = new DataSet();
      
              //    dsXMLReturn.Tables.Add(tagMeasurementsTable);
      
              //    string sReturnXML = dsXMLReturn.GetXml();
      
              //    XmlTextReader xmlReader = new XmlTextReader(new System.IO.StringReader(sReturnXML));// = XmlTextReader.Create( new System.IO.StringReader(sReturnXML));
      
       
      
              //    SqlXml outXml = new SqlXml(xmlReader);
      
              //    SqlContext.Pipe.Send("CLR GenerateXML End: " + System.DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));
      
              //    MeasurementData = outXml;//GetXml()
      
              //    SqlContext.Pipe.Send("CLR XML Sent " + System.DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));
      
              //}
      
              //catch (Exception ex)
      
              //{
      
              //    //SqlContext.Pipe.SendResultsEnd();
      
              //    SqlContext.Pipe.Send("CLR Exception: Output XML Data: " + ex.Message + ": " + ex.ToString());
      
              //    MeasurementData = null;
      
              //}
      
          }
      
       
      
          public static void GeneratePIData(string piServerName, SqlDateTime startDate, SqlDateTime endDate, DataSet tagData, SqlInt32 maxTagsToProcess,
      
              bool includeParameterId, bool includeControllerId, Int32 ArchiveCallType, string PiArgument, SqlBoolean TimeValueOnly, out DataTable tagMeasurementsTable)
      
          {
      
              string controllerId;
      
              string parameterId;
      
              string piTagName;
      
              int tagCount = 0;
      
              //DataSet tagMeasurements = new DataSet();
      
       
      
              if (maxTagsToProcess == SqlInt32.Null)
      
              {
      
                  maxTagsToProcess = 25;
      
              }
      
       
      
              DataTable tagMeasurements = new DataTable();
      
              //define output dataset columns
      
              tagMeasurements.Columns.Add("tag");
      
              //tableDefinition.Columns.Add("Time");
      
              tagMeasurements.Columns.Add("TimeStamp");
      
              tagMeasurements.Columns["TimeStamp"].DataType = System.Type.GetType("System.String");
      
              tagMeasurements.Columns.Add("_index");
      
              tagMeasurements.Columns["_index"].DefaultValue = 1;
      
              tagMeasurements.Columns.Add("value");
      
              tagMeasurements.Columns.Add("status");
      
              tagMeasurements.Columns.Add("questionable");
      
              tagMeasurements.Columns.Add("substituted");
      
              tagMeasurements.Columns.Add("annotated");
      
              tagMeasurements.Columns.Add("annotation");
      
              tagMeasurements.Columns.Add("ParameterID");
      
              tagMeasurements.Columns.Add("ControllerID");
      
       
      
              //tableDefinition.Columns["TimeStamp"].DateTimeMode = DataSetDateTime.Utc;
      
              //tableDefinition.Columns["TimeStamp"].AllowDBNull = true;
      
       
      
       
      
              //tagMeasurements.Tables.Add(tableDefinition);
      
       
      
              //Get archive values for each tag listed in tagData
      
              foreach (DataRow tagRow in tagData.Tables[0].Rows)
      
              {
      
                  try
      
                  {
      
                      //Set CID and PID based on missing data
      
                      if (tagData.Tables[0].Columns.Contains("ControllerID") == false)
      
                      {
      
                          controllerId = "0";
      
                      }
      
                      else
      
                      {
      
                          controllerId = tagRow["ControllerID"].ToString();
      
                      }
      
       
      
                      if (tagData.Tables[0].Columns.Contains("ParameterID") == false)
      
                      {
      
                          parameterId = "0";
      
                      }
      
                      else
      
                      {
      
                          parameterId = tagRow["ParameterID"].ToString();
      
                      }
      
       
      
                      piTagName = piServerName + tagRow["TagName"].ToString();
      
                      GetPiArchive(piTagName, startDate, endDate, Convert.ToInt32(parameterId), Convert.ToInt32(controllerId), ref tagMeasurements,
      
                          (bool)includeParameterId, (bool)includeControllerId, Convert.ToInt32(ArchiveCallType.ToString()), (string)PiArgument);
      
                  }
      
                  catch (Exception ex)
      
                  {
      
                      //could return information about the current tag being processed
      
                      SqlContext.Pipe.Send("CLR Exception: Get Data: " + ex.Message + ": " + ex.ToString());
      
                  }
      
       
      
                  tagCount++;
      
                  if (tagCount >= maxTagsToProcess)
      
                  {
      
                      break;
      
                  }
      
       
      
              }
      
       
      
              //Send aggregated tagMeasurements back to SQL as a table
      
              try
      
              {
      
                  tagMeasurements.Columns["TimeStamp"].ColumnName = "Time";
      
                  //tagMeasurements.Tables[0].Columns["TimeStamp"].ColumnName = "Time";
      
                  if (includeParameterId == false)
      
                  {
      
                      tagMeasurements.Columns.Remove("ParameterID");
      
                  }
      
       
      
                  if (includeControllerId == false)
      
                  {
      
                      tagMeasurements.Columns.Remove("ControllerID");
      
                  }
      
       
      
                  if (TimeValueOnly == true)
      
                  {
      
                      tagMeasurements.Columns["Time"].SetOrdinal(0);
      
                      tagMeasurements.Columns["Value"].SetOrdinal(1);
      
       
      
                      for (int index = tagMeasurements.Columns.Count - 1; index > 1; index--)
      
                      {
      
                          tagMeasurements.Columns.RemoveAt(index);
      
                      }
      
                  }
      
                  //SendDataTableOverPipe(tagMeasurements.Tables[0]);
      
              }
      
              catch (Exception ex)
      
              {
      
                  //error output
      
                  //SqlContext.Pipe.Send("CLR Exception: Send Data: " + ex.Message + ": " + ex.ToString());
      
              }
      
       
      
              tagMeasurementsTable = tagMeasurements;
      
       
      
       
      
          }
      
       
      
          //Delegate for GeneratePIData
      
          delegate void MethodDelegate(string piServerName, SqlDateTime startDate, SqlDateTime endDate, DataSet tagData, SqlInt32 maxTagsToProcess,
      
              bool includeParameterId, bool includeControllerId, Int32 ArchiveCallType, string PiArgument, SqlBoolean TimeValueOnly, out DataTable tagMeasurementsTable);
      
       
      
          public static void GetPiArchive(string piTagName, SqlDateTime startDate, SqlDateTime endDate, SqlInt32 parameterId,
      
              SqlInt32 controllerId, ref DataTable tagMeasurements, bool includeParameterId, bool includeControllerId
      
              , Int32 ArchiveCallType, string PiArgument)
      
          {
      
              //Get Value, Timestamp and Digital State if applicable
      
              ArrayList tagInfoCollection = new ArrayList();
      
              //DataRow tagRow;
      
              PISDK.PISDK SDK = new PISDK.PISDK();
      
              //PISDKCommon.ISlimFast sf;
      
              PIPoint point;
      
              PIValues values;
      
              IPIData2 PiInterpData;
      
              PITimeServer.PITime piStartDate = new PITimeServer.PITime();
      
              PITimeServer.PITime piEndDate = new PITimeServer.PITime();
      
              OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
      
              DataTable dtValuesData = new DataTable();
      
       
      
              //convert datetime values to PITime values
      
              piStartDate.LocalDate = (DateTime)startDate;
      
              piEndDate.LocalDate = (DateTime)endDate;
      
       
      
       
      
              try
      
              {
      
                  //Get point and values
      
                  point = SDK.GetPoint(piTagName);
      
       
      
                  //set defaults
      
       
      
                  dtValuesData.Columns.Add("Tag").DefaultValue = point.Name;
      
                  if (includeControllerId)
      
                  {
      
                      dtValuesData.Columns.Add("ControllerID");//.DefaultValue = controllerId.ToString();
      
                      //tagMeasurements.Columns["ControllerID"].DataType = System.Type.GetType("System.Int32");
      
                      //dtValuesData.Columns["ControllerID"].DefaultValue = (Int32)controllerId;
      
                      dtValuesData.Columns["ControllerID"].DefaultValue = controllerId.ToString();
      
                  }
      
                  //else tagRow["ControllerID"] = null;
      
       
      
                  if (includeParameterId)
      
                  {
      
                      dtValuesData.Columns.Add("ParameterID");//.DefaultValue = parameterId.ToString();
      
                      //tagMeasurements.Columns["ParameterID"].DataType = System.Type.GetType("System.Int32");
      
                      //dtValuesData.Columns["ParameterID"].DefaultValue = (Int32)parameterId;
      
                      dtValuesData.Columns["ParameterID"].DefaultValue = parameterId.ToString();
      
                  }
      
                  //else tagRow["ParameterID"] = null;
      
                  SqlContext.Pipe.Send("CLR Archive Read Start " + System.DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));
      
                  //SqlContext.Pipe.Send("CLR Archive Read Call Type:" + ArchiveCallType.ToString());
      
                  switch (ArchiveCallType)
      
                  {
      
                      case 0:
      
                          values = point.Data.RecordedValues(piStartDate, piEndDate, BoundaryTypeConstants.btInside);
      
                          values.get_RecordSet();
      
                          dataAdapter.Fill(dtValuesData, values.RecordSet);
      
                          break;
      
                      case 1:
      
                          PiInterpData = (PISDK.IPIData2)point.Data;
      
                          values = PiInterpData.InterpolatedValues2(piStartDate, piEndDate, PiArgument);
      
                          values.get_RecordSet();
      
                          dataAdapter.Fill(dtValuesData, values.RecordSet);
      
                          break;
      
                      case 2:
      
                          values = point.Data.PlotValues(piStartDate, piEndDate, Convert.ToInt32(PiArgument));
      
                          values.get_RecordSet();
      
                          dataAdapter.Fill(dtValuesData, values.RecordSet);
      
                          break;
      
                      case 3:
      
                          PiInterpData = (PISDK.IPIData2)point.Data;
      
                          values = PiInterpData.InterpolatedValues2(piStartDate, piEndDate, PiArgument);
      
                          values.get_RecordSet();
      
                          dataAdapter.Fill(dtValuesData, values.RecordSet);
      
                          break;
      
                      default:
      
                          values = point.Data.RecordedValues(piStartDate, piEndDate, BoundaryTypeConstants.btInside);
      
                          values.get_RecordSet();
      
                          dataAdapter.Fill(dtValuesData, values.RecordSet);
      
                          break;
      
                  }
      
                  SqlContext.Pipe.Send("CLR Archive Read End " + System.DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));
      
                  //dtValuesData.Columns["TimeStamp"].DateTimeMode = DataSetDateTime.Utc; //DataType = System.Type.GetType("System.String");
      
                  //tagMeasurements.Tables.Clear();
      
                  tagMeasurements.Merge(dtValuesData, true, MissingSchemaAction.Ignore);
      
                  SqlContext.Pipe.Send("CLR Archive Merge " + System.DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));
      
                  //sf = point as PISDKCommon.ISlimFast;
      
                  //sf.SlimFast();
      
       
      
                  //sf = values as PISDKCommon.ISlimFast;
      
                  //sf.SlimFast();
      
                  //}
      
              }
      
              catch (Exception ex)
      
              {
      
                  //do nothing, return empty collection
      
                  SqlContext.Pipe.Send("CLR Exception: GetArchiveData:  " + ex.Message + ": " + ex.ToString());
      
              }
      
          }
      
       
      
          private static void SendDataTableOverPipe(DataTable tbl)
      
          {
      
              string sTestValue = "";
      
              DateTime updatedTime;
      
              //Indicatestart
      
              SqlContext.Pipe.Send("CLR Send Start " + System.DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));
      
              // Build our record schema
      
              List OutputColumns = new List(tbl.Columns.Count);
      
              foreach (DataColumn col in tbl.Columns)
      
              {
      
                  SqlMetaData OutputColumn = new SqlMetaData(col.ColumnName, ConvertToSqlDbType(col.DataType), col.MaxLength);
      
                  OutputColumns.Add(OutputColumn);
      
              }
      
       
      
              // Build our SqlDataRecord and start the results
      
              SqlDataRecord record = new SqlDataRecord(OutputColumns.ToArray());
      
              SqlContext.Pipe.SendResultsStart(record);
      
       
      
              // Now send all the rows
      
              foreach (DataRow row in tbl.Rows)
      
              {
      
       
      
                  //JAE 2/4/11 added +1 day that got lost in last update
      
                  updatedTime = DateTime.Parse(row["Time"].ToString());
      
                  updatedTime = updatedTime.AddDays(1);
      
                  row["Time"] = updatedTime;
      
       
      
                  sTestValue = row["Value"].ToString();//row[col].ToString(); //
      
                  if (sTestValue != "Missing" & sTestValue != "missing" & sTestValue != "Bad" & sTestValue != "bad" & sTestValue != "Pt Created" & sTestValue != "No Data")
      
                  {
      
                     //record.SetValue(col, row[col]);//row.ItemArray[col]);
      
                      //Format to 2 decimal places
      
                      row["Value"] = String.Format("{0:0.##}",row["Value"].ToString());
      
                      record.SetValues(row.ItemArray);
      
                      SqlContext.Pipe.SendResultsRow(record);
      
                  }
      
                  else if (sTestValue != "Pt Created" & sTestValue != "No Data")
      
                  {
      
                      //record.SetValue(col, "-999");
      
                      row["Value"] = "-999";
      
                      record.SetValues(row.ItemArray);
      
                      SqlContext.Pipe.SendResultsRow(record);
      
                  }
      
                  else //send no data
      
                  {
      
                      //record.SetValue(col, "-999");
      
                      //SqlContext.Pipe.SendResultsRow(record);
      
                      //do nothing - for logic branch included
      
       
      
                  }
      
       
      
       
      
              }
      
       
      
       
      
              // And complete the results
      
              SqlContext.Pipe.SendResultsEnd();
      
              SqlContext.Pipe.Send("CLR Send Complete " + System.DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));
      
          }
      
       
      
          public static SqlDbType ConvertToSqlDbType(Type type)
      
          {
      
              //this should be more complete but covers the needed types for now
      
              switch (type.Name)
      
              {
      
                  case "Int32":
      
       
      
                      return SqlDbType.Int;
      
       
      
                  case "Int64":
      
       
      
                      return SqlDbType.BigInt;
      
       
      
                  case "String":
      
       
      
                      return SqlDbType.NVarChar;
      
       
      
                  case "Boolean":
      
       
      
                      return SqlDbType.Bit;
      
       
      
                  default:
      
       
      
                      return SqlDbType.NVarChar;
      
              }
      
          }
      
      };
      
      
       
      

       

        • Re: URGENT HELP REQUIRED : PI SDK connection to PI Server is failing from SDK based SQL CLR code

          Please check with your network team on whether there were changes made to network configuration late last week or over the week-end. Try to "ping" and "tracert" the PI Server from where the assembly runs. Try with fully-qualified name and IP address. Just a few pointers...

           

          To tell you the truth, connection problems are generally better handled by our regular Tech Support team, not vCampus (which focuses on programming and systems integration issues).

           

          Plus, note that discussion forums are typically meant for, well, discussion ;)  Urgent help should definitely be directed to our regular Tech Support team - they have a mandate to respond within a certain amount of time and they have escalation procedures in case the first representative you talk to cannot help.