### Author:  [@pk.241011](https://discuss.elastic.co/u/pk.241011)
#### Post date: [June 22, 2020, 2:23am UTC](https://discuss.elastic.co/t/why-i-am-getting-this-error-when-using-jdbc-input-plugin-of-logstash/237988/1 "2020-06-22T02:23:16Z")
Here is the query I had put in the logstash conf file:
```auto
select a.ID, a.TransactionID, b.Result
from MyDB.Result a inner join MyDB.ResultData b on a.ID=b.ID
where a.ID < :sql_last_value and a.CreatedOn > '2020-01-01'
order by a.ID
```
This is warning I get:
`[2020-06-22T11:29:01,084][ERROR][logstash.inputs.jdbc][main][c453478trgergy8gh78og6ogy8wef7834g78o9b5] Exception when executing JDBC query {:exception=>#}`
This is the line listed:
`SELECT TOP (1) count(*) AS [COUNT] FROM (select a.ID, a.TransactionID, b.Result from MyDB.Result a inner join MyDB.ResultData b on a.ID=b.ID where a.ID < 100000 and a.CreatedOn > '2020-01-01' order by a.ID) AS [T1]`
I removed the inner join and just put in a simple select statement but still it is not working with the ORDER BY clause.
Without ORDER BY, the whole logic will fail.
As per this [thread](https://discuss.elastic.co/t/how-does-sql-last-value-parameter-work-in-jdbc-input-plugin/101624/6), ORDER BY should fine.
Is it something to do with the Microsoft SQL server which is my source database?
Logstash version: 7.7.0
SQL server version: Microsoft SQL Server 2014 (SP2-CU1) (KB3178925) - 12.0.5511.0 (X64) Aug 19 2016 14:32:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
**EDIT**
Just went through this [thread](https://discuss.elastic.co/t/data-mismatch-in-elasticsearch-when-loading-data-from-database-using-logstash/161513/17).
Looks like he got around this issue by specifying
`SELECT TOP 638858`
Somehow I feel it is not the optimum solution.
---
### Author:  [@ptamba](https://discuss.elastic.co/u/ptamba)
#### Post date: [June 22, 2020, 4:30am UTC](https://discuss.elastic.co/t/why-i-am-getting-this-error-when-using-jdbc-input-plugin-of-logstash/237988/2 "2020-06-22T04:30:59Z")
> [@pk.241011](#):
>
> Java::ComMicrosoftSqlserverJdbc::SQLServerException: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.\>}
this error is coming from the database itself, so yes, MSSQL refuses to execute the query
---
### Author:  [@pk.241011](https://discuss.elastic.co/u/pk.241011)
#### Post date: [June 22, 2020, 5:43am UTC](https://discuss.elastic.co/t/why-i-am-getting-this-error-when-using-jdbc-input-plugin-of-logstash/237988/3 "2020-06-22T05:43:57Z")
Oh dear.
Is there any way out then to keep pulling data out of MSSQL server?
Any ideas how to go about this one then? I am sure I am not the first one to hit this roadblock.
Is this behaviour specific to MSSQL?
Is there any way I can find the last value and assign it explicity to the `:sql_last_value`?
EDIT:
I asked about this on [Stackoverflow](https://stackoverflow.com/questions/62462495/can-i-rewrite-this-sql-query-to-avoid-the-the-order-by-clause-is-invalid/62465762#62465762) and they said that it is happening since my query is getting embedded inside a larger query. Is there something future releases of Elastic can do to make life easier?
---
### Author:  [@Jenni](https://discuss.elastic.co/u/Jenni)
#### Post date: [June 22, 2020, 7:19am UTC](https://discuss.elastic.co/t/why-i-am-getting-this-error-when-using-jdbc-input-plugin-of-logstash/237988/4 "2020-06-22T07:19:06Z")
I know that in your case you are not calling a stored procedure, but I think this solution could apply to your problem as well as the plugin apparently only creates the "`SELECT TOP (1) …` " query in debug mode?
> [@Logstash cannot call SQL Stored Procedure](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/13):
>
> @Ashish_Viradia I found this to be a bug, if you run logstash in debug mode the jdbc plugin will do a SELECT TOP(1) on the stored procedure which is invalid. However, if logstash is not in debug mode then it works fine. Not the best solution but at least a work around. E
---
### Author:  [@pk.241011](https://discuss.elastic.co/u/pk.241011)
#### Post date: [June 22, 2020, 11:43am UTC](https://discuss.elastic.co/t/why-i-am-getting-this-error-when-using-jdbc-input-plugin-of-logstash/237988/5 "2020-06-22T11:43:54Z")
Hi @Jenni,
I tried with `--log.level=info` but I still see the same error.
Beats me how others are able to use it.
I must be missing out on some configuration I think.
This is my config:
```
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://servername:9999;databaseName=Somethign"
jdbc_user => ""
jdbc_password => ""
tracking_column => "ID"
use_column_value => true
tracking_column_type => "numeric"
schedule => "*/1 * * * *"
jdbc_paging_enabled => "true"
jdbc_default_timezone => "Australia/Sydney"
jdbc_page_size => "500"
last_run_metadata_path => "/Data/path/logstash_jdbc_last_run"
```
---
### Author:  [@Jenni](https://discuss.elastic.co/u/Jenni)
#### Post date: [June 22, 2020, 11:46am UTC](https://discuss.elastic.co/t/why-i-am-getting-this-error-when-using-jdbc-input-plugin-of-logstash/237988/6 "2020-06-22T11:46:20Z")
Does it work without paging?
---
### Author:  [@pk.241011](https://discuss.elastic.co/u/pk.241011)
#### Post date: [June 22, 2020, 11:55am UTC](https://discuss.elastic.co/t/why-i-am-getting-this-error-when-using-jdbc-input-plugin-of-logstash/237988/7 "2020-06-22T11:55:46Z")
Yikes !!! It looks like it is working. No error yet !!
Will removing jdbc\_paging\_enabled increase the load on the SQL database ? My understanding was that it would break up a big query into smaller ones which probably be lighter on Database.
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [June 22, 2020, 1:42pm UTC](https://discuss.elastic.co/t/why-i-am-getting-this-error-when-using-jdbc-input-plugin-of-logstash/237988/8 "2020-06-22T13:42:01Z")
I would ignore the error. It tries to do a count, if it fails then it stops trying. More details [here](https://discuss.elastic.co/t/inner-join-and-config-of-logstash-and-jdbc/204406/2).
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 20, 2020, 1:42pm UTC](https://discuss.elastic.co/t/why-i-am-getting-this-error-when-using-jdbc-input-plugin-of-logstash/237988/9 "2020-07-20T13:42:05Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.