Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Stored procedure apparently not fully completing or committing before next task is executed

Answered Stored procedure apparently not fully completing or committing before next task is executed

  • Sunday, January 27, 2013 2:32 PM
     
     
    Sign In to Vote
    0

    I believe I'm seeing this both when running a T-SQL script directly in SQL Server Agent, or when executing SQL Tasks in an SSIS package.

    I have a stored procedure that does a lot of inserts in to Table A and then subsequent SQL statements that act on those inserted rows in Table A.

    The problem in both SQL Server Agent (when using a T-SQL step) or when running an SSIS package in SQL Server Agent** is that it sure seems that the task running the stored procedure, just to illustrate, inserts 5,000 rows and then control is passed to the next Execute SQL statement when really the stored procedure has not finished yet and will insert another 4,000 before it is done.

    I know this because if I run the same T-SQL script in SSMS, the results are correct and as expected.

    In any event, any thoughts appreciated.  Thanks.

    ** the T-SQL step and the SSIS package do the same thing, the code in the SSIS package just uses Execute SQL Statements that were ripped out of the T-SQL step.

     

All Replies

  • Sunday, January 27, 2013 2:46 PM
     
     Answered
    Sign In to Vote
    0

    I suggest you run a trace (using Profiler or a server-side trace) to capture batch and RPC completed events.  This will confirm or deny your suspicion. 

    Stored procedures and ad-hoc SQL statements run synchronously as invoked by the client application.  Unless you have an explicit transaction, each data modification statement is in a separate transaction and automatically committed when the statement completes.  With an explicit transaction, changes are committed during the explicit COMMIT.

    You might try adding SET NOCOUNT ON to your proc if you haven't already done so.  This will suppress DONE_IN_PROC (rowcount) messages that can confuse applications that are not expecting them.

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked As Answer by Bob Hmine Monday, January 28, 2013 12:58 PM
    •  
     
  • Monday, January 28, 2013 1:50 PM
     
     
    Sign In to Vote
    0

    Thanks Dan,

    The trace would have been a good idea.  This did turn out to be operator error, the sequence of events/statements in the overnight load was not what I presumed it to be, so it really had nothing to do with commits in the stored procedure.