MS SQL Exception: Incorrect syntax near '@P0'

incorrect syntax near '-'. sql date
incorrect syntax near the keyword 'table' sql server
incorrect syntax near
incorrect syntax near
incorrect syntax near sql server
incorrect syntax near
incorrect syntax near
incorrect syntax near

I'm querying a DB using MS SQL and for some reason I get the following error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0' even though this 'P0' isn't anywhere in my syntax...

I've read that someone has had a same issue but they were using a stored proc, something which I am not using so I don't see how his solution will work for me. (His solution being asomething about adding braces {} around the procedure call.

Anyways, below I have pasted the relevant code. Really hope someone can help me with this, getting quite frustrated.

PreparedStatement stmt = null;
Connection conn = null;    

String sqlQuery = "SELECT TOP ? \n"+
                              "z.bankAccountNo, \n"+
                              "z.statementNo, \n"+
                              "z.transactionDate, \n"+
                              "z.description, \n"+
                              "z.amount, \n"+
                              "z.guid \n"+
                              "FROM \n"+
                              "( \n"+
                              "select  \n"+
                              "ROW_NUMBER() OVER (ORDER BY x.transactionDate, x.statementNo) AS RowNumber, \n"+
                              "x.transactionDate, \n"+
                              "x.statementNo, \n"+
                              "x.description, \n"+
                              "x.amount, \n"+
                              "x.bankAccountNo, \n"+
                              "x.guid \n"+
                              "FROM \n"+
                              "( \n"+
                              "SELECT  \n"+
                              "a.bankAccountNo,  \n"+
                              "a.statementNo,  \n"+
                              "a.transactionDate, \n"+
                              "a.description,  \n"+
                              "a.amount,  \n"+
                              "a.guid  \n"+
                              "FROM BankTransactions as a  \n"+
                              "LEFT OUTER JOIN BankTransactionCategories as b  \n"+
                              "ON a.category = b.categoryCode  \n"+
                              "WHERE b.categoryCode is null \n"+
                              ") as x \n"+
                              ") as z \n"+
                              "WHERE (z.RowNumber >= ?)";

stmt = conn.prepareStatement(sqlQuery);
stmt.setInt(1, RowCountToDisplay);
stmt.setInt(2, StartIndex);
ResultSet rs = null;
try{
    rs = stmt.executeQuery();
} catch (Exception Error){
    System.out.println("Error: "+Error);
}

Thanks in advance!

SQL Server requires you to place parenthesis around the argument to top if you pass in a variable:

SELECT TOP (?)

[Solved] Incorrect syntax near ' ', An exception is returned when trying to execute a query containing a hyphen in the SQL query using the Connect for JDBC SQL Server driver. In SQL only one ORDER BY is allowed at the end after Unioning the Select Statements together. To fix the issue I had to find where the I had "Sorted" in a query. So, I looked at the Query steps in the Queries I created (I was merging a Query that was referenced from another Query and the original Query had a Step "Sorted Rows".

In our application we have extended a depraceted SQLServerDialect. After change to SQLServer2008Dialect the problem disappeared.

Article: Incorrect syntax near '-' when executing T-SQL Microsoft SQL , SqlException: 'Incorrect syntax near the keyword 'Table'. if you want pass the data to sql server then you must be created table, just copy the  Service Manager data warehouse jobs stuck or fail with "Incorrect syntax near ')'" Inhoud van Microsoft Van toepassing: System Center 2016 Service Manager System Center 2012 R2 Service Manager

Upgraded hibernate to version 5.x and came across this issue. Had to update "hibernate.dialect" configuration from org.hibernate.dialect.SQLServerDialect to org.hibernate.dialect.SQLServer2012Dialect. Fixed the issue!

Hibernate Doc Reference: https://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/session-configuration.html#configuration-programmatic

Hibernate Jira issue: https://hibernate.atlassian.net/browse/HHH-10032

Error : Incorrect syntax near the keyword 'Table'." ?, Driver version 7.0.0 SQL Server version Microsoft SQL Server 2017 (RTM-CU10) (KB4342123) - 14.0.3037.1 (X64) Jul 27 2018 09:40:27  Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. 320: 16: No: The compile-time variable value for '%.*ls' in the OPTIMIZE FOR clause must be a literal. 321: 15: No

It can also be caused by a syntax error in your SQL as was the case for me

select * from drivel d where exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and '2015-02-05' < drivel.start_date and drivel.end_date < '2015-02-05'

gave the message

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P5'

the problem was actually the balancing ')' missing at the end, namely, correct version is

select * from drivel d where exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and drivel.start_date < '2015-02-05' AND '2015-02-05' < drivel.end_date) OR exists (select * from drivel where d.id = drivel.id and '2015-02-05' < drivel.start_date and drivel.end_date < '2015-02-05')

