collectiveidea/delayed_job_active_record — pull request #57 by avinmathew (2013-04-29 05:32:52+00:00)
Title: ActiveRecord::Job::reserve and MSSQL

I have a Rails 3 application that runs on multiple databases including Oracle and MS SQL Server via JRuby (1.7.3). I've encountered an issue with SQL statement generation from delayed_job (3.0.5) when calling `Delayed::Worker.new.work_off`. Using Oracle (`activerecord-oracle_enhanced-adapter`), this statement works fine. Using SQL Server, I receive the following error:

> ActiveRecord::JDBCError: 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.

The generated SQL is:

```sql
UPDATE [delayed_jobs]
SET [locked_at] = '2013-04-29 03:19:39', [locked_by] = N'host:PC pid:3160'
WHERE [delayed_jobs].[id] IN
(
SELECT [delayed_jobs].[id]
FROM [delayed_jobs]
WHERE [delayed_jobs].[id] = 1
AND ((run_at <= '2013-04-29 03:19:39' AND (locked_at IS NULL OR locked_at < '2013-04-28 23:19:39') OR locked_by = N'host:PC pid:3160') AND failed_at IS NULL)
ORDER BY priority ASC, run_at ASC
)
```

I've tracked this down to `Delayed::Backend::ActiveRecord::Job::reserve`. Line 51 adds a priority to the query but this doesn't make sense in the context of the above update statement with subquery. The Oracle adapter automatically splits the generated SQL into two queries so a subquery isn't used.

Like the PostgreSQL and MySQL customisations, I've written one for MSSQL using a combination of their techniques. As I'm using JRuby, I've only tested against `activerecord-jdbcmssql-adapter`.