Differences between SQL 6.5 & 7.0 with ADO 
Author Message
 Differences between SQL 6.5 & 7.0 with ADO

I tried your example on 6.5 SP5a and it worked. I did need to specify
an active connection, though. You may need to check your code for
connection problems. Or maybe you need to specify the object owner.

I checked with the OLE DB native provider and the OLE DB provider for
ODBC. Are you using different providers (or versions?) to connect to
7.0 and 6.5?

I'm sure that your proc is more complex than this, so my guess is that
you've come across an issue we found with SQLOLEDB.

SQLOLEDB behaves differently from the ODBC Provider, in that the native
provider 'stops' on each statement, where ODBC does not. In your case,
if you have any statements before the final SELECT, you need to get
the final recordset using rs.NextRecordset or use the SET NOCOUNT ON
statement at the top of the stp.

I contacted Bill Vaughn (Hitchiker's Guide to VB and SQL
Server) and he forwarded my query to the developers. Below was their
response: -


From the developers:

Actually, I'm pretty sure I know what it is.  This is that difference
between SQL ODBC and SQL OLEDB where the ODBC driver doesn't stop on
each "command", whereas SQLOLEDB does.  It's hard to explain but, look
at the following:

CREATE PROC GetEmployeeStp
     SELECT * INTO #employee
     FROM employee           'SQL OLE DB "stops" first here

     SELECT * FROM #employee 'SQL ODBC & OLE DB stop here

In order to get SQLOLEDB to behave like SQL ODBC, add SET NOCOUNT ON
to the beginning of the stored procedure.  Then the SQLOLEDB driver
will only stop on row returning commands.

For what it's worth, he could also use "set rs=rs1.NextRecordset" in
this case to get to the result set, since it's the second stop in the
stored proc.


Perhaps the reason it works in 7.0 is that they've changed the behaviour?

> -----Original Message-----

> Sent: 20 December 1999 11:17
> To: Duncan, Andrew
> Subject: Differences between SQL 6.5 & 7.0 with ADO

>  Message from the Deja.com forum:
>  microsoft.public.vb.database.ado
>  Your subscription is set to individual email delivery

> I'm using VB6.0 SP3 and ADO with 2 seperate projects.  One is
> on SQL Server
> 6.5 SP5a and the other is SQL Server 7.0 (no SP)

> I am trying to return a value from a stored procedure as follows:

> rs.Open "my_proc"
> Debug.Print rs(0)

> where my_proc is:

> select 1

> This works under SQL 7.0 but not SQL 6.5.  I get an error on
> rs(0), telling
> me that "ADO could not find the object in the collection".

> Does anyone have any ideas on what I'm doing wrong ?

>  _____________________________________________________________
>  Deja.com: Before you buy.
>   http://www.*-*-*.com/
>  * To modify or remove your subscription, go to

> http://www.*-*-*.com/
>  * Read this thread at
>   http://www.*-*-*.com/ %3Cs58ruums53177%40corp.supernews.com%3E

 Sent via Deja.com http://www.*-*-*.com/
 Before you buy.

Fri, 07 Jun 2002 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Differences between SQL 6.5 & 7.0 with ADO

2. RDO error when upgrading from SQL 6.5 to SQL 7.0

3. Upsizing SQL 6.5 to SQL 7.0

4. New Transact-SQL Debugger for Microsoft SQL Server 6.5/7.0

5. USing ASP migrating from SQL 6.5 to SQL 7.0

6. Migrating from SQL 6.5 to SQL 7.0

7. SQL Debugger for Microsoft SQL Server 7.0 and 6.5

8. SQL Server 6.5 & VB6 ADO


10. MS SQL 6.5 / 7.0 / 2K

11. Connecting to SQL Server 6.5 and 7.0

12. SQL Server 7.0 , ADO & VB 6.0


Powered by phpBB® Forum Software