title: execute query on sqlserver using spark sql
body: <p>I am trying to get the row count and column count of all the tables in a schema in sql server using spark sql.</p>
  
  <p>when I execute below query using sqoop, it's giving me the correct results.</p>
  
  <pre><code>sqoop eval --connect "jdbc:sqlserver://&lt;hostname&gt;;database=&lt;dbname&gt;" \
  --username=&lt;username&gt; --password=&lt;pwd&gt; \
  --query """SELECT 
  ta.name TableName ,
  pa.rows RowCnt, 
  COUNT(ins.COLUMN_NAME) ColCnt FROM &lt;db&gt;.sys.tables ta INNER JOIN 
  &lt;db&gt;.sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN 
  &lt;db&gt;.sys.schemas sc ON ta.schema_id = sc.schema_id join 
  &lt;db&gt;.INFORMATION_SCHEMA.COLUMNS ins on ins.TABLE_SCHEMA =sc.name and ins.TABLE_NAME=ta.name 
  WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) and sc.name ='&lt;schema&gt;' GROUP BY sc.name, ta.name, pa.rows order by 
  TableName"""
  </code></pre>
  
  <p>But when I try to execute the same query from spark sql, I am getting an error that "<strong>com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WHERE'</strong>"
   Please help me out, if anyone has an idea about this error.</p>
  
  <p>Below is the spark sql command I executed
  spark-shell --jars "/var/lib/sqoop/sqljdbc42.jar"</p>
  
  <pre><code>sqlContext.read.format("jdbc").option("url", "jdbc:sqlserver://&lt;hostname&gt;;database=&lt;dbname&gt;;user=&lt;user&gt;;password=&lt;pwd&gt;").option("dbtable", """(SELECT 
  ta.name TableName ,pa.rows RowCnt, 
  COUNT(ins.COLUMN_NAME) ColCnt FROM &lt;db&gt;.sys.tables ta INNER JOIN 
  &lt;db&gt;.sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN 
  &lt;db&gt;.sys.schemas sc ON ta.schema_id = sc.schema_id join 
  &lt;db&gt;.INFORMATION_SCHEMA.COLUMNS ins on ins.TABLE_SCHEMA =sc.name and ins.TABLE_NAME=ta.name 
  WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) and sc.name ='&lt;schema&gt;' GROUP BY sc.name,ta.name, pa.rows)""").option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver").load()
  </code></pre>
  
  <p><strong>expected output:</strong></p>
  
  <p>TableName, RowCnt, ColCnt</p>
  
  <p>table A, 62, 30</p>
  
  <p>table B, 3846, 76</p>