Trying to get output of PRINT command after select in Transaction

  • Hi

    I am looking to select the output of the data that I plan to update if rolling back the data, and print out some details as per the code below. When I run the select it doesn't then return the PRINT commands after just the output of the SQL. Is this all related to the buffer data, sure obvious but just trying to find the best way to display the data and output of the PRINT statement.

    Thanks

    Ian

     IF (@debug = 1) -- If Chosen to update, then commit changes
    BEGIN
    COMMIT TRANSACTION TRAN1;
    PRINT 'Site updated,Transaction committed'
    END
    ELSE -- If run in debug then rollback updates applied
    BEGIN
    select electrician,meteringactual,arrivalEnergyStatus,DepartureEnergyStatus from Appointments where id in (select appID from #CompleteTemp)
    ROLLBACK TRANSACTION TRAN1;
    PRINT 'No site data updated. Transaction rolled back'
    END
  • PRINT goes to the messages tab in SSMS as its information not data

     

    If you want it in the data result pane or data set you would want to use SELECT instead of PRINT

     IF (@debug = 1) -- If Chosen to update, then commit changes
    BEGIN
    COMMIT TRANSACTION TRAN1;
    SELECT 'Site updated,Transaction committed'
    END
    ELSE -- If run in debug then rollback updates applied
    BEGIN
    select electrician,meteringactual,arrivalEnergyStatus,DepartureEnergyStatus from Appointments where id in (select appID from #CompleteTemp)
    ROLLBACK TRANSACTION TRAN1;
    SELECT 'No site data updated. Transaction rolled back'
    END
  • Hi Ant-Green

    Yes sorry missed that, schoolboy my end.

    Thanks for the reply

    Regards

    Ian

  • PRINT may also be substantially delayed an may not show up in some job output in SQL Agent.  I use RAISERROR('somemessagehere',0,0) WITH NOWAIT; for almost real time feedback (but even that has a limit and will start "chunking" in groups of 100 messages after something like 500 messages have been displayed... yeah... I know... don't ask... :D).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • "after something like 500 messages have been displayed... yeah... I know... don't ask..."

    Ouch. I'd like to see that. Not on my servers, mind you, but it would be interesting to see such a beast.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply