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.

------------------------------------------------------------------------
0
tim
6/15/2001 3:02:16 PM
perl.dbi.users 11099 articles. 1 followers. Follow

0 Replies
416 Views

Similar Articles

[PageSpeed] 28

Reply:

Similar Artilces:

RE: Warning for those using Cursors in Microsoft SqlServer with O DBC or ADO
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: tim.wilson@barclayscapital.com [mailto:tim.wilson@barclayscapital.com] Sent: Friday, June 15, 2001 11:02 AM To: dbi-users@perl.org 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 ...

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to
Hello all, I'm a newbie in ASP. I wrote a C# app to access an MS Access database placed in the c:\inetpub\wwroots\databases folder. Everything works fine. When I am trying to access the same database through my ASP web application, I keep on getting: "ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.\r\nERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed\r\nERROR [HY000] [Microsof...

Is it possible to use SQLServer Profile Provider if roles and users are using ActiveDirectory Provider?
We are using the built in membership and roles provider that uses Active Directory. Can we use the default SQL Server Profile Provider with above providers? I think it may not be possible since the profile tables need to relate to users table in the database schema of SQL Server Profile Provider?sun21170 Yes you can use it together with the AD provider. The providers don't need to work agianst the same data source to work./Fredrik Normén - fredrikn @ twitterMicrosoft MVP, MCSD, MCAD, MCTASPInsidersMy Blog...

how to use ref cursor to retrieve dataset from oracle using Microsoft data application block?
I can not figue it out, and I can not find any document regarding this. thanks in advance. If you haven't modified the enterprise library on your own, it creates a default cursor called 'cur_out' and automatically appends it to your parameter list. See:         /// <devdoc>         /// This is a private method that will build the Oracle package name if your stored procedure         /// has proper prefix and postfix.         /// This fun...

DBD::ODBC 1.14 does not work on Windows x64 using Microsoft ODBC drivers
I have Perl 5.8.8 built for Windows x64 from Activestate (version 822) installed on a Windows 2003 x64 bit server (Xeon processor). I have installed version 1.59 of DBI and version 1.14 of DBD::ODBC compiled for x64 bit, using the freely available Microsoft SDK. The ODBC-ODBC bridge used is supposed to be for 64 bit, as described by Microsoft. I ran the attached test script and get an error: DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid string or buffer length (SQL-HY090)(DBD: st_execute/SQLExecute err=-1) at test_dbd-odbc.pl line 48. This seems to ...

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.
Hello, I am a Newbie to ASP.Net 2.0 I have table named Tests in MS Access, consisting of 2 columns: id (Autonumber, ReplicationID,Primary Key) testcolumn (Text,255) I get following error: ----------------------------------------------- ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.Odbc.OdbcException: ...

HELP PLEASE! Problems using returning a cursor to DBI & DBD::Oracle using Stored Procedures
Tim, Oracle 8.1.7 DBI 1.28 DBD::Oracle 1.12 I hope this email does not irriatate you but I really need some help. I = have an Oracle DBA I need to work with who doesn't seem to how to write = PL/SQL for Perl DBI. He insists on writting stored procedures which loop = through the results set =20 fetching the data into output variables. The problem is I can't seem to = figure out how to get all the data from it. I found an example of an oracle FUNCTION returning a cursor which = allowed me to fetch the data from Perl. Unfortanely, the DBA is = complaining that the return...

Warning: [10039] Error detected while using multi-row cursor -- retrying with single row cursor
Hi, We are using Sybase 8.0.2.4511 and Mobilink Synchronization. Using pocket pc with sybase 8.0.2.4505 we are synching to main server. I am getting this following message though it is a warning message. I just want to get rid of this message. Can any one briefly explain this warning. Warning: [10039] Error detected while using multi-row cursor -- retrying with single row cursor It is affecteing my logic. Thanks. Chandhu. That's a common warning; why is it affecting your logic? On 18 Apr 2005 05:37:08 -0700, "Chandhu" <chandrasekhars@i-vantage....

When using MS-ADO to connect to ASE using ASE OLEDB/ODBC driver the record count is -1
Hi VB 6 application connecting to ASE 12.5 (NT) using OLEDB/ODBC drivers. Gets a problem. He retrieve rows from the database - WORKS Fine!!! But as soon as he wants to see how many rows was returned the recordcount returns -1. He gets this problem connecting using ODBC and OLEDB. He gets the recordcount from the recordset object's recordcount property. This is what I found on the MSDN online manuals: RecordCount Property - ADO Indicates the current number of records in a Recordset object. Applies To Recordset Remarks Use the RecordCount property to find out how many r...

