Using Progress with SQL Server...and pesky [Square Brackets]

[doom1701]

I'm thinking this has been asked before, but I couldn't find anything. I'm trying to do more with SQL SSIS (Integration Services) and Reporting Services and our ERP system's OE10.1b database.

In order to expose datasets to the client side report builder tool for Reporting Services, as well as some tasks in SSIS, you have to create a Data Source View. It's easy enough--just drag and drop the fields you want to see. But Visual Studio adds [square brackets] around each table and field name...and P98157 says that syntax isn't standard. When you try to finish the data source view, you get a SQL Syntax Error 10713 because of the brackets.

I'm not sure which route to pursue here--the options I see are either getting the OE ODBC driver to like the brackets, or getting Visual Studio to not put them in. Which immovable object should I try to lean on...

 

[tamhas]

To query the OE database with the Progress SQL drivers you will have to switch from brackets to quote marks. Quotes are not required if there are no illegal characters, like dash, but they are permitted everywhere. I.e., with

"PUB"."Customer"."First-Name"

they are required only for First-Name, but are permitted in all cases as shown. I have never seen any indication that there was any other possibility, so I think you are either going to have to bash Visual Studio into excepting this or post-process anything you generate.

 

[doom1701]

It's even worse--I'm going to have to understand how to write the underlying XML for a Data Source View. Visual Studio creates it with brackets, and it won't save it (or let you see the underlying XML, from what I can tell) until it verifies the code. Since it can't verify, it can't save, so I can't hack it after the fact.

 

[tamhas]

Are you approaching this from the wrong end? Do you have AppServer? Why not just write the ABL code and create the proxies?

 

[4GLNewbie]

It seems like you really need something that stay between progress and sql and makes conversions from one to another language..
But since i have begun to use progress, i do not have seen anything like that.

 

[NZimmer911]

Hey doom, were you ever able to figure this problem out? I'm running into exactly the same issue and apart from using an intermediate access db containing the progress tables I'm interested in I can't figure out how to get past it.