Additionally, use SET NOCOUNT ON if you plan on using temp tables inside stored procedures! Basically what this option (NOCOUNT) does is turn off the message returned at the end of each statement that states how many rows were affected by the statement. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, June 15, 2001 11:02 AM To: [EMAIL PROTECTED] Subject: Warning for those using Cursors in Microsoft SqlServer with ODBC or ADO Hi all Just thought I'd let you know about a gotcha when using ODBC (and ADO I think) to connect to Microsoft Sqlserver. I now highly recommend having: SET NOCOUNT ON as the first statement in all your stored procedures. I had a cursor inside a stored procedure that ran correctly from isqlw but would not run correctly when called from ODBC. I thought the problem may be something to do with DBI but thankfully it is not. Unfortunately DBI does not pick up the error message (if any ) that is generated. The Cursor would fail after a fixed number of rows , any changes to the proc would seem to randomly alter number of rows before it would fail. Using the above setting stops SQL returning all the result sets that it generates for any INSERT, UPDATE,DELETE or SELECT. It is the number of rows effected by the action. eg (1 row effected) Adding the above SET statement fixed the problem. regards Tim ------------------------------------------------------------------------ For more information about Barclays Capital, please visit our web site at http://www.barcap.com. Internet communications are not secure and therefore the Barclays Group does not accept legal responsibility for the contents of this message. Although the Barclays Group operates anti-virus programmes, it does not accept responsibility for any damage whatsoever that is caused by viruses being passed. Any views or opinions presented are solely those of the author and do not necessarily represent those of the Barclays Group. Replies to this email may be monitored by the Barclays Group for operational or business reasons. ------------------------------------------------------------------------ ******************************************************** This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free. The sender therefore does not accept liability for any errors or omissions in the contents of this message that arise as a result of e-mail transmission. This message is provided for informational purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments. All e-mails at Neuberger Berman are, in accordance with Firm policy, to be used for Neuberger Berman business purposes only. E-mails sent from or to the Firm are subject to being reviewed by the Firm in accordance with the Firm's procedure for the review of correspondence.
