Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server Query Suppress stored procedure reply

Posted on 2011-10-18
9
Medium Priority
?
242 Views
Last Modified: 2012-05-12
I have a stored procedure that is used for a LOT of pages and other stored procedure.
It loads a record into a table for an email queue.

I want to execute this stored procedure in a fetch statement
But the stored procedure returns the scope_identity()

So if I have 1200 records in my fetch...I get the 1200 replies during the fetch.

Any way to suppress that?
0
Comment
Question by:lrbrister
  • 3
  • 3
  • 3
9 Comments
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36987399
try adding SET NOCOUNT ON before the execute of the SP, this has to be done in the same connection etc though as its a per session setting


Thanks

Dave
0
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36987402
Sorry I meant SET NOCOUNT OFF

0
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36987407
Oh dear I'm having bad day!! I was right the first time! sorry for the mess around

SET NOCOUNT ON
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:lrbrister
ID: 36987430
David Morrison
Still getting the responses.

See my attached code...
-- Declare the variables to store the values returned by FETCH.
Declare @email varchar(50);

Declare email_cursor Cursor for
		select	u.user_name + '@mycompany.com' email
		from	crmprod_01.dbo.users u
				left join crmprod_01.dbo.users u2 ON u.reports_to_id = u2.id
		where	(u.status = 'Active' and u.department = 'Sales')
					and
				(u2.user_name in ('user1', 'user2) or u.user_name in ('user1', 'user2))		
		order by u.user_name;

Open email_cursor;
Fetch Next From email_cursor
Into @email;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
While @@FETCH_STATUS = 0
Begin

   -- Insert email
  SET NOCOUNT ON; 
   exec portal.dbo.sp_portal_EmailSchedulerInsert @email,'','','TestEmail',1,'TEST',null;

   -- This is executed as long as the previous fetch succeeds.
   Fetch Next From email_cursor
   Into @email;
End

Close		email_cursor;
Deallocate	email_cursor;

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 36987722
why don't you try like this (Moved it out from the while loop and also notice the SET NOCOUNT OFF at the end).

.....
SET NOCOUNT ON;

While @@FETCH_STATUS = 0
Begin

   -- Insert email
   exec portal.dbo.sp_portal_EmailSchedulerInsert @email,'','','TestEmail',1,'TEST',null;

   -- This is executed as long as the previous fetch succeeds.
   Fetch Next From email_cursor
   Into @email;
End

SET NOCOUNT OFF

Close            email_cursor;
Deallocate      email_cursor;
0
 

Author Comment

by:lrbrister
ID: 36987750
ralmada:...no go.  Still returning id (x 1200)
0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 36987756
if not you might want to check this other link

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23515369.html

basically if in your sp_portal_EmailSchedulerInsert stored procedure you have a select query include the select nocount on in there
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 2000 total points
ID: 36987788
btw, the correct coding for that answer should be: (semicolon was missing)

@query = 'set nocount on; select top......'
0
 

Author Closing Comment

by:lrbrister
ID: 36987795
Thanks for sticking with me.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question