OUTPUT FROM TSQL

Question

OUTPUT FROM TSQL

Niall Cronin

SSC-Addicted

Points: 450
More actions
July 8, 2008 at 4:40 am

#184304

All,
In previous editions of SQL Server if during a batch process you include a select the output
would be returned immediately to the tool calling the process. HOwever in sqlserver 2005
you have to wait till the end of the batch.
E.g.
declare retcode int
select @retcode = 0
while @retcode < 1000000
begin
select @retcode
set @retcode = @retcode + 1
end
in previous editions you would see the numbers 1 to 100000 while the batch was execting. now however you have to wait till the batch has exectued and then the output is returned. Is there anyways of getting it to output the out as u process.
Regards niall

Viewing 7 posts - 1 through 6 (of 6 total)

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

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

The only way I know of will hurt your performance. You can put FAST 1 hint on the query and it will start returning data immediately. It will however, really mess up your queries, creating two execution plans, one to get the rows back fast and the other, only viewable when you look at the proc cache, to return the rest of the data.

If the data set you're returning is really large, you'll usually start seeing it returned before the processing is done.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

gbd77rc Ten Centuries Points: 1031 More actions · Answer 2

Hi niall

You can do the same thing with PRINT command

declare @retcode int

set nocount on

select @retcode = 0

while @retcode < 1000000

begin

print @retcode

set @retcode = @retcode + 1

end

It will output information when it happens and not after a batch of processing.

If you are using this information within .NET application then just InfoMessage event on the connection object.

Regards

Richard...

http://www.linkedin.com/in/gbd77rc

Niall Cronin SSC-Addicted Points: 450 More actions · Answer 3

Cheers lads.

Yeah I had thought of the print however I think you need

to convert everything to string i think in order to use

that. Was hoping it was some dboption which was

introduced in sql2005. But alas they seem to have messed it up 🙂

Thanks Again for your replies.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

This will help... details are in the comments

[font="Courier New"]--===== How to force messages to screen on long running queries

--=========================================================================

-- What normally happens is there is no display until the end of run

--=========================================================================

PRINT 1

WAITFOR DELAY '00:00:5'

PRINT 1.5

WAITFOR DELAY '00:00:5'

PRINT 2

WAITFOR DELAY '00:00:5'

PRINT 3

GO

--=========================================================================

-- Raiserror forces the messages to be flushed as the run proceeds

--=========================================================================

PRINT 1

--======= Flush message 1 to client

RAISERROR ('After 1',10,1) WITH NOWAIT

WAITFOR DELAY '00:00:5'

PRINT 1.5

WAITFOR DELAY '00:00:5'

PRINT 2

--======= Flush waiting message(s) to client

-- This will send 1.5 and 2 to the client

RAISERROR ('After 2',10,1) WITH NOWAIT

WAITFOR DELAY '00:00:5'

PRINT 3

--===== Flush message 3 to client

RAISERROR ('After 3',10,1) WITH NOWAIT[/font]

--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.

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

Niall Cronin SSC-Addicted Points: 450 More actions · Answer 5

Niall Cronin

SSC-Addicted

Points: 450

July 10, 2008 at 12:47 am

#840651

Thanks Jeff.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

You bet...

--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.

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