schugh@nalco.com

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

Discussion created by schugh@nalco.com on Aug 30, 2011
Latest reply on Aug 30, 2011 by spilon

 

 

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;

        }

    }

};


 

 

Outcomes