A processing error "Incorrect syntax near '?'." occurred. in , SQL Server Agent Error message. Error messages: Incorrect syntax near '​sp_MSins_ProductionProduct'. (Source: MSSQLServer, Error  Msg 102, Level 15, State 1, Line 257 Incorrect syntax near 'THROW'. Replacing THROW with PRINT @ErrorMessage works. Replacing @ErrorMessage variable with a literal string works. According to the docs, however, THROW is supposed to be able to take a variable. Not sure what to make of this.

If you are using Hibernate within a spring-boot app, you may set hibernate.dialect with following configuration property:

spring.jpa.database-platform=org.hibernate.dialect.SQLServer2008Dialect

Peer-to-peer replication fails and returns "Incorrect syntax near , SqlException: 'Incorrect syntax near the keyword 'order'.' Or you can put the content of the sqlstatement into your SQL Server for testing. I'm querying a DB using MS SQL and for some reason I get the following error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0' even though this 'P0' isn't anywhere in my

System.Data.SqlClient.SqlException: 'Incorrect syntax near the , The database in use is Microsoft SQL Server 2005. The following appears in the atlassian-confluence.log : 2013-10-03 08:57:12,030 ERROR  Questions: I’m querying a DB using MS SQL and for some reason I get the following error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0' even though this ‘P0’ isn’t anywhere in my syntax… I’ve read that someone has had a same issue but they were using a stored proc, something which I am not using so I

Incorrect syntax near the keyword 'where', ' Member Avatar Converting VB5 source code to VB.NET; Member Avatar incorrect syntax near ' ); Member Avatar Sql server connection using  This feature is not available right now. Please try again later.

vb.net, sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'. SQL Query in Parameterized Database Query node is: SELECT * FROM #table#.

Comments
  • '@P0' is your rowcounttodisplay parameter
  • Can you provide actual sql causing the error? It seems like you do not properly provide RowCountToDisplay
  • Thank you! this was the problem... Been struggling with this for hours. Always a little thing isn't it... Thanks again.
  • This error can also appear with INSERT queries on syntax error binding. The number after @P characters can indicate which parameter produces the error (@P3 for the third parameter, ...).
  • Likely time spent on this problem without SO: 3-8 hours Time spent on this problem with SO: 55 seconds. Thank you!
  • Right you are! Thanks.
  • Although this is the correct, I found the solution to be @Tomasz Godziński answer
  • I experienced the same after moving to Spring Boot 1.4 hence new Hibernate version
  • I got this when using PageRequest. I'm guessing, as per the answer from @Andomar, that it is implemented using a "top" without parentheses. As you say, updating from SQLServerDialect (in my case to 2012) seems to fix the issue. Apparently, this was a change in SQL Server 2005: dotnetfunda.com/articles/show/59/…
  • Works well with Spring Roo. Simply provide property add --key spring.jpa.properties.hibernate.dialect --value org.hibernate.dialect.SQLServer2012Dialect at the end of a roo script.