[AccessD] ADP/SP problem

Add SET NOCOUNT ON to the beginning of the stored procedure and the Access
message should go away.

>From SQL Books Online:

SET NOCOUNT
Stops the message indicating the number of rows affected by a Transact-SQL
statement from being returned as part of the results.

Syntax
SET NOCOUNT { ON | OFF } 

Remarks
When SET NOCOUNT is ON, the count (indicating the number of rows affected by
a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the
count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client
for each statement in a stored procedure. When using the utilities provided
with MicrosoftR SQL ServerT to execute queries, the results prevent "nn rows
affected" from being displayed at the end Transact-SQL statements such as
SELECT, INSERT, UPDATE, and DELETE.

For stored procedures that contain several statements that do not return
much actual data, this can provide a significant performance boost because
network traffic is greatly reduced.

The setting of SET NOCOUNT is set at execute or run time and not at parse
time.

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com


-----Original Message-----
From: Michael Brosdorf [mailto:michael.broesdorf at web.de] 
Sent: Thursday, August 07, 2003 3:51 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] ADP/SP problem


Hello,

in my ADP-project (A2K) I have a (read-only) subform that is bound to a
stored procedure with an input parameter. The input parameter uses a textbox
on the main form. That works fine as long as the SP returns records.
However, if the SP does not return records, Access displays a message
telling me that the SP has been executed but did not return any records. I
click on OK and the same message reappears over and over again. I can only
end this by 'killing' access using the task manager of Windows.

How can I tell Access not to tell me that no records are returned by the SP?

TIA,

Michael

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com