Hi, I have a sql query that I am running using the CreateSQLQuery of NHibernate. It use to work perfectly when I was on NHibernate 1.2, but since I have upgraded to 2.0 I am getting the SqlException error (0x80131904) Ambiguous column name on __hibernate_sort_expr_0__.
On checking the logs I saw the query that NHibernate actually runs has the sql ROW_NUMBER() function. Now I was able to solve the issue by specifying the rows explicitly instead of using ".*", but I would not want every direct sql query run through NHibernate to use Row_Number (). Does anyone has any idea how I can avoid this, or why this is happening ? Thanks, - AB Here is a simplified version of the above problem: SQL Query: SELECT BDTC.* from BDTC INNER JOIN Drug ON BDTC.DrugId = Drug.DrugId WHERE BDTC.BookId = 2 This is what NHibernate runs (from its logs): SELECT TOP 5 * FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__) as row, query.*, query.__hibernate_sort_expr_0__ FROM (SELECT BDTC.*, CURRENT_TIMESTAMP as __hibernate_sort_expr_0__ from BDTC INNER JOIN Drug ON BDTC.DrugId = Drug.DrugId WHERE BDTC.BookId = 2) query ) page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_0__ which throws this error in sql: Msg 8156, Level 16, State 1, Line 1 The column '__hibernate_sort_expr_0__' was specified multiple times for 'page'. Msg 209, Level 16, State 1, Line 1 Ambiguous column name '__hibernate_sort_expr_0__'. This is how I am calling the CreateSQLQuery: ISQLQuery sqlQuery = NHibernateSession.CreateSQLQuery(sql).AddEntity (alias, type); --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
