SQL Query in Excel does not work

[David]

I have created the following SQL query:

SELECT part_e.ea_guid as CLASSGUID, part_e.Object_Type as CLASSTYPE, port_s.Name as s_port, part_s.Name as s_part, blk_s.Name as s_blk, port_e.Name as e_port, part_e.Name as e_part, blk_e.Name as e_blk
FROM (((((t_object AS port_s INNER JOIN t_connector AS c ON port_s.Object_ID = c.End_Object_ID) INNER JOIN t_object AS port_e ON c.Start_Object_ID = port_e.Object_ID) INNER JOIN t_object AS part_e ON port_e.ParentID = part_e.Object_ID) INNER JOIN t_object AS part_s ON port_s.ParentID = part_s.Object_ID) INNER JOIN t_object AS blk_s ON part_s.PDATA1 = blk_s.ea_guid) INNER JOIN t_object AS blk_e ON part_e.PDATA1 = blk_e.ea_guid

This query works great in EA. I can also use it to find items in diagrams and in the project browser.

I do the following:
1) Create a new excel workbook in my EA model
2) Click on the searches tab. Select my query from the list.

I get the following error:

SQL failed:
System.runtime.interop.services.COMException (#): To run a custom search you must specify ea_guid AS CASSGUID …

As you can see in my query, I have it specified. Any ideas what is wrong?

[eadocX Support]

Interesting. You’re using two bits of eaDocX which we have never tested together. The SQL query has been tested with Word, but never with Excel.
Can you get ANY kind of simple SQL query to work?

[David]

I can get searches that are built using the search builder found in EA to work. I can not get even the simplest searches built with SQL to work.

Also, In MS Word, the ‘EA Search” has

as an option and the package option. SQL Query does not. This seems strange. Do I have to create a custom query for every option I want put in my document? I would like to see the same two options for the SQL Query.

Perhaps the MS Excel should have the same two search options (EA Search and SQL Query) as MS Word. Is this what is needed?

[David]

It seems like the SQL query does work in Excel if you do the following:

1) Put t_object.ea_guid AS CLASSGUID, as the first statement in the SQL SELECT
1.1) The ‘AS’ must be capital letters. A lower case ‘as’ will not work.
2) Only use the ea_guid out of the t_object table. It does not seem to work on other tables.

[Author]

Posts