SQL Server ODBC Drivers problem
John Bair
>We are experiencing an intermittent problem when trying to open more than
>one snapshot in VB ( VB3). The message 'CONNECTION IS BUSY WITH THE
>RESULTS OF ANOTHER HSMST' is given.
>The current drivers we are using are:-
>Server: SQLSRV32.DLL V2.50.0126 dated 7/8/95
>Cleint: SQLSRVR.DLL V2.50.0121 dated 15/8/96
>Does anyone have ant suggestions/recommendations as to why this is
>happening and how to get round it.>
[snip]
According to MS ODBC driver documentation:
"The SQL Server driver can have only one active hstmt unless it is
used with server cursors; it returns this information through
SQLGetInfo with the SQL_ACTIVE_STATEMENTS option. An hstmt is defined
as active if it has results pending. In this context, results are any
information returned by SQL Server, such as a result set or a count of
the rows affected by an UPDATE statement.
Note An hstmt's activity is not related to its state. For example, if
a SELECT statement is executed and it doesn't return any rows, the
statement is not active, since no results are pending. However, before
the statement can be executed again, the cursor associated with it
must be closed with SQLFreeStmt.
Server cursors allow multiple active statements on a single connection
when connected to SQL Server 6.0.
When connected to SQL Server version 4.2x, the SQL Server driver
supports only one active statement per connection. The cursor library
(shipped with the SQL Server driver) allows applications to use
multiple active statements on a connection."
I'm not that familiar with VB, but in C you would call
SQLSetConnectOption(SQL_ODBC_CURSORS, SQL_CUR_USE_DRIVER) to remedy
the problem. Can you change the type of cursor you are using in the
properties of your control?
Rob Towne
Dave Eaton <eat...@internet-pilot.uk-po.com> wrote:
>We are experiencing an intermittent problem when trying to open more than
>one snapshot in VB ( VB3). The message 'CONNECTION IS BUSY WITH THE
>RESULTS OF ANOTHER HSMST' is given.
>
Our DBA reminded me of this one the other day when I got this message.
In VB, if your setting this SQL statement to say, a snapshot w/passthrough,
before referencing the snapshot do a snap.movelast (then a movefirst if you
need the 1rst row). I know this sounds backwards, but it only happens when
you try to open a 2nd snapshot/dataset where the buffer isn't
clear from the first.
It takes a bit of time if its a large dataset, but it works. Hopefully this
is it and not any driver problems. You can do a 'resume' once you hit this
error to get out of the error handling and back up to the passthrough, then
set the code to a new line that states 'snap.movelast' then hits the
passthrough line again to test.
HTH,
Robert
/\/\/\/\/\/\/\/\/\
|Robert Towne |
|rto...@scsn.net |
\/\/\/\/\/\/\/\/\/
Dave Eaton
one snapshot in VB ( VB3). The message 'CONNECTION IS BUSY WITH THE
RESULTS OF ANOTHER HSMST' is given.
The current drivers we are using are:-
Server: SQLSRV32.DLL V2.50.0126 dated 7/8/95
Cleint: SQLSRVR.DLL V2.50.0121 dated 15/8/96
Does anyone have ant suggestions/recommendations as to why this is
happening and how to get round it.>
> --------------------------------------------------------------------
>
> Newsgroup: comp.databases.ms-sqlserver
>
> * Re: SQL 6.0 Transfer Manager doesn't transfer Constraints. -
> Timmy Whelan (23)
> * What development platform to use?
> o Jon Gupta (10)
> + Trevor Best (18)
> o David Brabant [SNI] (13)
> o "D. Painter" (18)
> + Steve Krauss (4)
> + Phil Grisier (18)
> + Rune Moberg (21)
> + Lee Marshall (31)
> + Rune Moberg (11)
> + Phil Grisier (19)
> + Randy Magruder (22)
> + Phil Grisier (30)
> o Bob Crawford (12)
> o Bob Crawford (23)
> o Bill (23)
> + Phil Grisier (32)
> o Rune Moberg (22)
> + Dave Hegemann (29)
> + ar...@ix.netcom.com (18)
> o Leo Singh (54)
> o Pete Cresswell (15)
> + Tony Scandora 708-252-7541 (12)
> + Phil Grisier (14)
> * Re: Help: BCP from Access 2.0 - Mark Pickavance (33)
> * BCP GUI ?? - CJ Yang (12)
> o CJ Yang (24)
> o Jeff Fudge (24)
> * ROWID from Gupta SqlBase on Microsoft SqlServer? - Herbert
> Lederer (24)
> o Rob Docherty (20)
> * SQL question: count groups
> o Graham Blunn (47)
> o Shaun MacDonald (51)
> o John Prosser (4)
> * Re: Creating Trigers - Trevor Andrew - TA & Associates P/L (44)
> * bcp -- what a mess !
> o Larry Magiera (36)
> o Jeremy H. Griffith (35)
> * Error with primary key on autonumbered field - Petr Cervenka
> (22)
> o MJPinker (6)
> * Compuserve, MSN, SQL Server - Dick Burger (15)
> * SQL Server 6.0 on NT Workstation
> o Patrick Schlaepfer (30)
> + Jamckinney (6)
> o Bennett W. McEwan (8)
> * ODBC & Delete from - Patrick Schlaepfer (17)
> * DDCS/NT, SQL-SERVER and DB2/MVS - dch...@platinum.com.hk (16)
> * System Development Survey - David Fulton (499)
> * Re: the uniqueness of the timestamp data type in MS SQL 6.0 -
> Les Gainous (37)
> * 1920-char limit on stored procs? - tcam...@dynix.com.au (23)
> * Re: SQL question: count groups - Brian Wisniewski (43)
> * Where Are 4.21a Service Packs? - Steve Gray (12)
> * Script over 64 pages - Won't Run - Ell (10)
> * Re: Anyone played with tempdb in ram??? - "Simon Walker" (35)
> * Re: Report Writer - Shaun MacDonald (26)
> o Mark Eaton (49)
> * MS SQL Server to Sybase 10 or vice versa - Barry Smith (5)
> o "Simon Walker" (13)
> * Leap Year Scramble??? - "C. Yee" (5)
> o "Terry J. Liberty" (17)
> + Raymond Glassmeyer (33)
> o Doug Crist (19)
> * Re: What development platform to use? - Carlos Iván Chávez
> Fuentes (17)
> o Ian Kaw (6)
> + Daniel Bush (21)
> + Bradley Parker Webb (14)
> * Re: What development platform to use? - Carlos Iván Chávez
> Fuentes (29)
> o Phil Grisier (20)
> + "H.L.Arledge" (30)
> o Thomas Tomiczek (19)
> + Scheffler (6)
> + Mark Derricutt (15)
> o James Collins (11)
> + Trevor Best (17)
> + Sundial Services (18)
> + Jason Kaczor (49)
> + Greg Blumers (12)
> + rlwad...@phelpsd.com (13)
> + Jon Gupta (3)
> + Rune Moberg (18)
> + Phil Grisier (15)
> + Trevor Best (24)
> + James Collins (10)
> + Rune Moberg (15)
> + "D. Painter" (28)
> + Michael F. Huettemann (32)
> * Re: SIMPLE SQL QUESTION - Dave Nielsen (7)
> * Trapping SQL sent through SQL Server client?
> o Brian Engler (6)
> o Mark Stockton (10)
> o Thomas A. Moreau (8)
> o Tim Nichols (31)
> * Re: SIMPLE SQL QUESTION - Brian Wisniewski (39)
> * New Professional Association for Developers - Jim Schamadan
> (18)
> * Connecting with NT Client - Daniel Brochu (8)
> * Using SQL DISTINCT, MS-ACCESS - Dylan NICHOLSON (52)
> * Re: Trigger to update another table - "Michael D. Moore" (29)
> * Re: ODBC error 3146 suddenly happening - john (14)
> * HELP:SQL 6.0 / Foreign Keys / DRI - john (17)
> o Eddy Setiawan (21)
> o "Terry J. Liberty" (18)
> o Raymond Glassmeyer (25)
> o Jeremy H. Griffith (30)
> * Re: Looking for Accees to SQL upsizing wizard - Mark Stockton
> (11)
> * Audit Logging Using Triggers - Rob Docherty (23)
> o Kirill Seitkhalilov (12)
> o "Simon Walker" (41)
> + Rob Docherty (36)
> + "Simon Walker" (28)
> + Mikko Rasanen (27)
> * RE: Counter field in SQL-Server 6.0 - Keith Elmore (4)
> * RE: the uniqueness of the timestamp data type in MS SQL 6.0 -
> Keith Elmore (3)
> * RE: Auto commit in 4.21? - Keith Elmore (9)
> * SQLServer 6.0 with SQLWindows 5.0.2 - Daniel Roth (17)
> * USER_NAME() returns NULL - Rob Docherty (19)
> o "Simon Walker" (33)
> * Fast 16-Bit ODBC Driver to SQL Server 6.0 -
> tom...@teleport.com (19)
> * recovering - Hemant Upadhyay (20)
> o Srini Nimmagadda (63)
> * Embedded 'C' for SQL server? - "Timothy L. Dry" (9)
> o Ramon Carlos (20)
> + Rick Brown (28)
> * Problems running SQL client - Shourav Udas (23)
> o Scott Mecimore (11)
> * SQL to HTML - Gary O. Stenstrom (24)
> o Sean Mathias (37)
> * User Profiles in 6.0 - Srini Nimmagadda (30)
> * Re: ? NT/WWW/SQLserver <---> MVS/DB2 ? - Gene Kligerman (58)
> * SQL Magazines - Constantinos Parpas (4)
> o Cleaver Barnes (10)
> * Mac connection to SQL 6.0 - Peter Boyland (11)
> * Re: Join problems (is normialization bad?) - Cleaver Barnes
> (88)
> * List servers for ms sql? - Ivor O'Connor (6)
> o Joe Clifford (2)
> * ODBC Problems under Win95/NT using Visual FoxPro - Andy Blight
> (35)
> * ODBC drivers for Mac - Risto Komppa (10)
> o Todd M Bender (13)
> * SQL Server Error: 53 - Ivan Wang (28)
> o Joshua Guttman (17)
> + Ivan Wang (17)
> + Joshua Guttman (11)
> + Ivan Wang (17)
> + Ivan Wang (17)
> * SQL Server backup strategy. - Eddy Setiawan (34)
> * Re: SQL Server and WWW - Ferenc Mathe (21)
> o Joe Clifford (2)
> * Waitfor Syntax? - Ell (8)
> * Re: SQL Books - Hemant Upadhyay (34)
> * Access To SQL Upsizing Tool in MSDN - Dorian (6)
> o Rick Brown (14)
> * Tape Drive Contention - Doug Crist (7)
> * Can anyone help? Error number 614 - Kent Rubin (40)
> * Extracting all of the text from a text column - Johan Wåhlin
> (20)
> * ODBC-->Internet-->SQLSrv 6.0? - Bryan Field-Elliot (8)
> o Bjarne Hansen (22)
> * TCP/IP Programming with SQL Server - Curtis Paris (15)
> o "Simon Walker" (36)
> * Enterprise Manager - Can it notify of connection failure? -
> Doug Crist (3)
> * Error 703 Help - Lance Holland (17)
> o Lynne Warner (14)
> + Thomas A. Moreau (8)
> + Lance Holland (19)
> o Andy Ball (14)
> * Scheduler Leap Year Bug? - Raymond Glassmeyer (38)
> * Need Wizard Opinion r.e. code written for SQL & Unix -
> RayneWaters Studio Arts (20)
> * Re: replication product search/help - Scott Jerome-Parks (23)
> * Need recommendation for a Windows and Mac Cross platform RDBMS
> using Netware 4.1 - Julian Eccli (11)
> o Julian (18)
> + Joe Clifford (13)
> + itrc (23)
> + Joseph Buhl (17)
> + Steve Gutterman (20)
> + cgoulden (18)
> + Michael Leung (40)
> + Jeff Bernier (23)
> + Eric Rustomji (8)
> + Steve Shiflett (10)
> + Bill Gfroehrer (12)
> + Charles Wannop (8)
> + Dave Ballard (29)
> o Gord Thompson (21)
> o Carl Doering (24)
> o Johan Braun (7)
> * Multi-user/Single LoginID - Yan Li (22)
> o Martin Herbener, mher...@carleton.edu (49)
> * US-UT ** NEED SYSTEMS ANALYST/VB/SQL ASAP *** PAYS$$$!!! **** -
> ASamuels (80)
> * US-NV ** NEED TESTING QA LEAD ASAP *** PAYS$$$!!! **** -
> ASamuels (91)
> * ODBC SQLTables failure - Patrick Graydon (11)
> * Integration with SNA server??? - Richard Greaves (9)
> o Jeff Fudge (33)
> * Engineers Needed - Dan Allen (30)
> * STRANGE BEHAVIOR - Carlos Palomino (17)
> * Re: ODBC Connectivity Error - Carlos Palomino (18)
> * Anybody got experience with MS-SQL-Server, SolarNet/PC-NFS and
> ODBC. - Johan Gustav Bellika (14)
> o David Upham (23)
> * HELP:Restore database to a specific time - Deepak Sharma (24)
> o Eddy Setiawan (32)
> o "Terry J. Liberty" (38)
> o Andy Ball (9)
> * What does "Row compare failure" mean? - Anthony Faull (13)
> * (no subject) - Martin Mittendorfer (40)
> o Jeremy H. Griffith (260)
> * Magazine list - Constantinos Parpas (5)
> * Cursors that select from a sp. - Mark Donahoe (38)
> * DRI vs Triggers, any performance considerations? - Lynne Warner
> (13)
> o "Terry J. Liberty" (31)
> * Re: PRODUCT: Cold Fusion 1.5 ships; Web Application Development
> Platform - "Simon Walker" (16)
> * Re: Auto commit in 4.21? - E Raubenheimer (11)
> * Re: batch updates question - E Raubenheimer (20)
> * Returning Identities in SQL6 - EFTPOS New Zealand (26)
> * Returning Identities in SQL6 - EFTPOS New Zealand (26)
> * Borland C++ and 32 bit dblib - Kent Kingsford (14)
> * Question about ROWCOUNT - SLovin (14)
> * Q: IIS query - same names of various fields - Leonid Iakovlev
> (25)
> o Sandro Franchi (37)
> * SQL Replication - davc...@voicenet.com (10)
> * Net-library for PC-NFS and ODBC? - Johan Gustav Bellika (14)
> * MS SQL 32-bit DB-Library compatibility layer - Henry Nowicki
> (17)
> o David Upham (42)
> o Patrick TOURNAY (7)
> * VB 3.0 ODBC API Code Wanted - John M. King (14)
> * SQL Server 6.0 and Blobs - John M. King (6)
> * [Q]: Literature MS-Access / SQL-Server 6.0 ?? - Tspscho (10)
> * Why is MS's Hierarchy Expansion Limited to 794 lines? - Steve
> Schneider (20)
> o "Simon Walker" (41)
> * Re: Tools 4.21 & Windows 95 - Johan Stander (27)
> * MS SQL Client for Unix ? - Vidar Knudsen (6)
> o David Upham (19)
> + Greg Blumers (33)
> + Ernst Hoefer (10)
> * OLE Object in MS Access - Fatima Leitao (12)
> * Re: CAN PEOPLESOFT DELIVER? - Damian Yenzi (15)
> o Stephanie Ruby (63)
> * Re: SQL P... and the Like? - SQL Coder - Craig S. Mullins (35)
> * DBLibrary error resulting in Netscape Commerce Server Crash -
> b...@netcarta.com (42)
> * Tape Reading/Conversion Software Opinions Wanted - Ken Linger
> (27)
> * t-sql programming books - James Yin (8)
> * HELP: Unix (Linux) C/C++ to MS SQL server - Mike Berry-Porter
> (19)
> o "Simon Walker" (28)
> * Re: DSS and End User Ad-Hoc query tools wanted - Craig S.
> Mullins (41)
> * couple of sql server q's - pa...@fs.com (21)
> * Connecting To SQL server with AT - Travis L. Reno (10)
> o Rick Brown (34)
> o Patrick TOURNAY (9)
> * Re: what sql standard does sql-server 6.0 meet ? - Mike Hodgson
> (16)
> o c (11)
> o Ernst Hoefer (24)
> * Is is possible to attach tables using ODBC in SQL Server? How?
> - Zach Smith (6)
> o Martin Herbener, mher...@carleton.edu (22)
> + Zach Smith (8)
> * Net Protocol - Rich Moldovan (13)
> o David Upham (36)
> * SQL6.0 taking up HUGE amt of RAM! - Bryan Field-Elliot (33)
> o Ernst Hoefer (20)
> o Lasse Frisk (29)
> * *** US-FL-Tampa- P/A's w/ Access, SQL Server, & NT *** - Joe B
> (22)
> * DB-Libarary clashes with overlapped named-pipes ? - Gary
> Chapman (32)
> * WWW & SQL communication on NT - dudu (19)
> * Restoring a Table from a Database Dump - (9)
> o David Upham (29)
> o Andy Ball (13)
> * comparison ORACLE // SQL server - ha...@pi-user.pi.net (6)
> * PC-SQLserver to VAX-SQL server? - Henk.Bl...@pi.net (12)
> o Sylvain Faust (30)
> * Re: MS SQLserver // Atlanta - Les Gainous (44)
> * Re: BCP Load Probelm - Sysop (10)
> * Need Programmer IMMEDIATELY for Hollywood Project - Thomas Bond
> (32)
> * Need help with database layout - Matthew Andersen (22)
> * Stored Procedure Performance - David Hoefner (12)
> o Rick Brown (39)
> o Brian Jay Gould (30)
> * Simultaneous ODBC connections to the same source - Kenneth
> Falck (13)
> * Re: Stored procedure returning item list? - David Upham (33)
> * Re: DB size and performance ? - griff (5)
> o Trevor Best (23)
> + JohnEmcRx (7)
> + Sundial Services (37)
> o dennis Redfield (9)
> + Steven R Melton (9)
> + glassb (26)
> + Brett Weaver (14)
> * WebSite + SQL server - Wayne Gan (7)
> * Concurrency control in sql server programming - lmr (7)
> o Patrick TOURNAY (5)
> * Quick help on SQL Server 6 and NT -
> cj...@student.open.ac.uk@open.ac.uk (10)
> * Configure ODBC remotely - Brian Elliott (9)
> * Anybody have experience with Banyan/SQL Workstation? -
> Mfritzlen (2)
> * Oracle (DEC) Rdb -> MS SQL Server - mas...@vaxg.isc.rit.edu
> (6)
> o c (7)
> * Extended Stored Procedure - DLL not found - Ramon Carlos (18)
> * ODBC-Connection Win3.1 -> MS-SQL 4.2 - Thomas Freudenberg (12)
> * Syncing up SQL DB with Btrieve DB - Jung Lee (14)
> * Re: Counter field in SQL-Server 6.0 - b...@netcarta.com (29)
> o Hemant (38)
> o Lynne Warner (7)
> * US-UT ** NEED SYSTEMS ANALYST/VB/SQL ASAP *** PAYS$$$!!! **** -
> ASamuels (80)
> * US-NV ** NEED TESTING QA LEAD ASAP *** PAYS$$$!!! **** -
> ASamuels (91)
> * Re: Error 703 Help - Trevor Andrew - TA & Associates P/L (36)
> o Lance Holland (32)
> * sql compliant data bases - sam amsler (13)
> * How about some examples of Stored Procedures? - cdslack (10)
> * Help? Simultaneous Connections - Mike Krause (17)
> * Multiple recordsets and ODBC - Andrew Errington (45)
> * ODBC and stored procedures - Andrew Errington (35)
> * Re: MSSQL 6.0 - Error 0 - Alexander Barth (8)
> * Using Named Transactions - "Susan G. McManus" (14)
> * MS-SQL Server & IIS WEB - René Iffarth (17)
> * Re. problems accessing SQL Server - René Iffarth (11)
> * How NULLs are stored - Sasha Ostojic (10)
> * HELP WANTED : Programmer/Support Services - Scotto Wienhusen
> (18)
> * Re: How to limit # of rows returned by SELECT - Fernando Nantes
> de Souza (12)
> o Fabie...@free.fdn.org (25)
> * Any good books on using MSSQL with VB - Ken Ponder (7)
> * append - Thomas Lai (20)
> * err 3146 partially solved!! - Thomas Lai (27)
> * Can I execute Stored Procedures in a SELECT STATEMENT ?? -
> Iv=?iso-8859-1?Q?=E1n Mingot (23)
> o Brain (9)
> * SQL Monitor fail to start. - Eddy Setiawan (11)
> * Re: Are MICROSOFT SQL courses any good - Sysop (15)
> * Automating database dumps. - Eric (7)
> * Err 3146 (ODBC) - Thomas Lai (13)
> * HELP: dbcc checkdb. - Thomas Lai (112)
> * SQLMail not behaving - cwh...@carleton.edu (17)
> * Image files and BCP - jon glickman (6)
> * SQL Lockup - Roger Layton (25)
> o Wade Henderson (36)
> * HELP! SQL Server 6.0 data corruption - Budget Bureau (21)
> * When Timestamp is not Timestamp -- How to Fix - Gene Hubert
> (13)
> * SQL Server 4.21a Monitoring Tools? - kidseven (4)
> * DB-LIB and stored procs - T.M. Scheeler (12)
> * Press release for Database Security - Gordon Laqua (55)
> * Changing a field length in SQL Server - Gilstrap17 (11)
>
> --------------------------------------------------------------------
>
Ell
: In article <4i90pu$k...@igateway.uk-po.com>, Dave Eaton <eat...@internet-pilot.uk-po.com> writes:
: > We are experiencing an intermittent problem when trying to open more than
: > RESULTS OF ANOTHER HSMST' is given.
:In my experience using Access as a client this error has appeared AFTER
:another SQL error. I think it means that the client software has not
:retrieved all the rows from a previous query yet.
If this is the problem, the thing to do in vbsql is to check that the
result from excuting an sql statement is "NOMORERESULTS".
Elliott
Joe Foster of Borg
> Martin Herbener, mher...@carleton.edu (mher...@carleton.edu) wrote:
> : In article <4i90pu$k...@igateway.uk-po.com>, Dave Eaton <eat...@internet-pilot.uk-po.com> writes:
> : > We are experiencing an intermittent problem when trying to open more than
> : > one snapshot in VB ( VB3). The message 'CONNECTION IS BUSY WITH THE
> : > RESULTS OF ANOTHER HSMST' is given.
> : >
> :In my experience using Access as a client this error has appeared AFTER
> :another SQL error. I think it means that the client software has not
> :retrieved all the rows from a previous query yet.
> If this is the problem, the thing to do in vbsql is to check that the
> result from excuting an sql statement is "NOMORERESULTS".
> Elliott
We've also gotten this error in Access 2.0. Based on checking
various knowledge bases and the MSDN CD's, the quick fix was to
put this in our Access, VB, and various application .INI files:
[Debug]
RmtTrace=16
This forces the Jet database engine to turn off concurrent
connection processing in ODBC drivers. No code changes were
needed in our applications, except perhaps to verify that the
SetDataAccessOption statement was used and pointing Jet at the
right INI files in our VB apps.
I may have the spelling wrong. If this is the case, I'll
supersede this post with a corrected one.
--
Joe Foster (j...@bftsi0.gate.net or joe%bft...@uunet.uu.net)
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!