Are there any good walkthroughs of exposing PI as a Linked Server? (Too lazy & busy to have a look )
Particularly interested on example queries combining data from PI Linked Server and the host SQL Server.
don't know if that counts, but http://vcampus.osisoft.com/blogs/andreas/archive/2009/01/14/manual-input-in-microsoft-sharepoint.aspx shows how to move data from the host SQL server to the linked server.
For how to create a linked server, you may refer to the blog of cristobal: http://vcampus.osisoft.com/blogs/cristobal/archive/2008/12/02/row-numbering-pi-data-with-pioledb-and-mssql-server.aspx
Thanks Andreas...perfect to get me going. Got one up and running in minutes, now onto the complex queries
The suggestions made by Andreas a great practical examples (as a matter of fact, it looks like they helped RJK out! )
Now, for the benefit of those who just began with SQL Server and the PI OLEDB Provider, please allow me to answer the original question with a more theoretical answer.
To create the Linked Server:
(click to view full size)
(click to view full size)
(click to view full size)
Important Note: Setting the the Collation Compatible parameter to True has a very significant (and positive) impact on the performance of the linked server. Also, setting an appropriate non-zero value for the Query Timeout helps preventing an eventual "application hang".
This takes care of creating the linked server itself.
There are a few parameters we recommend to set at the "provider level" - these affect any Linked Server using PI OLEDB as its provider.
Simply navigate Server Objects > Linked Servers > Providers, right-click on PIOLEDB and select Properties. Check the following parameters:
Make sure the Disallow adhoc accesses is not checked; if set, SQL Server does not allow to execute 'pass-through' queries against the OLEDB Provider.
Hope this helps!
Steve, nice one. Indeed the "Collation Compatible" parameter does have a positive impact as I have found out...thanks.
Dumb question time, presumably now that PI OLEDB is a linked provider, can I use provider specific functions? In this case PI time functions, Digital functions (DIGString)?
Yes, there are ways for you to use provider-specific functions (such as PI Time and Digital State functions), and even use the PI Time formats (dd-mmm-yy HH:mm:ss or relative time)
Easiest way in SQL Server is to use 'pass-through' queries. For example:
SELECT Tag, Descriptor FROM LinkedServer.PIPoint..Classic WHERE Tag LIKE 'ba%'
SELECT * FROM OpenQuery(LinkedServer, 'SELECT Tag, Descriptor FROM PIPoint..Classic WHERE Point LIKE ''ba%''')/* or WHERE Point LIKE ''ba*''')*/
2 important notes for pass-through queries:
Another way to use PI specific functions is to create them as "Views", using the PI Function Catalog Manager (located in \PIPC\OLEDB\Tools\pifunction Catalog Manager):
(click to view full size)
Steve, once again thanks. Just what I needed...this type of information is why vCampus is extremely valuable!!
(EDIT: Steve typo in your query, you have "Point" in the where clause rather than tag)
Glad to read this, RJK!(Thanks for pointing out the typo - I corrected it in my original post)
Good one Steve! :)
Steve, thought I would pop back in on this thread as I have now in a matter of days implemented a very powerful addition to an existing system (non-PI) using the PI-OLEDB provider. As soon as AF can be queried via OLEDB it will be even better! If I understand what I am hearing correctly, this will come in the form of the PI system OLEDB provider..?
In fact this will come in the form of the PI OLEDB Enterprise. The first release of PI OLEDB Enterprise will provide read-only and high performance access to AF databases, and will coexist with the PI OLEDB Provider (i.e. not a replacement for the PI OLEDB Provider). According to our public engineering plan, should be released Q4'09.
Hi guys. Looking at the last image you posted Steve - I really can't access the properties of the PIOLEDB provider.
If you look at my screenshot, I don't have the 'Properties' menu item when right-clicking the providers (same for all available providers).
Any idea why? Can I set the mentioned parameters (dynamic parameters, nested queries, allow inprocess) in the provider string textbox in my linked pi-server properties? (I've already set the option "allways return rowset=true" there)
Asle, you select the Properties of the LinkedServer instance, not on the list of available providers.
So if "SVGPIGAS" is a linked server using the PIOLEDB provider, right click on SVGPIGAS and select Properties.
any chance you are running SQLEXPRESS?
I have Microsoft SQL Server Express Edition and Microsoft SQL Server Developer Edition running on my system and can see that the Properties is only available if I connect to the Microsoft SQL Server Developer Edition but does not appear on the Microsoft SQL Server Express Edition.
Yes, this is most likely the case. I suspected the Express-install could be the problem here.
Other thing I asked about, was if I could set these provider options in the provider-string textbox (in the properties of the linked server). Any experience on that?
searching in our internal Wiki I found the following solution:
Changing the Provider Options from the user interface feature is only accessible for the users of the Standard, Enterprise or Data Center Edition. By default a new Linked Server created against the PI OLEDB driver will have the AllowInProcess, NestedQueries and DynamicParameters options not set.
A stored procedure is available to perform the changes on the provider. Using SQL Server Manager Studio Express, open a query window and set the following commands:
EXEC sys.sp_MSset_oledb_prop 'PIOLEDB', 'AllowInProcess', 1EXEC sys.sp_MSset_oledb_prop 'PIOLEDB', 'DynamicParameters', 1EXEC sys.sp_MSset_oledb_prop 'PIOLEDB', 'NestedQueries', 1
Then execute the 3 commands.
Thanks guys - I'm sure this thread will be useful for others as well
Works fine with my SQLExpress...
Yes, THAT works fine. But if you see the last image in Steve Pilon's large post, you can go into the Providers folder just above your selected linked server, and access the properties of the provider itself.
I need a way to set these provider options without the GUI - which probably isn't present due to the use of SQLEXPRESS
I think I found the correct way to set these options without access to the GUI for the provider properties:
EXEC master.sys.sp_MSset_oledb_prop N'PIOLEDB', N'AllowInProcess', 1
I'm not able to verify it yet, because I'm dealing with another error with my linked server with techsupport. I'll let you know as soon as I can test it out.
Sorry I've been late getting back into the game for this one... Asle Frantzen has the correct solution: you need to set Provider options using the SP_MSSET_OLEDB_PROP stored procedure. The Express edition of SQL Server does not provide a graphical user interface (GUI) for that. The last (optional) argument of the SP_ADDLINKEDSERVER stored procedure (used to create a Linked Server "by code"), can also be used to set Provider options.
I probably should have outlined that in my original post... sorry about that!
i just downloaded the new oledb 4 which combine the AF and pi archive oledb access. do you have a sample on how to configure a linked server withing sql 2005 for the AF data?
also should i be able to configure another linked server for the historian data as in version 3.3 or there is new settings to put in order for it to work?
Configure like a normal linked server, select PI system OLEDB Provider, set the product name to PISYSOLEDB and DataSource to the name of your AFServer.There are lots of other things to set (security etc) but this will get you going.
As Rhys pointed out, you'll be able to create another linked server that's making use of the PI OLEDB Provider for AF (listed as "PI System OLEDB Provider" in the list of available providers).
For the benefit or all our readers, I would like to clarify a little something on your statement about "PI OLEDB 4 combining AF and PI access via OLEDB", as this may be a common misconception: PI OLEDB 4 indeed is a package, but purely from an "install kit" standpoint. Essentially, it provides 2 distinct OLEDB Providers: one for PI (PI Points, PI Archive and other PI databases) and one for AF. In other words, you have to configure a separate linked server to access to AF (i.e. cannot do both PI and AF access through the same linked server).
Important to note: don't forget to set the Collation Compatible setting to True in the Server Options section.
Just like the standard PI OLEDB provider, the "PI OLEDB for AF" provider has a number of provider-specific options - you can read more on those in the "Getting Started" that's provided with the Provider.
in sql, when configuring the linked server, should is see 2 oledb provider: one for accesssing Af and one for the archive?
after installing the beta package i have only pi system oledb provider in my list.
if they are part of the same provider what will make one linkded server point to af and the other linkded server to the archive?
Yes you will see 2 distinct OLEDB Providers, if both are installed on your machine. However, in the Beta kit that's available now, only the one for AF is provided (v0.9.0.1) and therefore you need to install the regular PI OLEDB Provider (v22.214.171.124) separately. The Release kit shall contain both.
i understand now. it was strange for me that only one provider could be use for both. now is see!
now i have this error
Cannot initialize the data source object of OLE DB provider "PISysOLEDB" for linked server "AF".OLE DB provider "PISysOLEDB" for linked server "AF" returned message "PI System 'itwks14\sql2005' is not registered.". (Microsoft SQL Server, Error: 7303)
That simply indicates the AF Server you entered (itwks14\sql2005) could not be found as a valid, registered AF Server on this machine. Typically, one would use just the hostname or the IP address of the AF Server; not the hostname + SQL Server instance, if that's what "itwks14\sql2005" is.
Also, you need to make sure the AF Server is "known" to the machine and to the AF SDK (just like you would add a PI Server to the Known Servers Table in AboutPI-SDK.exe or in any application's File > Connections). To do so, open the PI System Explorer, click on File > Database..., click the ellipsis (...) button beside the PI System drop-down list, right-click in the white area and select "New PI System" to add your AF Server to the list.
I have the express SQL Server edition and I can see the properties... what version of express are you using?
I have SQL Server Express 2008 with Tools. you may get it to work with 2005 Express if you do install the SQL Server Management Studio Basic 2008.
my 2 cents :)
I followed your nice tutorial. Unfortunately I end up with this error:
TITLE: Microsoft SQL Server Management Studio------------------------------The test connection to the linked server failed.------------------------------ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------Cannot create an instance of OLE DB provider "PIOLEDB" for linked server "PI". (Microsoft SQL Server, Error: 7302)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=7302&LinkId=20476------------------------------BUTTONS:OK------------------------------
I am on Windows Vista Home Premium with SQL Server 2008 Express 64 bit. The PI Server is on another machine. I can connect with the Management Console Snap in. Whether the Windows Firewall is on or off does not matter. If I set the in-process bit, the error changes:
OLE DB provider 'PIOLEDB' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308)
Do you have an idea what could cause this?
Jan van der Ven
I should have searched on 64-bit. I expected the 64-bit to be the cause, but went on the campus and posted without looking for it. Thanks to the tag cloud I finally found it.
I downloaded the 64-bit version of PIOLEDB, uninstalled PI OLEDB and the PI SDK, installed the 64-bit versions.
Then I got a more promising error message. I went into the properties of the PI OLEDB provider, and activated Allow in process. Restarted the SQL server instance and executed my first query. With results and without errors.
Super, glad you got it all sorted out!
Jan van der VenThanks to the tag cloud I finally found it.
Speaking of tag cloud, I'll add a few tags to your latest posts
I get this same Error: 7308. My installation of SQL is:
Microsoft SQL Server Management Studio Basic 10.50.1352.12 (2008 R2)
Microsoft Data Access Components (MDAC) 6.0.6001.18000
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.6001.18000
Microsoft .NET Framework 2.0.50727.3615
Operating System 6.0.6001
I'm using the most recent version of PI Enterprise API, SDK, etc. SDK is 1.3.8 build 387, on Win Vista 64.
One interesting this perhaps to note is the PI connection manager refers to my OS as NT x86 5.2.3790. Does this mean that the install thought I was on a 32 bit OS?
Thanks in advance for your help.
Please let me invite your to contact the OSIsoft techsupport(techsupport.osisoft.com/.../Contact+Methods.htm)
on this. They are much better in troubleshooting installation issues.
Thanks for the procedure in screenshots... The last part about PI OLEDB provider properties are really important. If anyone is running across a connection error when setting up the linked server, check those properties and try again.
Configuring SQL Linked Server for the PI OLEDB driver using Microsoft SQL 2005 Express Edition needs to set Provider Options as recommended in the PI OLEDB documentation but the SQL Server Management Studio Express does not offer the feature to change it from the user interface. Changing the Provider Options from the user interface feature is only accessible for the users of the Standard, Enterprise or DataCenter Edition. By default a new Linked Server created against the PI OLEDB driver will have the AllowInProcess, NestedQueries and DynamicParameters options not set.
Therefore, a stored procedure is available to perform the changes on the provider. Using SQL Server Manager Studio Express, open a query window and set the following commands:
Then execute the 3 commands. It is sometimes needed to restart the SQL Server instance.
I have several doubt while configuring pi collective with link server .
1.) What is the Data source name while configuring the link server? I tried to configure with collective name but i am getting following error
I am using SQL server 2008 r2 express edition
2.) Does it work with pi collective?
3.) Could Connection type is editable in PI OLEDB?
Retrieving data ...