Why does the Store Procedure I'm calling over ODBC fail at the same location?

Question

I'm using the freeodbc++ library to access data on a MS SQL Server 2000 database (SP3? SP4?). In particular, I'm running a particularly long and nasty stored procedure. I can watch the procedure execute in SQL Profiler, however, it tends to stop processing at a certain point. No error codes or exceptions thrown. If I comment out the nested statement that is always the last statement, it just ends slightly before the comment. I haven't tried radically commenting out the whole darn thing... I'm setting the query timeout to 300 seconds. The callable statement usually returns in under 1 sec, without actually finishing the SP.

Any ideas?

UPDATE0: If I run the SP via Query Analyzer or some other tool... it works. It's just via my ODBC connection that it fails.

UPDATE1: As I comment out code, the execution ends further into the SP. Makes me think there is a timeout or buffer limit that I'm running into.

Answer 1

Run the procedure from query analyzer and see what happens. You can use the RAISERROR() function in the procedure to provide tracing information back to the message window to help you debug.

Answer 2

Have you tried using TRY and CATCH? It might be throwing an error on a function call within your stored procedure that you won't see.

BEGIN TRY
 <Your code>
END TRY
BEGIN CATCH
        DECLARE @ErrMsg nvarchar(max),
      @ErrSeverity int,
      @ErrState int
    SELECT @ErrMsg = ERROR_MESSAGE(),
      @ErrSeverity = ERROR_SEVERITY(),
      @ErrState = ERROR_STATE()

    RAISERROR (@ErrMsg,@ErrSeverity,@ErrState);
END CATCH

Answer 3

Have you tried profiling on the SQL Server side, to see what is happening with your SPID?

Also, I have not used freeodbc++, but maybe there is a PRINT statement in there that it does not like. You could also SET NOCOUNT ON to suppress the row count messages. Again, this depends upon how freeodbc++ reacts to these "informational" messages.

It sounds like a bug in freeodbc++, based on this "freezing" style of behavior that you describe. Start with examining the process on the SQL side to see if it is really hung, or if your library just "died" on you.

Answer 4

Adding "::Sleep(30000);" immediately after I call execute on the ODBC statement object (and before the statement close command) keeps the server process from exiting prematurely. Must be a bug with freeodbc++ or a configuration error on my part.

Thanks for the troubleshooting/debugging tips.