I am getting error while query PIOLEDB. Event Collection Exceeded The Maximum allowed . snapshot is attached please guide me how to fix this issue.
See below KB article:
KB00347 - Error [-11091] when running a PI OLEDB non pass-through query using a linked server
Normally, you would resolve error "[-11091] Event collection exceeded the maximum allowed" by increasing the ArcMaxCollect parameter on the PI Server. However, the error presented with the linked server is not necessarily the result of a low ArcMaxCollect parameter setting. Instead, it can be the result of an incorrect query plan. The possibility of an incorrect query plan should be investigated before increasing the ArcMaxCollect parameter.
That would really help to see your query, is it possible to post it in this discussion?
SELECT time,value FROM piarchive..picomp2 WHERE tag = '<TAG_NAME>'
When querying for time series data, please always consider adding a time restriction to the WHERE-Clause. Without any time constrained when asking for archived data but as well when asking for calculated or sampled data, archive queries can become very expensive because the PI point may have millions, billions or more events archived. Please see below for some examples on how to restrict the query period.
For the past 1 hour
SELECT * FROM piarchive..picomp2 WHERE tag = 'sinusoid' AND time BETWEEN '*-1h' AND '*'
Between yesterday midnight and today midnight
SELECT * FROM piarchive..picomp2 WHERE tag = 'sinusoid' AND time BETWEEN 'y' AND 't'
Since a specific date and time (until now)
SELECT * FROM piarchive..picomp2 WHERE tag = 'sinusoid' AND time > '10-Apr-2016 10:00 AM'
but what if i need all those archived events in one go is it possible ?? i
This is possible to avoid this error by increasing the parameter ArcMaxCollect. I would however not recommend it.
I would typically avoid to perform unconstrained queries because the data density can be different from one tag to another and this can cause your application to behave in an unpredictable manner:
So, that would be preferable that you chunk the query in several smaller calls ( e.g. get data for 3 months at the time, but you will need to find the sweet spot between performing too many calls and querying too much data). For each call, you should have your program processing the data. Then you repeat for each remaining time period. This technique is also known as paging, this should help your application to be a good citizen in the IT environment because this will limit the amount of RAM necessary to contain the time series data received.
Let me know if you have questions,
I have a similar problem with a SQL linked server. this query with a linked server works:
select * from openquery (TR_PI,'select * FROM picomp2
WHERE tag = ''PTM_LAB_CONSISTANCE_RAFF_P1''
AND time = ''2016-08-10 07:00:00''')
and it returns No data (i don't have any yet)
the same one done directly instead of (openquery )like this:
AND time = '2016-08-10 07:00:00'
gives me the error:
OLE DB provider "PIOLEDB" for linked server "TR_PI" returned message "[PI SDK] [261.296875] Failed to retrieve events from server. [-11091] Event collection exceeded the maximum allowed".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT "Tbl1002"."tag" "Col1005","Tbl1002"."time" "Col1006","Tbl1002"."_index" "Col1007","Tbl1002"."value" "Col1008","Tbl1002"."status" "Col1009","Tbl1002"."questionable" "Col1010","Tbl1002"."substituted" "Col1011","Tbl1002"."annotated" "Col1012","Tbl1002"."annotations" "Col1003" FROM "piarchive"."picomp2" "Tbl1002"" against OLE DB provider "PIOLEDB" for linked server "TR_PI".
Any idea why?
With OPENQUERY you execute your query as path-through which means it will be send as is. Your second query is not executed path-through. SQL Server tries to to optimize your query but it doesn't know anything about PI and ends up executing multiple queries where it should be a single one. I believe SQL Server Profiler is the name of the SQL Tool that can be used to look at the execution plan created by SQL Server.
In your case SQL Server appears to remove the time restriction causing the query becomes executed without time restriction which than causes error [-11091] Event collection exceeded the maximum allowed because the amount of events without time restriction exceeds the maximum of events that can be retrieved with a single call. This amount is set through ArcMaxCollect timeout parameter and a mechanism to protect against expensive queries.
When querying for time series data or summaries, I suggest to always restrict the time period instead of providing an exact timestamp e.g.
WHERE time BETWEEN "10-Aug-2016 07:00:00 AM" AND "10-Aug-2016 07:00:05 AM"
Restricting by a single timestamp is possible but you need to be sure there's an event at that exact time.
In my case, it is related to manual entries, so that is why the time constraint is specify as a single timestamp. what is strange is that we are doing this mechanism at multiple locations and this is the first time it occurs. could it be related to an OLEDB version or a PI server version?
I consider a dependency on the PI OLEDB (Classic) Provider, PI SDK or PI Data Archive version very unlikely but am experienced enough to not state it's not the case.
When you observer different behavior between 2 installations, I suggest to look at the PI OLEDB Logs for both these installations first and compare the queries that become recorded there. If they are different, what I assume will be the case, this would indicate SQL Server handling things differently. If the queries look similar but the calls PI OLEDB sends to the PI Data Archive are different, it could be because of different PI OLEDB Versions.
You could enable debugging by specifying Log File and Log Level within the connection string in your Linked Server connection. For the details, please refer to the PI OLEDB (Classic) Provider manual.
To continue with speculations / theories, I believe it's more likely that different versions or editions of SQL Server create different execution plans and I suggest looking at those first.
very strange but by deleting and recreating the linked server it start to works normally like all our other sites. After doing multiple tests, i found out that if the setting for the linked server Collation Compatible was set to false it was not working. by setting it to True, issue dissappear. After it got recreated it works, because i always set them but this one was not originally.
Good finding Yannick! Thanks for sharing
I must admit that it is not the first time that I forgot about the Linked Server adding to the scope.
Gregor Beck, this is a pass-through query, not a path-through!
yannick galipeau, Glad you found your way, this was indeed a good find. I typically use a stored procedure now to create my linked servers so they are always the same. Maybe Patrick Bergeron forgot to update a stored procedure ?
Once the provider is configured as needed (make sure to look at the documentation), I used the script feature of SQL Server management studio to generate the code and I created variables instead of the hard-coded script-enerated strings.
Here is an example.
-- Type de procedure: CONFIGURATION
-- Description : configures a LINKED server for PI OLEDB Provider Classic
-- Usage Examples:
-- Debug - configues log level 1
-- [dbo].[sp_configure_PIOLEDBLinkedServer] 'PIClassic','PIServer01',0,'C:\Temp\OLEDBClassicLogs.txt', 1
-- PROD - no logging - use trust authentication
-- [dbo].[sp_configure_PIOLEDBLinkedServer] 'PIClassic','PIServer01',0
-- OLEDB Provider test query
WHERE tag = 'sinusoid'
CREATE PROCEDURE [dbo].[sp_configure_PIOLEDBLinkedServer]
@LinkedServerName nvarchar(255) , -- name of linked server to be created
@PIServerName nvarchar(255), -- Name of PI Server
@UseSSPIAuthentication int =1, -- tells if using SSPI
@LogFilePath nvarchar(4000)=null, -- log file full path,
@LogLevel nvarchar(1), -- log level, 0 = basic, 1=detailed
@User nvarchar(255)=null, -- user, leave null for a trusted connection
@Password nvarchar(255)=null -- user password
declare @useselfConfig nvarchar(10)
declare @provider_string NVARCHAR(MAX)
SET @provider_string='Session ID=-1;'
IF NULLIF(@LogFilePath, '') IS NOT NULL
SET @provider_string=@provider_string + 'Log File =' + @LogFilePath + ';Log Level = ' + @LogLevel + ';'
SET @provider_string=@provider_string+'Integrated Security=SSPI;'
-- Deletes the linked server if it exists
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = @LinkedServerName)
print 'dropping existing linked server ' + @LinkedServerName + '...'
EXEC master.dbo.sp_dropserver @server=@linkedServerName, @droplogins='droplogins'
-- OLEDB Classic linked server creation
print 'creating PI OLEDB Provider classic linked server ' + @LinkedServerName + '...'
@server = @linkedServerName,
EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'collation compatible', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'connect timeout', @optvalue=N'90'
EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'query timeout', @optvalue=N'600'
EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@LinkedServerName, @optname=N'remote proc transaction promotion', @optvalue=N'true'
print('Configures futher SQL Server options over the PIOLEDB Provider: AllowInProcess,DynamicParameters,NestedQueries,SqlServerLIKE')
EXEC sys.sp_MSset_oledb_prop N'PIOLEDB', 'AllowInProcess', 1
EXEC sys.sp_MSset_oledb_prop N'PIOLEDB', 'DynamicParameters', 1
EXEC sys.sp_MSset_oledb_prop N'PIOLEDB', 'NestedQueries', 1
EXEC sys.sp_MSset_oledb_prop N'PIOLEDB', N'SqlServerLIKE', 1
Patrice Thivierge wrote: Gregor Beck, this is a pass-through query, not a path-through!
Patrice Thivierge wrote:
Looks like you recharged your humor batteries Please be prepared that I'll be hunting for your typos from now on
Retrieving data ...