title: Spring Data and SQL Server getting Incorrect syntax near the keyword 'as' error
body: <p>I'm trying to use Spring Data to read from a SQL Server table.  I have native queries that work fine, but I want to use a Spring Repository and find() methods instead.</p>
  
  <p>I have an Entity defined:</p>
  
  <pre><code>@Entity
  @Table(name = "my table")
  // This native query works when I try and use it.
  @NamedNativeQuery(name = "Associate.getDistinctBusinessUnits",
      query = "SELECT DISTINCT bus_unit_name FROM \"my table\" ORDER BY bus_unit_name"
  )
  public class Associate {
      @Id
      @Column(name = "assoc_oper_id", columnDefinition = "VARCHAR(50)")
      public String assocOperId;
  
  ...
  }
  </code></pre>
  
  <p>and a Respoitory</p>
  
  <pre><code>@Repository
  public interface AssociateRepository extends PagingAndSortingRepository&lt;Associate, String&gt; {
      List&lt;String&gt; getDistinctBusinessUnits();
  }
  </code></pre>
  
  <p>and my controller is calling the repository</p>
  
  <pre><code>// This query fails.
  final Optional&lt;Associate&gt; result = associateRepository.findById(associateId);
  </code></pre>
  
  <p>but when it does I get</p>
  
  <pre><code>12147 [http-nio-8080-exec-1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Incorrect syntax near the keyword 'as'.
  12181 [http-nio-8080-exec-1] ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [/roster-service] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause
  com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'as'.
          at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:256)
          at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621)
          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:592)
          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)
          at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
          at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2930)
          at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
          at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:444)
          at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
          at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
          at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
          at org.hibernate.loader.Loader.getResultSet(Loader.java:2167)
          at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1930)
          at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892)
          at org.hibernate.loader.Loader.doQuery(Loader.java:937)
          at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340)
          at org.hibernate.loader.Loader.doList(Loader.java:2689)
          at org.hibernate.loader.Loader.doList(Loader.java:2672)
          at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506)
          at org.hibernate.loader.Loader.list(Loader.java:2501)
          at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:504)
          at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:395)
          at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220)
          at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1508)
          at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1537)
          at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505)
          at org.hibernate.query.Query.getResultList(Query.java:135)
          at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:74)
          at org.springframework.data.jpa.repository.support.SimpleJpaRepository.readPage(SimpleJpaRepository.java:569)
          at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:388)
          at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:356)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
          at java.lang.reflect.Method.invoke(Method.java:498)
          at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:359)
          at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:200)
          at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:644)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
          at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:608)
          at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$invoke$3(RepositoryFactorySupport.java:595)
          at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:595)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
          at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
          at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
          at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
          at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
          at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
          at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
          at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
          at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
          at com.sun.proxy.$Proxy119.findAll(Unknown Source)
          at com.cerner.abilities.roster.service.controllers.AssociatesController.getAssociates(AssociatesController.java:59)
          at com.cerner.abilities.roster.service.controllers.AssociatesController$$FastClassBySpringCGLIB$$a1313748.invoke(&lt;generated&gt;)
          at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
          at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
          at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
          at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
          at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
          at com.my.project.controllers.AssociatesController$$EnhancerBySpringCGLIB$$34fe814.getAssociates(&lt;generated&gt;)
  </code></pre>
  
  <p>I know that my DB connection is working fine as my native query Repository method works as expected.  I've tried several different flavors of existing Repository methods plus ones I defined to try and get the same error.</p>
  
  <p>My spring configuration is</p>
  
  <pre><code># ===============================
  # DATABASE
  # ===============================
  
  spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
  spring.datasource.url=jdbc:sqlserver://grddwmrtwhqipdw.northamerica.cerner.net:1433;databaseName=IPDW_PROD02
  spring.datasource.username=ablab
  spring.datasource.password=ablab
  
  
  # ===============================
  # JPA / HIBERNATE
  # ===============================
  
  spring.jpa.show-sql=true
  spring.jpa.hibernate.ddl-auto=none
  spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServerDialect
  </code></pre>
  
  <p>I'm using Spring Boot 2.1.3 with Spring Data 2.1.5 (I did try Spring Data 2.1.3 in case they needed to be in step) which I believe are the current latest versions available.</p>
  
  <p>I don't own the DB server and don't know how to definitively check the version from SQL but I have tried several different dialects.  I also don't know where there's a definitive list of all available dialects for a driver so I don't know if I tried them all.</p>
  
  <p>I'm not terribly experienced with Spring nor Spring Data.  I've used the Repository in similar sorts of ways against Oracle without problem so my guess is a peculiarity with SqlServer.  </p>
  
  <p>Anyone have some ideas where to go from here?</p>
  
  <p>Thanks!!</p>