No exception thrown in sqlContext.sqlDBQuery

[ArneV94]

In sqlDBQuery, when an exception occurs the error doesn't get thrown but instead gets printed out. IMO it would be better to close the connection and throw an exception so the user can handle this properly.

[jbarracca]

I second this, but if you still want to keep stifling the exceptions, can you return the stack trace or something? It is currently impossible to log the results of a failed query in the current implementation.

[david-nguyen]

third, why isn't an exception thrown?

[tdppnr]

Fourth, In my case, we executed a stored procedure on SQL Managed Instance, which internally executed a dynamic sql statement. Certain errors were noticed only when the procedure was directly executed on sql server but only the message was printed in databricks. The problem was "SET NOCOUNT". One of my team mates looked into the connector code here and found the below

[syedhamjath]

I also came across similar scenario with sqlDBQuery, where is the exception? What is the purpose of this behavior?

   try
    {
      sqlContext.sqlDBQuery(config)
    }
    catch
    {
      case _ : Throwable  => println("Error while Creating/Dropping table"+line)
      throw new Exception("Error while Creating/Dropping table"+line)
    }

[arvindshmicrosoft]

This project is not being actively maintained. There are no plans to investigate and / or fix any issues with this old connector. Instead, Apache Spark Connector for SQL Server and Azure SQL is now available, with support for Python and R bindings, an easier-to use interface to bulk insert data, and many other improvements. We encourage you to actively evaluate and use the new connector.

Also, note that it may be easier in some cases to directly use SQL JDBC to execute statements. Here is a trivial example for SELECT:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

val ds = new SQLServerDataSource();
ds.setServerName("SOMESERVER.database.windows.net"); // Replace with your server name
ds.setDatabaseName("SOMEDB"); // Replace with your database
ds.setUser("SOMEUSER"); // Replace with your user name
ds.setPassword("SOMEPASSWORD"); // Replace with your password
ds.setHostNameInCertificate("*.database.windows.net");

val connection = ds.getConnection(); 
val stmt = connection.createStatement();

val rs = stmt.executeQuery("SELECT SUSER_NAME()");
if (rs.next()) {
    print("You have successfully logged on as: " + rs.getString(1));
}

and here's another showing how to execute an UPDATE and also obtain the affected row count:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

val ds = new SQLServerDataSource();
ds.setServerName("SOMESERVER.database.windows.net"); // Replace with your server name
ds.setDatabaseName("SOMEDB"); // Replace with your database
ds.setUser("SOMEUSER"); // Replace with your user name
ds.setPassword("SOMEPASSWORD"); // Replace with your password
ds.setHostNameInCertificate("*.database.windows.net");

val connection = ds.getConnection(); 

val query = "UPDATE someTable SET someCol = someThing"

val pStmt = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
val affectedRows = pStmt.executeUpdate();
print(affectedRows);