One batch containing many SQL commands using ODBC.jl does not work with SQL Server

General Usage
,
1

Hello,

I am using ODBC.jl to create a connection to a Microsoft SQL Server Database.

conn = ODBC.Connection(Driver=ODBC Driver 17 for SQL Server;SERVER=MyServer;DATABASE=MyDatabase;Trusted_Connection=Yes")

Then I am executing sql queries with this command in Julia:

DBInterface.execute(conn, sql_string)

My sql_string consists of this here:

USE MyDatabase;

BEGIN TRANSACTION

EXEC sp_rename Table199, Table200;
EXEC sp_rename Table198, Table199;
EXEC sp_rename Table197, Table198;
...
EXEC sp_rename Table1, Table2;

COMMIT TRANSACTION;

I have 199 commands within one big SQL transaction block because I want to rename 199 tables.

When I do DBInterface.execute(conn, sql_string), then nothing happens. The tables were not renamed, but I also got no error message in Julia.

I heard about a MARS configuration that should be set to true if many SQL queries run as a batch. How shall I set MARS = true in ODBC.jl connections?
Or is the cause for my problem another one?

Thanks for any advices!

Kind regards,
Miroslav Stimac

P.S.:
If I use a loop and do the table renaming with seperate calls of DBInterface.execute(conn, sql_string) for each of the 199 tables, then the tables are renamed, but I want to do it with one big SQL transaction block. The big SQL transaction block is correct and works correctly when executing it by using SQL Server Management Studio. There is no problem with the SQL query, but with the ODBC connection.

2

I hadn’t heard of this MARS, SQL Server-specific, but might help:

By default, MARS functionality is not enabled by the driver. To use MARS when connecting to SQL Server with SQL Server Native Client, you must specifically enable MARS within a connection string.

3

Thanks for the link, but sadly it did not help.
I extended the connection string to

conn = ODBC.Connection("Driver=ODBC Driver 17 for SQL Server;SERVER=MyServer;DATABASE=MyDatabase;Trusted_Connection=Yes;MultipleActiveResultSets=True")

But the tables are still not renamed.
As I mentioned, I do not know whether MARS is the cause of the problem. This was just an idea.

4

There is also DBInterface.executemultiple which - according to the documentation - allows for executing multiple semicolon separated statements in a single query.

1 Like
5

What you saw :

MARS is disabled by default so you must explicitly enable it by adding MultipleActiveResultSets=True

is under the “In-Memory OLTP” section that may, or (most) likely may not apply for you. [At first I thought might be =True vs =Yes, and interesting Microsoft documents both, make sure which or both works… It seems to be a mess how they choose to support enabling things.]

I also see:

The SQL Server Native Client ODBC driver supports MARS through additions to the SQLSetConnectAttr and SQLGetConnectAttr functions. SQL_COPT_SS_MARS_ENABLED has been added to accept either SQL_MARS_ENABLED_YES or SQL_MARS_ENABLED_NO, with SQL_MARS_ENABLED_NO being the default. In addition, a new connection string keyword, Mars_Connection, as been added. It accepts “yes” or “no” values; “no” is the default.

[Note, as → has [been] typo; :slight_smile: You may want to report to Microsoft, I will not bother, I don’t use their products, and recommend PostgreSQL, you could try, it likely has no problem with their defaults out of the box.]

But I’m not even sure you’re using “SQL Server Native Client” since I also see:

The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS). The SQL Server Native Client (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new application development. Switch to the new Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server or the latest Microsoft ODBC Driver for SQL Server going forward.

Maybe this helps, or the other advice, but not this doesn’t seem like a Julia problem. I would try to confirm what works independently of Julia, then try the same there.

6

Thanks for your answer!
I tried both of the two additional configurations that you mentioned and sadly it does not help.
By the way, Mars_Connection accepts only “yes” and not “true”. In case of “true” I get an error message saying that this value is invalid for Mars_Connection. So, Mars_Connection seems really to exist and “yes” is accepted, but the table renaming still does not work.

I also tried SQL_COPT_SS_MARS_ENABLED with the value SQL_MARS_ENABLED_YES, but this does not help, too.

I will program a workaround and avoid using multiple SQL queries when using DBInterface.execute(conn, sql_string) and ODBC.

Perhaps it would help to install a newer ODBC Driver from Microsoft, but even if this perhaps work, I have no permission to do this on the Windows Server that is running the SQL Server in the production environment.

1 Like