External Definition from MS SQL Server View

[Former Member]

Hi all,

I'm trying to create an external definition with category dbtab. The database i'd like to access is a Microsoft SQL Server 2005. After I provided the receiver communication channel and click on display, all tables are shown in the list. But there are some views I need to use in the jdbc lookup, which aren't shown. Is this in any way limited to real physical tables or is there a way to access views as well.

I'm running on SAP PI 7.11.

Best Regards

Sven

[Former Member · Accepted solution]

Hi Sven,

You can operate with views as well using JDBC adapter.

You can just give the view name in place of a table name and access them both alike.

Regards

Jai

[Former Member · Accepted solution]

Hi,

i know that i normally can use views as well as tables, but in the new feature in PI 7.11 where I can supply a jdbc receiver channel to access a database and extract the external definition directly from the database, this doesn't work. Even if I type the view name, I only get a "table not found" error.

Best Regards

Sven

[rajasekhar_reddy14 · Accepted solution]

it will not work with views as per my knowledge.

[Former Member · Accepted solution]

Hi all,

does anybody have a hard piece of evidence (some statement from SAP, documentation)? Please point me to it.

Why won't this feature work with Views, they can basically be treated in the same way as tables?

Best Regards

Sven

[Former Member]

After tests against SQL Server and Oracle databases, I'm afraid, the external definitions are really not able to cope with views - for whatever reason. So I'm closing this thread.

Best Regards

Sven

[Former Member]

It's an very old thread, but maybe my solution will be helpful to someone. I'm running SAP PI 7.11 and it can be tricked, by creating fake table with the same fields as in view. Configure JDBCLookup for the table as usual. Than just delete table (or change it's name) and set the same name to view.

Than it works without problem. Probably PI calls jdbc function getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) with types limited only to "TABLE". I hope it's fixed in PI 7.3.