Sorry - didn't get the format syntax right.

My problem is that:

ORDER BY c.comp_name

becomes

ORDER BY c\.comp_name

My code is

      @opportunities = WillPaginate::Collection.create(page, per_page) do |pager|
        result = Opportunity.find(:all, 
               :select => "
[o].*,
 [c].[Comp_Name] AS company_name,
 [p].[Pers_Salutation] AS person_salutation,
 [p].[Pers_FirstName] AS person_first_name,
 [p].[Pers_LastName] AS person_last_name,
 [u].[User_FirstName] AS user_first_name,
 [u].[User_LastName] AS user_last_name
",
               :conditions => conditions.join(" AND "),
               :joins => "
AS [o]
LEFT JOIN [Company] AS [c]
  ON [o].[Oppo_PrimaryCompanyId] = [c].[Comp_CompanyId]
LEFT JOIN [Person] AS [p]
  ON [o].[Oppo_PrimaryPersonId] = [p].[Pers_PersonId]
LEFT JOIN [Users] AS [u]
  ON [o].[Oppo_AssignedUserId] = [u].[User_UserId]
",
               :order => 'c.comp_name',
               :limit => pager.per_page,
               :offset => pager.offset)
        # inject the result array into the paginated collection:
        pager.replace(result)

        unless pager.total_entries
          # the pager didn't manage to guess the total count, do it manually
          pager.total_entries = Opportunity.count(:all, :conditions => conditions.join(" AND "))
        end
      end

And the error message is:

DBI::DatabaseError: Execute
    OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
      Incorrect syntax near '\.'.
    HRESULT error code:0x80020009
      Exception occurred.: SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 20  
[o].*,
 [c].[Comp_Name] AS company_name,
 [p].[Pers_Salutation] AS person_salutation,
 [p].[Pers_FirstName] AS person_first_name,
 [p].[Pers_LastName] AS person_last_name,
 [u].[User_FirstName] AS user_first_name,
 [u].[User_LastName] AS user_last_name
 FROM Opportunity  
AS [o]
LEFT JOIN [Company] AS [c]
  ON [o].[Oppo_PrimaryCompanyId] = [c].[Comp_CompanyId]
LEFT JOIN [Person] AS [p]
  ON [o].[Oppo_PrimaryPersonId] = [p].[Pers_PersonId]
LEFT JOIN [Users] AS [u]
  ON [o].[Oppo_AssignedUserId] = [u].[User_UserId]
 WHERE (Oppo_CreatedDate < '2008-12-01 00:00:00' AND Oppo_CreatedDate > '2008-11-01 00:00:00')  ORDER BY c.comp_name) AS tmp1 ORDER BY c\.\[comp_name\] DESC) AS tmp2 ORDER BY c\.\[comp_name\]

I've also tried using paginate_by_sql:

      sql = <<EOF
SELECT [o].*,
  [c].[Comp_Name] AS company_name,
  [p].[Pers_Salutation] AS person_salutation,
  [p].[Pers_FirstName] AS person_first_name,
  [p].[Pers_LastName] AS person_last_name,
  [u].[User_FirstName] AS user_first_name,
  [u].[User_LastName] AS user_last_name
FROM [Opportunity] AS [o]
  LEFT JOIN [Company] AS [c]
    ON [o].[Oppo_PrimaryCompanyId] = [c].[Comp_CompanyId]
  LEFT JOIN [Person] AS [p]
    ON [o].[Oppo_PrimaryPersonId] = [p].[Pers_PersonId]
  LEFT JOIN [Users] AS [u]
    ON [o].[Oppo_AssignedUserId] = [u].[User_UserId]
WHERE #{conditions.join(" AND ")}
ORDER BY [c].[comp_name]
EOF

      @opportunities = Opportunity.paginate_by_sql(sql, :page => params[:page], :per_page => 3)

But this results in another error:

DBI::DatabaseError: Execute
    OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
      The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
    HRESULT error code:0x80020009
      Exception occurred.: SELECT COUNT(*) FROM (SELECT [o].*,
  [c].[Comp_Name] AS company_name,
  [p].[Pers_Salutation] AS person_salutation,
  [p].[Pers_FirstName] AS person_first_name,
  [p].[Pers_LastName] AS person_last_name,
  [u].[User_FirstName] AS user_first_name,
  [u].[User_LastName] AS user_last_name
FROM [Opportunity] AS [o]
  LEFT JOIN [Company] AS [c]
    ON [o].[Oppo_PrimaryCompanyId] = [c].[Comp_CompanyId]
  LEFT JOIN [Person] AS [p]
    ON [o].[Oppo_PrimaryPersonId] = [p].[Pers_PersonId]
  LEFT JOIN [Users] AS [u]
    ON [o].[Oppo_AssignedUserId] = [u].[User_UserId]
WHERE Oppo_CreatedDate < '2008-12-01 00:00:00' AND Oppo_CreatedDate > '2008-11-01 00:00:00'
ORDER BY [c].[comp_name]
) AS count_table

I've investigated this further and it is not a will_paginate bug. It's an active record bug. The same thing happens with a find :

class Opportunity
  def self.find_test
    find(:all,
               :select => "
[o].*,
 [c].[Comp_Name] AS company_name,
 [p].[Pers_Salutation] AS person_salutation,
 [p].[Pers_FirstName] AS person_first_name,
 [p].[Pers_LastName] AS person_last_name,
 [u].[User_FirstName] AS user_first_name,
 [u].[User_LastName] AS user_last_name
",
               :joins => "
AS [o]
LEFT JOIN [Company] AS [c]
  ON [o].[Oppo_PrimaryCompanyId] = [c].[Comp_CompanyId]
LEFT JOIN [Person] AS [p]
  ON [o].[Oppo_PrimaryPersonId] = [p].[Pers_PersonId]
LEFT JOIN [Users] AS [u]
  ON [o].[Oppo_AssignedUserId] = [u].[User_UserId]
",
               :order => 'c.comp_name',
               :limit => 30,
               :offset => 30)
  end
end

Opportunity.find_test

Gives the same error. Can you close this ticket please, and I'll see if I can get the problem sorted elsewhere.

Working on the problem further, its the square brackets causing the problem. They are commonly used in MS SQL code, but ActiveRecord seems to behave differently if it finds them in and offset or limit option. Take out the square brackets and I started getting somewhere. This version of the above test works:

class Opportunity << Base
  def self.find_test
    find(:all,
               :select => "
o.*,
 c.Comp_Name AS company_name,
 p.Pers_Salutation AS person_salutation,
 p.Pers_FirstName AS person_first_name,
 p.Pers_LastName AS person_last_name,
 u.User_FirstName AS user_first_name,
 u.User_LastName AS user_last_name
",
               :joins => "
AS o
LEFT JOIN Company AS c
  ON o.Oppo_PrimaryCompanyId = c.Comp_CompanyId
LEFT JOIN Person AS p
  ON o.Oppo_PrimaryPersonId = p.Pers_PersonId
LEFT JOIN Users AS u
  ON o.Oppo_AssignedUserId = u.User_UserId
",
               :order => 'company_name',
               :limit => 30,
               :offset => 30)
  end 
end

Note: I also has to tweak the :order statement as the limit and offset are used in a SELECT statement within another SELECT statement, and the preceding table name gets stripped off. Therefore you need to use a column name that is consistent in each of the SELECT statements.

So definitely close this ticket now please.