Calling SQL Server stored procedures via ODBC fails, leaving empty tables

Question

We are using ODBC from C on Linux. We can successfully execute direct statements ("INSERT ...", "SELECT ...", etc) using ODBC for both SQL Server 2008 and MySQL. We are migrating to stored procedures, so we first developed MySQL stored procedures. Calling MySQL stored procedures using ODBC works. Life is good.

The stored procedures are translated into T-SQL. We verify that they function by executing queries directly from Visual Studio. The database is filled, queries work. Huzzah.

We have a test program allowing us to use MySQL or SQL Server, direct execution or calling stored procedures. We call the T-SQL stored procedures from a C test program. Log output indicates that tables are being filled with data, queries are working, etc. Until the end, where a statement fails. The program exits (taking several seconds longer than normal). The other 3 cases work (direct MySQL, direct SQL Server, stored proc MySQL).

We examine the SQL Server database. It's empty. We have autocommit turned on, so I don't think it's a commit problem. The stored procs are bog simple, being copies of the direct SQL. Any ideas?

Answer 1

It sounds like the query is running - then errors out for some reason, and everything is wrapped up as a single transaction - and rolls back. Hence empty tables.

Does the stored procedure have any error trapping within it? SQL Server 2005 and later improved error handling enormously with TRY.. CATCH.