DBI microsoft sqlserver driver
--Boundary_(ID_alIsriBDkYxcI1/a0IXdkw) Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: 7BIT I posted a week or so ago about the differences between MySQL and MS-sqlserver because I'm going to move an app from the former to the latter. I got good feedback and a suggestion to look into the Perl DBI book by Descartes and Bunce, which I did. In there I read that MS-sqlserver does not have its own driver, but relies upon a Sybase driver and that I have to do some fancy work to use Free TDS libraries, which I'm not very enthusiastic about. Worse, from my...

Remote Login, using dbi:ODBC
------=_NextPart_000_0043_01C5D771.B9D90110 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I login to my remote mysql databases, using perl, this way: sub ConnectToDB { my $host_name = "01.002.003.04";# Ip Address Here my $db_name = "DataBaseName"; # Database Name to connect to my $dsn = "DBI:mysql:host=$host_name;database=$db_name"; return (DBI->connect ($dsn, "usernameHere", "PasswordHere", {PrintError =>...

Demo for using SQLITE with ADO and ODBC
This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --JivePart=_61af3.zeB8CwDOH5aMJzMl Content-Type: text/plain; charset="Utf-8" This is a simple test case for the SQLITE ODBC driver and it also demonstrates how to use SQLITE with ADO. The ODBC driver can be downloaded at: www.ch-werner.de/sqliteodbc/ Note: you might want to checkout the open source tBetterAdoDataset component as well, it fixes some issues that tAdoDataset has with identity fields. Feel free to use this source c...

difference between using ADO.NET and ODBC
what is the difference between using ADO.NET and using ODBC. thank for your time ADO.NET is an object library for data access. It provides different types of data providers to work with different data sources. ODBC is a standard database access method. ADO.NET can use an ODBC data provider to access an ODBC-compliant data source. Terri Terri MortonEngagement Manager, NeudesicHow to ask a question thank you for your answer but waht do you mean by data source and thank for you time,because iam not profssional in these things Below Two URL's give you clear idea for your qu...

Using ADO/SQLServer for MDI application
Hi, I am just starting a new project using Delphi 7 and SQL Server 2005. Up until now I have been using paradox as my db in SDI applications. Can anybody suggest a good book to get me started, and any other information resources that may be available? Maggie Maggie Clark wrote: > Hi, > > I am just starting a new project using Delphi 7 and SQL Server 2005. Up until now I have been using paradox as my db in SDI applications. Can anybody suggest a good book to get me started, and any other information resources that may be available? > > Maggie Hi Maggie, I...

Web resources about - Warning for those using Cursors in Microsoft SqlServer with ODBC or ADO - perl.dbi.users

Microsoft - Wikipedia, the free encyclopedia
Albuquerque , New Mexico , U.S. (April 4, 1975 ( 1975-04-04 ) ) Microsoft Redmond Campus , Redmond, Washington , U.S. is an American multinational ...

Microsoft Corporation
Visit Microsoft Australia to download themes, find a new PC, upgrade Windows and much more.

Microsoft Canada - Software - Advertising - Phones - IT - Cloud
Microsoft Canada is a world leader in software, services, partner resources and Internet technologies. Get product information, support, and ...

Free Microsoft Points - Free Microsoft Points
Get Free Microsoft Points, Free 1400 Microsoft Points, Free 2100 Microsoft Points codes emailed to you for Free. Yes, All for Free.


Microsoft Bob - Just a short, simple blog for Bob to share some tips and tricks.
Just a short, simple blog for Bob to share some tips and tricks.

Microsoft New England Research and Development Center
The Microsoft New England Research & Development Center is a research and software innovation campus located in the heart of Cambridge, Massachusetts. ...

Jobs at Microsoft - Explore Microsoft Jobs and Join our Talent Community - Apply for Microsoft Jobs Online ...
Search for Jobs at Microsoft here. Explore worldwide Microsoft jobs, including game design jobs, developer jobs, software sales jobs, marketing ...

Microsoft Cloud Partner - Home
... your existing Internal Use Right through June 2013 while you work towards earning Cloud Accelerate status. Note: If you intend to sell Microsoft ...

Microsoft Hohm Service Discontinuation
Microsoft-Hohm discontinuation message

Resources last updated: 1/12/2016 2:56:37 AM