views:

3677

answers:

7

I'm not talking about doing a "SET NOCOUNT OFF". But I have a stored procedure which I use to insert some data into some tables. This procedure creates a xml response string, well let me give you an example:

CREATE PROCEDURE [dbo].[insertSomeData] (@myParam int) AS
DECLARE @reply varchar(2048)

... Do a bunch of inserts/updates...

SET @reply = '<xml><big /><outputs /></xml>'
SELECT @reply
GO

So I put together a script which uses this SP a bunch of times, and the xml "output" is getting to be too much (it's crashed my box once already).

Is there a way to suppress or redirect the output generated from this stored procedure? I don't think that modifying this stored procedure is an option.

thanks.


I guess i should clarify. This SP above is being called by a T-SQL Update script that i wrote, to be run through enterprise studio manager, etc.

And it's not the most elegant SQL i've ever written either (some psuedo-sql):

WHILE unprocessedRecordsLeft
  BEGIN
    SELECT top 1 record from updateTable where Processed = 0
    EXEC insertSomeData @param = record_From_UpdateTable
  END

So lets say the UpdateTable has some 50k records in it. That SP gets called 50k times, writing 50k xml strings to the output window. It didn't bring the sql server to a stop, just my client app (sql server management studio).

+1  A: 

I don't know if SQL Server has an option to suppress output (I don't think it does), but the SQL Query Analyzer has an option (under results tab) to "Discard Results".

Are you running this through isql?

MikeW
A: 

You could create a SQL CLR stored procedure that execs this. Should be pretty easy.

Hafthor
SQL CLR is new to me, but i am looking into it now, this might be promising.
Dave
A: 

You said your server is crashing. What is crashing the application that consumes the output of this SQL or SQL Server itself (assuming SQL Server).

If you are using .Net Framework application to call the stored procedure then take a look at SQLCommand.ExecuteNonQuery. This just executes stored procedure with no results returned. If problem is at SQL Server level then you are going to have to do something different (i.e. change the stored procedure).

JD
+1  A: 

Man, this is seriously a case of a computer doing what you told it to do instead of what you wanted it to do.

If you don't want it to return results, then don't ask it to return results. Refactor that stored procedure into two:

CREATE PROCEDURE [dbo].[insertSomeData] (@myParam int) AS
BEGIN
DECLARE @reply varchar(2048)

--... Do a bunch of inserts/updates...

EXEC SelectOutput
END
GO

CREATE PROCEDURE SelectOutput AS
BEGIN
SET @reply = '<xml><big /><outputs /></xml>'
SELECT @reply
END
John Saunders
I do agree with you there. If this was something i was allowed to modify, i would split up the stored proc, or append a parameter to not display output, etc.
Dave
A: 

From which client are you calling the stored procedure? Say it was from C#, and you're calling it like:

var com = myConnection.CreateCommand();
com.CommandText = "exec insertSomeData 1";
var read = com.ExecuteReader();

This will not yet retrieve the result from the server; you have to call Read() for that:

read.Read();
var myBigString = read[0].ToString();

So if you don't call Read, the XML won't leave the Sql Server. You can even call the procedure with ExecuteNonQuery:

var com = myConnection.CreateCommand();
com.CommandText = "exec insertSomeData 1";
com.ExecuteNonQuery();

Here the client won't even ask for the result of the select.

Andomar
+2  A: 

I think I found a solution.

So what i can do now in my SQL script is something like this (sql-psuedo code):

create table #tmp(xmlReply varchar(2048))
while not_done
  begin
    select top 1 record from updateTable where processed = 0
    insert into #tmp exec insertSomeData @param=record
  end
drop table #tmp

Now if there was a even more efficient way to do this. Does SQL Server have something similar to /dev/null? A null table or something?

Dave
@Dave: Perhaps you've declared @record and you meant to select into @record? In any case, you should look at OUTER APPLY, since it looks like you want to insert into the #tmp table the resultset returned from executing the insertSomeData SP passing as a parameter the "record" column from the next unprocessed row in updatetable.
John Saunders
I'm not familiar with the APPLY's, but it doesn't look like it's what i want. This sp is used by other processes, but in my case i don't want the output it gives me. But dumping the result into a temp table i can avoid it being outputted to the screen.
Dave
@Dave: Maybe your pseudo-code is not accurate, but I don't see how the output of the select will even be used in the insert. If it's the select output that you want to suppress, then maybe that's where the problem is: maybe it's not meant to be returning a result set. Maybe that's simply a bug.
John Saunders
'select top 1 record from updateTable...' gets the next record to process. However in the SP insertSomeData, it performs a select statement which prints out a xml string, that's what i'm wanting to suppress. I've found by doing a 'insert into #tmp exec SP' that the output that used to be printed to the screen, will be dumped into a table, which i can then drop, because i don't care about that output.
Dave
A: 

ever tried "SET NOCOUNT ON;" as an option?

plozi