Workflow activity search SQL error "ambiguous column name"

asked on August 28, 2014 • Show version history

When we try and run anything under "Common Searches" in workflow designer we get an error. I have opened a case with support, but while I try a million things with that can I ask a simple question, the syntax of this query looks wrong.

select top 500 * from ( select top 500 SI.search_id, instance_id, parent_instance_id, IWN.workflow_name, IRN.rule_name, version, IUN.user_name, SI.workflow_id, repository_name, server_name, entry_id, entry_name, entry_path, entry_guid, start_time, status_time, idle_time, status, has_errors, has_warnings, has_informations, BPI.bpi_name, BPI.bpi_status, BPI.bpi_due_date from search_instance SI left join search_entry SE on SI.search_id = SE.search_id and SE.is_starting_entry = 1 left join search_status SS on SI.search_id = SS.search_id left join search_rep SR on SR.repository_id = SE.repository_id left join index_workflow_name IWN on SI.workflow_id = IWN.workflow_id left join index_rule_name IRN on SI.workflow_rule = IRN.rule_name_id left join index_user_name IUN on SI.workflow_user = IUN.user_name_id left join bp_instance BPI on SI.search_id = BPI.bpi_id where SI.search_id > @search_id_1 and ( SS.status_time > @min_status_time_1 or SS.status_time is NULL) order by search_id asc union all select top 500 SI.search_id, instance_id, parent_instance_id, IWN.workflow_name, IRN.rule_name, version, IUN.user_name, SI.workflow_id, repository_name, server_name, entry_id, entry_name, entry_path, entry_guid, start_time, status_time, idle_time, status, has_errors, has_warnings, has_informations, BPI.bpi_name, BPI.bpi_status, BPI.bpi_due_date from search_instance_log SI left join search_entry_log SE on SI.search_id = SE.search_id and SE.is_starting_entry = 1 left join search_status_log SS on SI.search_id = SS.search_id left join search_rep SR on SR.repository_id = SE.repository_id left join index_workflow_name IWN on SI.workflow_id = IWN.workflow_id left join index_rule_name IRN on SI.workflow_rule = IRN.rule_name_id left join index_user_name IUN on SI.workflow_user = IUN.user_name_id left join bp_instance BPI on SI.search_id = BPI.bpi_id where SI.search_id > @search_id_2 and ( SS.status_time > @min_status_time_2 or SS.status_time is NULL) order by search_id asc ) t order by search_id asc

Well there is an error if I copy it into SQL manager which is "order by search_id ", that's the ambiguous column name. If I place SI. or one of the other tables in front of it, it works.

Could anyone else have a look at the query that their search runs and see if it runs the exact same query. I am trying to figure out if its an error in my install somehow. Or if for some reason my exact version of SQL doesn't like that syntax, but others would be fine with it.

I am running SQL 10.0.1600 (SQL 2008)

Thanks

0 0

replied on December 15, 2014

I'm getting the same "ambiguous column name" error after updating to 9.2. I found my db was also on a compatibility level of 2000, so I upped it to 2008. The problem has not been resolved. any other ideas?

0 0

View 1 previous reply

replied on December 15, 2014

no, query is:

select trustee_name, u.sid, account_name from (select trustee_id as id, sid from trusted_group union select userid as id, sid from trusted_login) u left join trustee on id = trustee_id left join account_cache on u.sid = account_sid where left(sid, 2) <> 0x0106 order by trustee_name, account_name

0 0

replied on December 15, 2014

Oh, ok. Then it's not the same issue. The error message is the same, but the causes can be different. That looks like a query against the Laserfiche Server, this threads is about a Workflow query.

0 0

replied on December 15, 2014

Yeah, I'm being told it's an issue with LFSO80 working with LFS 9. not cool, as my app has to use that version.

0 0

Question

Question

Workflow activity search SQL error "ambiguous column name"

Answer

Replies

Sign in to reply to this post.