How to create a 'public' view which can be used by ODBC connection?

[henky]

Hello,

I am trying to import data directly out of Progress 9.1E into SQL Server 2005. For many tables this is no problem, I created a Linked server and am able to connect the tables and can use T-SQL statements to select records out of the tables.

SQL Server 2005 has a big F***-up in their 'valid' datetime range. Only dates between 1735-01-01 and 9999-12-31 are valid.

Progress accepts all dates ofcourse and you can fill in a date when Columbus discovered America. (This is not possible to fill in a SQL Server 2005 datetime field :s).

My problem here is, I got records with 'invalid-SQL-Server-datetime' dates and still want to read that data.

Its not possible to do a cast/convert in SQL Server, becaus before the cast will be done, SQL Server will see the invalid date and stop the select action.

I was thinking to create a View in Progress and make it public, so I can access it with SQL Server using the Linked server and gather the information like this way.
In the view I will update the datetime field with a 'valid' date. (or I do a cast in the view already to make it char)

My question is how can I create a public view so I can access it with SQL Server 2005 as view/table of the linked server.

I know I can type:

CREATE VIEW myview AS
SELECT name, country, invalid_date_field from ancient_ppl)

but I cant find that view back using ODBC.

Can anyone help me with this?

Henky

 

[henky]

Hello,

Can someone help me with this? Please let me know if the question is in the wrong section.

Henky

 

[Casper]

If I'm not mistaken you can do something like:

create view PUB.myview (...) as (......)....

This should make PUB the owner of the view.

HTH,
Casper.

 

[FrancoisL]

Create your view with the SQL Explorer tools. It will be a SQL view and will be seen in the SQL schema and ODBC.

 

[henky]

Hi FrancoisL,

Thanks for your advice. I just figured that out and was logging in here to tell ppl (if they search for a slolution) how to solve the problem.

Its true, you have to use SQL explorer and use something like:

CREATE VIEW pub1.myview AS (SELECT field1, field2 FROM pub.app);
commit;

Thanks to everyone who replyed and the ppl who've thought about how to solve this problem

Henky