Nocount On Problem
Jul 23, 2005
i use
set nocount on inside the stored procedure at the beginning however the
Number of Rows Counted/Affected shows up when I execute the stored
procedure in query analyzer using execute sprocName. I also tried to
add the
SET NOCOUNT ON at the beginning of the procedure and it still shows the
number of rows affected.
if i set
set nocount on
exec sprocName then the result set does not show the number of rows
affected.
Any idea why this happens? I know that NOCOUNT is set on runtime not
parse time.
Thanks!
View 3 Replies
ADVERTISEMENT
Sep 28, 2006
Is set nocount on or off by default if you dont type anything for set nocount??cheers!!
View 2 Replies
View Related
May 18, 2004
what does SET NOCOUNT ON do??
View 2 Replies
View Related
Aug 9, 2004
If I understood correctly from some reading SET NOCOUNT ON is use so that my sp does not return the number of row affected and this should improve greatly the performance of my sp. I should use if for all insert, delete adn update sp. Is this correct? Is there any situation I need to have it set to OFF?
What about if I only do a SELECT * FROM Table, is there any point to turn this ON?
Thanks.
View 1 Replies
View Related
Jan 8, 2001
I'm trying to write some stored procs. Can somebody help me?
What does "set nocount on" do? And when should I use it?
Thank you,
Anastasia
View 1 Replies
View Related
Aug 31, 2004
Hi, i have the next problem...
I work with:
- Sqlserver 2000
- Web server, Tomcat.
- JDBC.
- Java Aplication.
I notice that when I call a store procedure from the aplication java, I usually set no count on in my sp because of some exception errors in my aplication.
But I have one special version of a jdbc that it doesnt work with this parameter. I wish it could be a parameter of the database that is set by the jdbc, but i´m not sure.
Any idea?????
Thanks.
View 3 Replies
View Related
May 29, 2007
hi
in stored procedures i don't want to display message as
these many rows
eg: (1 row(s) returned)
for that i wrote
SET NOCOUNT ON
but also it displays
is their any ather solution
or else iam going in wrong way
Malathi Rao
View 10 Replies
View Related
Jul 12, 2006
Any one help me out ?
Thank you very much .!
View 2 Replies
View Related
Feb 19, 2008
I donot know how to use SQL NOCOUNT . I have written store procs for select and insert , Below are the store procs ,Can anyone tell me how to use SQL NOCOUNT in this StoreProcs???
Select:
if exists (select * from dbo.sysobjects where id = object_id('[dbo].[GetAllLinks]') )drop Procedure [dbo].[GetAllLinks]
GO
CREATE Procedure [dbo].[GetAllLinks]
AS
Select * from UsefulLink
Go
Insert Storeproc:
if exists (select * from dbo.sysobjects where id = object_id('[dbo].[InsertLink]') )drop Procedure [dbo].[InsertLink]
GOCREATE Procedure [dbo].[InsertLink]
@Title varchar(100),
@Location Varchar(100),@ID int OUTPUT
AS
Set @ID = -1Begin Transaction
Insert Into UsefulLink(Title, Location) Values
(@Title,@Location)if @@error <> 0
RollBack Transaction
Else
BEGIN
Commit Transaction
Select @ID = @@identity
END
Go
View 3 Replies
View Related
Mar 20, 2008
What I am Trying To Achieve
I am trying to get the ID of the row I am inserting - using code that works fine on: SQL Server 2000 with an ODBC connection and SQL Server 2005 with an ODBC connection
Problem
When the code is executed it inserts the row 4 times.
My Settings
I have the following OLE DB connection string:-
Provider=SQLOLEDB.1;User ID=myuserid;Initial Catalog=dbname;Data Source=dbserveraddress;User Id=myuserid;PASSWORD=mypassword;
and I am executing the following SQL:-
session("sessionID") = dbExecuteRS("SET NOCOUNT ON;INSERT INTO myTable (ipAddress) VALUES('"& request.serverVariables("REMOTE_HOST") &"');SELECT SCOPE_IDENTITY();SET NOCOUNT OFF;")(0)
dbExecuteRS = a function that executes a query
I know my asp page is only executing this sql once but the SQL Profiler says it is running multiple times.
What I've Tried So Far
1) A normal insert works fine - only inserts one row
2) The same SQL with an OLE DB connection string on SQL Server 2000 encounters the same problem - the insert sql with the set nocount on inserts 4 rows
3) I've tried using this bit of SQL/ASP which causes the same problem:-
set tmpRS = dbExecuteRS("SET NOCOUNT ON;INSERT INTO mytable (ipAddress) VALUES('"& request.serverVariables("REMOTE_HOST") &"');SELECT newID = SCOPE_IDENTITY();")
if not tmpRS.eof then
response.write("tmpRS('newID'): " & tmpRS("newID") & "<BR>")
end if
set tmpRS = nothing
Conclusion
From all of my tests I can conclude that this problem ONLY happens when we are connecting to the database via the OLE DB connection string and using an INSERT statement with SET NOCOUNT ON
All ideas/suggestions appreciated, thanks.
View 1 Replies
View Related
Jan 16, 2008
I have a SP that has a cursor, and loops. Each loop executes SQL, and inserts values into a temp table on each loop. I want to just have the values in the temp table to show up in QA not the loop's sql results.
I thought:
SET NOCOUNT ON
at the begining of my proc would suppress the results, and then
SET NOCOUNT OFF
prior to my select on my temp table would do this, but the resultset of the loop still shows up in QA. It does suppress the count values on the message tab of QA, but that is it.
I tried something simple like:
SET NOCOUNT ON;
GO
SELECT * FROM [Contact]
And it still show the results in the grid
Any thoughts?
ThanksEric
View 4 Replies
View Related
Jul 16, 2007
Hopefully simple question:
How to configure replication so that replication triggers issue a SET NOCOUNT ON?
Also: are there any negative consequences of adding a SET NOCOUNT ON to replication triggers?
I'm not a DBA so my experience with replication is pretty much non-existent. Hopefully, there's a simple configuration option that I can apply when setting up replication for a database. If not, is it going to be feasible for me to write a script that runs over the replication triggers/procs to add this SET statement (any pointers on inbuilt procs that would help here would be real useful)?
Thanks in advance
Christian
View 6 Replies
View Related
