Development

PI OleDB seems to hang - on a query that worked fine a few minutes earlier

Discussion created by Development on Aug 23, 2010
Latest reply on Mar 18, 2011 by spilon

Hi

 

We're having an issue with getting sensor data through our PI interface that uses PI OleDb, and a combination of Aliases and named sensors.

 

This is the code that hangs: (Visual Basic)

Try
    Using cn As New OleDbConnection(m_PiConnectionString & "Log File = C:\Log\PIOLEDB.log;")

        If ds.Tables("SENSOR_INFO").Rows.Count = 0 Then

        Dim ak As DataRow
        Dim dWell As Integer
        Dim wellname As String
        Dim field As String
        Dim fromDate As Date
        Dim toDate As Date
        Dim strQuery As String
        Dim dsPointSource As New DataSet()

        If ds.Tables("WELL_PI_ALIAS_INFO").Rows.Count = 0 Then
            Return False
        End If

        strQuery = "SELECT tag FROM pipoint..classic WHERE pointsource = 'M'"
        Using da As New OleDbDataAdapter(strQuery, cn)
            da.Fill(dsPointSource, "POINTSOURCE")
        End Using

It will sit in the last Fill()-call *forever*.

 

This is what we see from the log file:

 

23-Aug-2010 13:04:44.567           PROVIDER_INIT               WIMS.vshost    4960      3             5856      Data Source       IDBInitialize                Initialize               User ID = "wims"; Initial Catalog = "piarchive"; Data Source = "grease"; Window Handle = "0"; Prompt = "4"; Extended Properties = ""; Connect Timeout = "0"; Integrated Security = ""; Command Timeout = "-1"; Log Level = "0"; Log File = "C:\Log\PIOLEDB.log"; Shorten Primary Keys = "0"; Always Return Rowset = "0"; Defer Execution = "0"; Identifier Prefixes = "0"; Disable Server Selection = "0"; Time as Double = "0"; Optimization Log Limit = "100"; Session ID = "1"; Keep Default Ordering = "-1"; Session Pipelines = "1"; Current Time Precision = "0"; Function Errors as NULL = "-1"; Cancel On Low Resources = "-1"; Show Hidden Metadata = "0"; Default Timestep = "1h"; Integers as Value = "0"; Timestamp Interval Start = "0"; Support PI Wildcards = "-1"; Time Zone = "Local"; Connection Type = "PreferPrimary";
23-Aug-2010 13:04:51.166           PROVIDER_INIT               WIMS.vshost    4960      3             5856      Data Source       IDBInitialize                Initialize               User ID = wims; Init Catalog = piarchive; Data Source = grease; Connection Timeout = 0; Command Timeout = -1; Log Level = 0; Log File = C:\Log\PIOLEDB.log; Shorten Primary Keys = False; Always Return Rowset = False; Defer Execution = False; Identifier Prefixes = False; Disable Server Selection = False; Time as Double = False; Optimization Log Limit = 100; Session ID = 1; Session Pipelines = 1; Integrated Security = ; Keep Default Ordering = True; Cancel On Low Resources = True; Evaluation Errors As Warnings = True; Default Timestep = 1h; Integers as Value = False; Timestamp Interval Start = False; Support PI Wildcards = True; Time Zone = Local; Current Time Precision = 0; Show Hidden Metadata = False; Integrated Security =  ;Connection Type = PreferPrimary
23-Aug-2010 13:04:51.166           PROVIDER_INIT               WIMS.vshost    4960      3             5856      Data Source       IDBInitialize                Initialize               OLE DB Provider Version = 3.2.2.10; Server Version = 3.4.375.80; PI SDK Version = 1.3.8.387;
23-Aug-2010 13:04:51.166           SQL_ENGINE_INFO_1   WIMS.vshost    4960      3             5856      SQL Engine                                         Command to prepare: SELECT * FROM pipoint..classic WHERE pointsource = 'M'
---

 

On the PI Server, I can see the following in the Message Logs:

 

---

 

[Server] grease [ID] 0 [Time] 23.08.2010 13:04:56 [Program] Connection Information [Priority] 10 [ProcessID] 0 [Severity] None
Connection ID: 202 ; Process name: WIMS.vshost.exe(4960):remote(4960) ; User: wims ; OS User:  ; IP: 10.1.1.159 ; AppID: 535 ; AppName: PI-OLEDB ; Flag: 0 (disconnecting) ;

[Server] grease [ID] 0 [Time] 23.08.2010 13:04:56 [Program] Connection Statistics [Priority] 10 [ProcessID] 0 [Severity] None
ID: 202; Duration:   2.33 min.; kbytes sent:    264.48; kbytes recv:     53.05; app: PI-OLEDB; user: wims; osuser: ; trust: ; ip address: 10.1.1.159; ip host: 10.1.1.159

[Server] grease [ID] 0 [Time] 23.08.2010 13:04:56 [Program] pinetmgr [Priority] 10 [ProcessID] 0 [Severity] None
Deleting connection:  WIMS.vshost.exe(4960):remote(4960), Shutdown request received from WIMS.vshost.exe(4960):remote(4960) (8, 997), ID: 202 10.1.1.159 10.1.1.159:49884

[Server] grease [ID] 0 [Time] 23.08.2010 13:04:54 [Program] Connection Information [Priority] 10 [ProcessID] 0 [Severity] None
Connection ID: 203 ; Process name: WIMS.vshost.exe(4960):remote ; User: wims ; OS User:  ; IP: 10.1.1.159 ; AppID: 535 ; AppName: PI-OLEDB ; Flag: 1 (connecting) ;

---

 

Can anybody give me any pointers to how we can solve this problem?

 

I searched for other related problems here on Virtual Campus, and found at least one other person with a very similar problem, who was helped by setting "SPTimeout" to a very high value. We have not tried that yet.

 

Any help is greatly appreciated.

 

Regards,

 

Nils A Thommesen,

 

ExproSoft

Outcomes