March 24, 2006 at 12:38 pm
I'm totally new to sqlserver 2000 and I want o suppress the row agffected lines
I ran
while @@fetch_status = 0
begin
print 'spid: '+convert(char, @myspid)
DBCC INPUTBUFFER (@mySPID)
fetch next from curprocess into @mySPID
end
and I get
spid: 55
EventType Parameters EventInfo
--------- ---------- ------------------
RPC Event 0 dbo.getStudyList;1
(1 row(s) affected)
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
All I want is
spid: 55
EventType Parameters EventInfo
--------- ---------- ------------------
RPC Event 0 dbo.getStudyList;1
What I would really like is just
spid 55 : dbo.getStudyList;1
March 24, 2006 at 1:35 pm
Try putting
SET NOCOUNT ON at the head of your script but be warned, it doesn't suppress the message in all cases.
It IS consistent, but I have found that when using table variables it doesn't always get suppressed.
The other thing to watch out for is in .NET. If you use ExecuteNonQuery this returns the value of the rows affected but putting SET NOCOUNT on will result in this being zero.
Using RETURN @@ROWCOUNT means that the parameter @RETURN_VALUE will have this value instead.
March 24, 2006 at 2:29 pm
Thanks for the suggestion
here is my script
alter procedure JFM_process
as
set nocount on
declare @mySPID int ,
@myPrint varchar(200)
declare curProcess cursor
for
select spid from master.dbo.sysprocesses
where loginame like 'sa' and spid = 55
open curProcess
fetch next from curProcess into @mySPID
while @@fetch_status = 0
begin
print 'spid: '+convert(char, @myspid)
DBCC INPUTBUFFER (@mySPID)
fetch next from curprocess into @mySPID
end
After I rerun the sp it still is appearing. I comment out the dbcc inputbuffer and the rows affectged are suppressed. Weird and thanks again for your input
March 25, 2006 at 1:48 am
try:
alter procedure JFM_process
as
begin
set nocount on
declare @myspid int
create table #inputbuf
(
EventType varchar(15),
Parameters int,
EventInfo varchar(255)
)
declare @cmd varchar(200)
declare curProcess insensitive cursor
for
select spid from master.dbo.sysprocesses
where spid > 50 --exclude system processes
open curProcess
fetch next from curProcess into @mySPID
while @@fetch_status = 0
begin
select @cmd = 'dbcc inputbuffer (' + convert(varchar, @myspid) + ') with no_infomsgs'
insert into #inputbuf exec (@cmd)
select replace(replace(convert(varchar, @myspid) + ': ' + EventInfo, char(13), ''), char(10), ' ') from #inputbuf where Eventinfo is not null
delete #inputbuf
fetch next from curprocess into @myspid
end
close curProcess
deallocate curProcess
drop table #inputbuf
end
go
To turn off column headers in Query Analyzer (in Results to Text mode at least), go to Tools, Options, Results and uncheck: Print column headers
March 27, 2006 at 12:15 pm
Thanks that worked like a charm. I appreciate the input
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply