Using PRO_ELEMENT in a SQL Server linked server

pxbaker

New Member
Hi,

I'm a first timer on progress and need some help getting array data into SQL Server 2005.
I've got it all connected up as a linked server but need to know the syntax to get the PRO_ELEMENT call out to the Progress DB.

I've tried :

SELECT
{ fn PRO_ELEMENT ( "FLD",1,1) } AS col_val
from ls_prg_uat1..pub.uddetail

This just gives an unknown function error.

Any help would be appreciated.

Pat
 

Casper

ProgressTalk.com Moderator
Staff member
Does
Code:
SELECT PRO_ELEMENT(FLD,1,1)  as col_val from  ls_prg_uat1.pub.uddetail
work?

Casper
 

pxbaker

New Member
It works on an ODBC connection ( without the 4 part table prefix ), but when used in SQL SERVER I get error..
Msg 195, Level 15, State 10, Line 2
'PRO_ELEMENT' is not a recognized built-in function name.
 

pxbaker

New Member
The linked server setup is fine.

The only problem is trying the Progress Arrays decoded into a SQL Server select.

I have select only access onto the Progress DB so I cant change anything on that end, it all has to be done through the linked server.

It a strange twist of fate, SSIS ( DTS 2005 ) can pull the data using PRO_ELEMENT, but we want real time access.
 

Casper

ProgressTalk.com Moderator
Staff member
It a strange twist of fate, SSIS ( DTS 2005 ) can pull the data using PRO_ELEMENT, but we want real time access

Indeed it is.....

It seems that OpenEdge DataServer for MS SQL Svr understands this by using specially named collumns at the SQL server site:

The OpenEdge database allows you to define fields as arrays, also called field extents. The DataServer interprets specially named data source columns of the same data type as an OpenEdge field with the The OpenEdge database allows you to define fields as arrays, also called field extents. The DataServer interprets specially named data source columns of the same data type as an OpenEdge field with the same number of array elements. You name the data source columns column_name##1, column_name##2, and so forth, to correspond to an OpenEdge array named colunm_name. The DataServer creates a single field definition in the schema holder for the field extentssame number of array elements. You name the data source columns column_name##1, column_name##2, and so forth, to correspond to an OpenEdge array named colunm_name. The DataServer creates a single field definition in the schema holder for the field extents

But again, this also is the other way around :)

Maybe it helps to also ask your question at: sql92@peg.com.

Regards,
Casper.
 
Top