intermediate results while "executing query.."

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

intermediate results while "executing query.."

Author

Topic

apnw7931
Starting Member

9 Posts

Posted - 2011-01-13 : 06:02:54

Hi everybody,

sometimes when i run (big) queries, it gives me intermediate results while it's still executing the query.

How does it happen and why ?

Because, it's sometimes good to see already results before the end of the query

phipywr
Starting Member

6 Posts

Posted - 2011-01-13 : 09:35:27

It's dependent on SQL Server, but if you always want to see results at stages within a script you can do this:
declare @msgstring varchar(100)
SET @msgString = '__to something_____'
RAISERROR (@msgString, 10,1) WITH NOWAIT

This will raise an informational message and cause whatever is in the buffer to be sent immediately without stopping the script from running.

Have an ordinary day

swansystem
Starting Member

2 Posts

Posted - 2011-01-13 : 10:44:49

Thank You to all!!! Work

apnw7931
Starting Member

9 Posts

Posted - 2011-01-14 : 03:38:02

phipywz,

i'm a beginner in SQL, so i do not know exactly how to use your code :-/
- Do i have to place it at the beginning of my query ?
- What do i have to write instead of "__to____something__" ?

Thank you

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2011-01-14 : 12:39:04

quote:
Originally posted by apnw7931

phipywz,

i'm a beginner in SQL, so i do not know exactly how to use your code :-/
- Do i have to place it at the beginning of my query ?
- What do i have to write instead of "__to____something__" ?

Thank you

If you place it at the beginning, there won't be any intermediate results for it to return.

You write whatever you want the informational message to be at the time the exception is raised (what you want your users to see).

phipywr
Starting Member

6 Posts

Posted - 2011-01-14 : 13:36:56

Perhaps a little longer example would be helpful

quote:

declare @msgstring varchar(100)

select *
from table1

SET @msgString = 'we have now selected table1'
RAISERROR (@msgString, 10,1) WITH NOWAIT

select *
From table2

SET @msgString = 'we have now selected table2'
RAISERROR (@msgString, 10,1) WITH NOWAIT

select a.columnA
,b.column1
from table1 a
inner join table2 b
on a.id = b.id

SET @msgString = 'we have now joined the results'
RAISERROR (@msgString, 10,1) WITH NOWAIT

without the RAISERROR....WITH NOWAIT showing up SQL server probably would not show any results for the 3 queries above until all three were complete. By throwing these in the middle it forces sql server to return the results with each query as soon as they are completed.

Have an ordinary day

phipywr
Starting Member

6 Posts

Posted - 2011-01-14 : 13:58:39

As I come back and reread the original request about the results coming back I think I'm answering the wrong question. I'm thinking about an extended script where you have multiple queries, whereas the original person was talking about results within an individual query.

The cause is related, to my answer, but I can't give you a real good answer on how or why you can get results within a single query have them start returning immediately.

Have an ordinary day

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources