5 Replies Latest reply on Aug 14, 2015 11:06 PM by bshang

    PIOLEDBENT Linked server SQL errors

    mavd

      Hi

       

      Context: On a SQL Server 2008 R there is a linked server pointing to AF (2.4.0.4431) using the PIOLEDBENT provider (Options: Dynamic parameter, Nested Queries, Supports 'Like' operator)

       

      On this SQL Server there are several SQL jobs scheduled to run at each 2 or 10 minutes calling stored procedures (from different databases) that are using the linked server.

       

      Once in a while (sometimes more than once a day) those jobs are failing for different reasons but it is always related to the linked server.

       

      Here is a list of the different errors that happen:

       

      - Cannot get properties from OLE DB provider "PIOLEDBENT" for linked server "MESAF". [SQLSTATE 42000] (Error 7372).  The step failed.

      - Could not execute statement on remote server 'MESAF'. [SQLSTATE 42000] (Error 7215).  The step failed.

      - Cannot initialize the data source object of OLE DB provider "PIOLEDBENT" for linked server "MESAF". [SQLSTATE 42000] (Error 7303).  The step failed.

      - The attempt by the provider to pass remote stored procedure parameters to remote server 'MESAF'  failed. Verify that the number of parameters, the order, and the values passed are correct. [SQLSTATE 42000] (Error 7213).  The step failed.

      - The OLE DB provider "PIOLEDBENT" for linked server "MESAF" reported an error. The provider reported an unexpected catastrophic failure. [SQLSTATE 42000] (Error 7399)  Cannot initialize the data source object of OLE DB provider "PIOLEDBENT" for linked server "MESAF". [SQLSTATE 42000] (Error 7303).  The step failed.

       

      When one of these errors occurs, nothing is done to fix the problem. The jobs were running correctly before the error occurred and will continue working on the next execution until one of these errors occurs again.

       

      Has anyone seen these errors before or knows how to fix them?

       

      Thanks,

      Veronique

        • Re: PIOLEDBENT Linked server SQL errors
          bshang

          Hi Veronique, we'll need some further information to try to understand why the issue is occurring, Is SQL Server 32-bit or 64-bit? If 32-bit, what is the memory usage and does it seem to grow or spike up at certain times? What is the version of PI OLEDB Enterprise (check in Programs and Features)? What other scheduled jobs are executed against this SQL server? Is PI OLEDB Enterprise provider running inprocess or out of process (check in Linked Servers>Providers>PIOLEDBENT>Properties)? Could you also provide some sample queries for us to check if they are being performed in an efficient manner?

           

          Do you also see any errors in Event Viewer when these issues occur?

            • Re: PIOLEDBENT Linked server SQL errors
              mavd

              Hi Barry

               

              Here is more information. SQL Server is 64 bits, the version of PI OLEDB Enterprise is 2010 R3 64 bits (1.2.4.7) There are 37 jobs running, some of them fetch PI data using a PI and/or an AF linked server, some of them fetch data in other system and the rest are maintenance jobs (data and database). It is a production server. The only enabled options for the PIOEDBENT provider are (Options: Dynamic parameter, Nested Queries, Supports 'Like' operator) so it is running out of process.

               

              Also here is a new error message, the first error is the same as in the previous message but the second error is new.

               

              - The OLE DB provider "PIOLEDBENT" for linked server "MESAF" reported an error. Provider caused a server fault in an external process. [SQLSTATE 42000] (Error 7399)  Cannot create a statement object using OLE DB provider "PIOLEDBENT" for linked server "MESAF". [SQLSTATE 42000] (Error 7305).  The step failed.

               

              The query that is failing the most is:

               

               

              Declare @OpenQuery  VARCHAR(Max) =
              'SELECT   eh.path + eh.Name + ea.Path + ea.Name AS Attribute, r.time, r.valueInt
              FROM ' + @AFDatabase + '.Asset.ElementHierarchy eh   
              INNER JOIN ' + @AFDatabase + '.Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID    
              INNER JOIN ' + @AFDatabase + '.Data.Archive  r ON r.ElementAttributeID = ea.ID
              WHERE   eh.path = ? AND eh.Name = ? AND ea.Path = ? AND ea.Name = ?
              AND r.time > ? AND r.time <  ?
              ORDER BY r.time

              OPTION (FORCE ORDER, ALLOW EXPENSIVE, IGNORE ERRORS)'

               

              INSERT INTO @stoppages (attribute,processDataPointDate,processDataPointValue)
              EXEC(@OpenQuery,@ElementHierarchyPath, @ElementHierarchyName, @ElementAttributePath, @ElementAttributeName, @lastStoppageImportDateString, @nextStoppageImportDateString) AT [MESAF]

               

              and one other example

               

              DECLARE @Query VARCHAR(MAX) =
              'SELECT NULL, NULL, NULL, ea.Name, NULL,
              CASE WHEN ConfigString LIKE ''\\%''
              THEN SUBSTR(ea.ConfigString, INSTR(ea.ConfigString,''?'', 1,1)+38, CASE WHEN INSTR(ea.ConfigString,''?'', 1,2) = 0
              THEN (INSTR(ea.ConfigString,''?'', 1,1)+38)
              ELSE INSTR(ea.ConfigString,''?'', 1,2) - (INSTR(ea.ConfigString,''?'', 1,1)+38)
              END)
              END AS Tag
              FROM ' + @AFDatabase + '.Asset.Element e
              JOIN ' + @AFDatabase + '.Asset.ElementAttribute ea ON ea.ElementID = e.ID
              WHERE ea.ElementID =

              (SELECT e.ID FROM ' + @AFDatabase + '.Asset.Element e WHERE e.ElementTemplateID =

              (SELECT et.ID FROM ' + @AFDatabase + '.asset.ElementTemplate et WHERE et.Name = ''MES_Transaction_BAKEF_FireMove'')
              AND SUBSTR(e.Name,LEN(e.Name)-6,1) = ''' + CONVERT(VARCHAR,@FurnaceNo) + '''
              AND SUBSTR(e.Name,LEN(e.Name),1) = ''' + CONVERT(VARCHAR,@FireNo) + ''')
              OPTION (ALLOW EXPENSIVE, IGNORE ERRORS)'

               

              INSERT INTO @Tags
              EXEC(@Query) AT [MESAF]

               

              In the event viewer (windows Logs\Application) I was able to see that around the time of the PIOLEDBENT error, there is an Application Error (DLLHost.exe) but this application seems to be in error at other time and the SQL queries are not failing.

               

              Thanks,

              Veronique

               

                • Re: PIOLEDBENT Linked server SQL errors
                  bshang

                  Thanks for the information. Can you share the DLLHost error in the Event Viewer Logs? Did you see any out of memory errors?

                   

                  Please be aware of some limitations of running out of process.

                  KB00374 - Common Issues with PI OLEDB Provider configured as Out-Of-Process Linked Server in SQL Server

                   

                  Issue 4 above suggests you may try running the provider in process if possible.

                   

                  Also, is both the AF server and client versions 2.4.0.4431? The versions are quite old, along with OLEDB Enterprise. If it's possible to upgrade, we would recommend that as an option.

                   

                  Prior cases indicated these errors may be related to out of process configuration, but to determine the root cause and optimal workaround, we may need more logs and hangdumps, which can be best achieved through opening a Technical Support case.

                    • Re: PIOLEDBENT Linked server SQL errors
                      mavd

                      Hi Barry

                       

                      It was running in process a few weeks/months ago but it was changed to out of process because it seems that it was causing the cluster to crash. Probably because of error number 5 (Error 7399).

                       

                      Since my last reply, here are some new error messages.

                      - The OLE DB provider "PIOLEDBENT" for linked server "MESAF" reported an error. The provider ran out of memory. [SQLSTATE 42000] (Error 7399)  Cannot create a statement object using OLE DB provider "PIOLEDBENT" for linked server "MESAF". [SQLSTATE 42000] (Error 7305).  The step failed.

                      - One or more properties could not be set on the query for OLE DB provider "PIOLEDBENT" for linked server "MESAF". Unknown provider error. [SQLSTATE 42000] (Error 7370).  The step failed.

                      - Could not execute statement on remote server 'MESAF'. [SQLSTATE 42000] (Error 7215)  OLE DB provider "PIOLEDBENT" for linked server "MESAF" returned message "Internal AF Server failure. Make sure AF Server uses the latest PI SQL (AF) Service version.  Details: [PI SQL (AF) Service] Timeout has expired.". [SQLSTATE 01000] (Error 7412).  The step failed.

                       

                      But the one that really occurs the most is Error 7215, probably 80% of the time.

                       

                      I found no error in the event viewer when error 7412 occurred.

                       

                      Here is the error of the event viewer when error 7370 occurred.

                       

                       

                      Faulting application name: DllHost.exe, version: 6.1.7600.16385, time stamp: 0x4a5bca54

                       

                      Faulting module name: ntdll.dll, version: 6.1.7601.18247, time stamp: 0x521eaf24

                       

                      Exception code: 0xc0000374

                       

                      Fault offset: 0x00000000000c4102

                       

                      Faulting process id: 0x9a8

                       

                      Faulting application start time: 0x01d0d13815c82066

                       

                      Faulting application path: C:\Windows\system32\DllHost.exe

                       

                      Faulting module path: C:\Windows\SYSTEM32\ntdll.dll

                       

                      Report Id: 544d4a32-3d2b-11e5-b6d1-005056ab438d

                       

                      I'm having problems with the AF linked server (PIOLEDBENT service) but it seems that in the event viewer there is also some problems with the PIOLEDB service. This is the error that occurs the most among all errors in the event log.

                       

                       

                      Faulting application name: DllHost.exe, version: 6.1.7600.16385, time stamp: 0x4a5bca54

                       

                      Faulting module name: PIOLEDB64.dll, version: 3.3.1.2, time stamp: 0x4efafb11

                       

                      Exception code: 0xc0000005

                       

                      Fault offset: 0x0000000000013ee6

                       

                      Faulting process id: 0x3f3c

                       

                      Faulting application start time: 0x01d0d409d6763d87

                       

                      Faulting application path: C:\Windows\system32\DllHost.exe

                       

                      Faulting module path: D:\Program Files\PIPC\OLEDB\PIOLEDB64.dll

                       

                      Report Id: 17c7e4f0-3ffd-11e5-b6d1-005056ab438d

                       

                      I know the version is old. It was supposed to be updated in April but it is still not done yet.

                      Explorer, AF SDK and AF version is 2.4.0.4431

                       

                      Thanks,

                      Veronique

                        • Re: PIOLEDBENT Linked server SQL errors
                          bshang

                          I think the error "The provider ran out of memory" might a good clue. I would make sure that the SQL queries are not returning unnecessary rows and/or columns. I'd also monitor the memory usage by PI OLEDB Enterprise and SQL Server. Regarding 7215, I would check if there are any large linked tables or inefficient queries associated with them are being used and if so, independently test out the queries against them to check the performance. You may be able to get better insight via a TechSupport case and they will walk you through the steps of gathering crash dumps which would better reveal why the process crashed.