We help IT Professionals succeed at work.

How do I run a stored procedure and ignore the returned output in Management Studio?

soozh
soozh asked
on
Hello,

I have written a stored procedure that returns a couple of columns of data.  This stored procedure is normally executed from my application and the returned information is important.

However I need to run it once on my table for each row (+400000) from Management Studio. I have tried to do this but the returned columns are displayed in the results pane each time the stored procedure is called.  Eventually Managment Studio crashes because of the large amount of data in the result pane.
 
Is there any way to execute a stored procedure and direct the output away from the results pane?  i.e. hide it or send it somewhere like a black hole?
Comment
Watch Question

Howdy

In management studio simple change the output method to text or file in the menu Query->Results to

Hope that helps
James-ct16 is almost right, but in your case you can go to Query Options>Results and check the "Discard results after execution" option. Just remember to uncheck it after you're finished otherwise you'll be in for a surprise when you get a blank results pane after executing any query!  :)
soozhCEO

Author

Commented:
Thanks for the replies.

Is there any way  to do either of these solutions in script?  So I could change the output, or discard results, before and after I execute my sp.

I just want to automate it as much as possible...
If you want to run it automatically outside the app and don't care about the returned results, then just create a SQL Server Agent job that calls the stored procedure and schedule the job to run as frequently as needed at the suitable time.
Just another thought, could you add an optional param to your stored proc which you could then use to suppress the output. Obviously you would default to output as then you dont have to change your app. After all if you are generating a large amount of data outputting it and the displaying it that a fair chunk of worthless work for the server.

Just an idea
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Do you know the output column types?
If you do, you can direct them to a temp table, then discard it.

create table #tmp (col1 int, col2 varchar(100), col3 decimal);
set nocount on;
insert #tmp exec myproc;
drop table #tmp;