Oracle Table Adapter Querry Wizard Error

[Ryan M. Hager]

I am trying to generate a new DataTable in Visual Studio 2005 using the .Net
provided Oracle Client.

Here is the querry I am trying to use:

SELECT A.OWNER, A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE, A.TABLE_NAME,
A.INDEX_NAME, B.TABLE_OWNER, B.COLUMN_NAME
FROM SYS.DBA_CONSTRAINTS A INNER JOIN SYS.DBA_IND_COLUMNS B ON
A.INDEX_NAME = B.INDEX_NAME
WHERE (A.CONSTRAINT_TYPE = 'P') AND (A.OWNER = 'RYAN')

Everything works fine in the Query Builder, however, when I choose finish in
the Table Adapter Configuration Wizzard I get the following error:

Syntax Error: Expecting ',' or 'WHERE' (etc.) after table alias.

Everything looks right. How do I fix this?

Ryan M. Hager

 

[Kevin Yu [MSFT]]

Hi Ryan,

Thanks for reporting this. Based on my research, this is a known issue in
the TableAdapter wizard. Unfortunately, this issue has something to do with
the oracle SQL parser. So, the development team has to work on it with
Oracle to have them parse the query and give us this schema information.
There is no workaround till now. I'm sorry for the inconvenience.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Ryan M. Hager]

Kevin,

1. Please add me to the list of Customers wanting this feature.

2. Is there anything I can do to expedite the development of this patch?

Sincerly,

Ryan M. Hager.

 

[Kevin Yu [MSFT]]

Hi Ryan,

Actually, the dev team is working on it with Oracle to make it work
properly. Currently, I don't know any workaround on this issue. I suggest
you try to contact Microsoft PSS for some help on it, tell them this is a
known issue. You can get the contact information from the following link:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[anony mouse]

I had the same problem. The parser doesn't like the INNER JOIN syntax. Rewrite the query to remove the "INNER JOIN" and use the "classic" approach of putting the ON in the WHERE clause

eg

SELECT
A.OWNER,
A.CONSTRAINT_NAME,
A.CONSTRAINT_TYPE,
A.TABLE_NAME,
A.INDEX_NAME,
B.TABLE_OWNER,
B.COLUMN_NAME
FROM
SYS.DBA_CONSTRAINTS A,
SYS.DBA_IND_COLUMNS B
WHERE
A.INDEX_NAME = B.INDEX_NAME
AND
(A.CONSTRAINT_TYPE = 'P')
AND
(A.OWNER = 'RYAN')

Don't use Query Designer or it will put the inner joins back in.

 

[Guest]

Kevin,

Any Update?

Ryan M. Hager

 

[Kevin Yu [MSFT]]

Hi Ryan,

There isn't any update to this issue. If this issue is urgent for you, I
suggest you contact Microsoft PSS directly.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Ryan M. Hager]

All,

An Update. Unfortunately, due to the complexity of this issue, it will not
be updated until the next version of Visual Studio. The workaround is to
use old style join syntax.

Ryan M. Hager

 

[Kevin Yu [MSFT]]

I'm sorry about that.

Kevin Yu
Microsoft Online Community Support

============================================================================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
============================================================================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)