Re: Retrieving errors OLEDB-MDAC

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


"Ivo" <Ivo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:57CD653E-A065-42FF-A88D-149951BF691A@xxxxxxxxxxxxxxxx
We have a VB6 Application using ADO (mdac2.8) to connect to SQL 2005. We
connect to database using:

Provider=MSDASQL.1 and CursorLocation= adUseClient.

This connection is working fine, but tracing with the profiler we found a
lot of instruction like: 'exec sp_cursorfetch'

So we changed the provider to SQLOLEDB and kept the same CursorLocation.
Now
the profiler doesn't show the 'sp_cursorfetch' entries. OK but, now we can
't
retrieve our print statements from within the triggers and stored
procedures
through the ADODB.Conection.Errors collection.

Changing the CursorLocation to adUseServer resolves the problem but is
just
not what we want.

So, why can't we retrieve Print Statements with SQLOLEDB?


You can, just a tad differently.

They are two very different providers (MSDASQL is a deprecated "OLE DB for
ODBC", SQLOLEDB is a 'pure' OLE DB Provider), and how "returns" are handled
are very different.

In general, with ADO/OLE DB/ODBC the only "return" available was the Errors
Collection - and that's what the MSDASQL provider populated with some degree
of reliability. With SQLOLEDB you do have "returns" - so you want
something - ask for it.

[SET NOCOUNT ON wil]

You mentioned retrieving "errors", then retrieving "Print". You will likely
need a different scheme (rewriting the SP?) for retrieving both, so it is
difficult to give a definitive answer. This might help:
"Error Handling in SQL Server - a Background"
(or as I like to call it "More than you will ever want to know about Error
Handling in SQL Server")
http://www.sommarskog.se/error-handling-I.html
It also suggests some other links.
Plus provides the buzz words for additional searches.

hth
-ralph


.

Relevant Pages

  • Re: VB6 Err 3251
    ... SQLOLEDB Provider and when Im trying to set the Index by using the code ... SQL Server and Im trying not to change the existing code so I need to use ... .CursorLocation = adUseServer ... Frankly you are better off biting the bullet and converting your code to ...
    (microsoft.public.vb.directx)
  • Re: Re-2: Codepage-Probleme
    ... Microsoft OLE DB Provider für SQL Server ... Der Microsoft® OLE DB Provider für SQL Server, SQLOLEDB, ...
    (microsoft.public.de.sqlserver)
  • RE: ODBC SQL Server Driver: Timeout expired error
    ... ODBC is an older API than OLE DB. ... it was as an API on top of ODBC, and that provider is known as MSDASQL. ... for SQL Server there exists a native provider. ... it was known as SQLOLEDB, and instead of Driver, in the connect string ...
    (microsoft.public.data.oledb)
  • Re: Unspecified error while passingTEXT fields containing Hebrew t
    ... I am using ADO to communicate with SQL Server and the PRovider is ... > I assume that the engine is SQL Server, and the provider is SQLOLEDB? ...
    (microsoft.public.data.oledb)
  • linked server , Oledb error....Urgent.
    ... I am getting below error when creating linked server,infact in provider ... SQL Server Enterprise Manager ... Could not create an instance of OLE DB provider 'SQLOLEDB'. ...
    (microsoft.public.sqlserver.server)