MS SQL Server Support: Cannot Paginate

[michalisa]

Trying to go through the pagination (Page 2 on any table) the following error is reported:

CompileError: MSSQL requires an order_by when using an offset.

Note that if you first sort by a column and try it again, it works fine.

[jeffknupp]

Looking at this now

[jeffknupp]

Ah, misread that. Thought you said MySQL. I'll need to get access to MS SQL to test a fix for this.

[nrnelson]

Have you by chance had any opportunity to look into this? I just encountered the same problem yesterday when using Microsoft SQL Server 2005 SP4 with Sandman v0.9.6 and Python 3.4.1.

[amdemas]

Not to rehash this, but I felt a new ticket would be a waste. I am having the same issue Sandman v0.9.8, Python 3.4.2, MSSQL 2012. Here is the full stack

Traceback (most recent call last): File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\flask\app.py", line 1836, in **call** return self.wsgi_app(environ, start_response) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\flask\app.py", line 1820, in wsgi_app response = self.make_response(self.handle_exception(e)) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\flask\app.py", line 1403, in handle_exception reraise(exc_type, exc_value, tb) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\flask_compat.py", line 33, in reraise raise value File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\flask\app.py", line 1817, in wsgi_app response = self.full_dispatch_request() File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\flask\app.py", line 1477, in full_dispatch_request rv = self.handle_user_exception(e) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\flask\app.py", line 1381, in handle_user_exception reraise(exc_type, exc_value, tb) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\flask_compat.py", line 33, in reraise raise value File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\flask\app.py", line 1475, in full_dispatch_request rv = self.dispatch_request() File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\flask\app.py", line 1461, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\flask_admin\base.py", line 68, in inner return self._run_view(f, _args, *_kwargs) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\flask_admin\base.py", line 344, in _run_view return fn(self, _args, *_kwargs) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\flask_admin\model\base.py", line 1260, in index_view view_args.search, view_args.filters) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\flask_admin\contrib\sqla\view.py", line 827, in get_list query = query.all() File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\sqlalchemy\orm\query.py", line 2320, in all return list(self) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\sqlalchemy\orm\query.py", line 2438, in __iter__ return self._execute_and_instances(context) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\sqlalchemy\orm\query.py", line 2453, in _execute_and_instances result = conn.execute(querycontext.statement, self._params) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\sqlalchemy\engine\base.py", line 729, in execute return meth(self, multiparams, params) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\sqlalchemy\sql\elements.py", line 322, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\sqlalchemy\engine\base.py", line 819, in _execute_clauseelement inline=len(distilled_params) > 1) File "", line 1, in File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\sqlalchemy\sql\elements.py", line 493, in compile return self._compiler(dialect, bind=bind, *_kw) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\sqlalchemy\sql\elements.py", line 499, in _compiler return dialect.statement_compiler(dialect, self, *_kw) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 873, in __init__ super(MSSQLCompiler, self).**init**(_args, *_kwargs) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\sqlalchemy\sql\compiler.py", line 395, in **init** Compiled.**init**(self, dialect, statement, *_kwargs) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\sqlalchemy\sql\compiler.py", line 199, in **init** self.string = self.process(self.statement, *_compile_kwargs) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\sqlalchemy\sql\compiler.py", line 222, in process return obj._compiler_dispatch(self, *_kwargs) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\sqlalchemy\sql\visitors.py", line 80, in _compiler_dispatch return meth(self, *_kw) File "F:\Index Laptop\Documents\Development\VirtualEnvs\FileInbound\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 935, in visit_select raise exc.CompileError('MSSQL requires an order_by when ' sqlalchemy.exc.CompileError: MSSQL requires an order_by when using an offset.